Pull Info From One Form To Another If Condition is True

[Paul]

I have a pop-up form for contact info of people at a particular agency. For
the Address, Phone Number and Fax, I would like to populate these fields
from the Agency Form, if a checkbox is checked. The pop-up form is derived
from a joined query Contact and Contact Info.

The relationships:
Agency is connected to Contact by Agency ID
Contact is connected to Contact Info by Contact ID

The Checkboxes are:

Use Agency Address = If checked fill in Address, City, State, Zip with data
from Agency Form
Use Agency Phone = If checked fill in Phone with data from Agency Form
Use Agency Fax= If checked fill in Fax with data from Agency Form

I have created Macros for each and set the conditions for each to operate if
the value of the checkbox is true. Then use SetValue to fill in the
appropriate field with the appropriate value. It's not working. I get an
error: Cannot Add Records; join key of table 'contact info' not in
recordset.

Paul

 

[Steve Schapel]

Paul,

I don't think this is related to your macro. It sounds like there is a
problem with the way you have set up the "pop-up form". What is the
Record Source of this form? How are you opening it, and at what record?
How is the Contact ID data supposed to be entered into the Contact
Info table for a new record?

 

[Paul]

You were right Steve. Thanks for pointing me in the right direction. I have
another question, Now that the pop-up form is working properly, The new data
isn't showing in the subform when it closes. I assume I have to requery the
data, but have never used this before. From the help it says to requery a
control making it sound like it's for a combobox. Do I requery the query
the subform is based on? Requery the subform or a control on the subform
(all textboxes.) Or all the above?

Thanks for your help again.

Paul

 

[Steve Schapel]

Paul,

Requery the subform control. If this is code runnig from the pop-up
form somewhere, I think the syntax would be something like this...
Forms![NameOfMainForm]![NameOfSubform].Form.Requery

But what is the purpose of the pop-up form? If you've got a subform for
the same data, why don't you just use this for data entry/editing of the
subform records?

 

[Paul]

Steve,

The data on the subform is a subset of the data from the pop-up form.
There's a More Info button on the subform that will bring up the pop-up
(with all the information) in Read Only mode to display the rest of the
data.

Thanks again for your help.

Paul

Paul,

Requery the subform control. If this is code runnig from the pop-up form
somewhere, I think the syntax would be something like this...
Forms![NameOfMainForm]![NameOfSubform].Form.Requery

But what is the purpose of the pop-up form? If you've got a subform for
the same data, why don't you just use this for data entry/editing of the
subform records?

--
Steve Schapel, Microsoft Access MVP

You were right Steve. Thanks for pointing me in the right direction. I
have another question, Now that the pop-up form is working properly, The
new data isn't showing in the subform when it closes. I assume I have to
requery the data, but have never used this before. From the help it says
to requery a control making it sound like it's for a combobox. Do I
requery the query the subform is based on? Requery the subform or a
control on the subform (all textboxes.) Or all the above?

Thanks for your help again.

Paul