requery of data viewed on a subform

W

Wiz

I've created a DB with a main form and on this form I have a tabbed box with
subforms on each tab. No issues!

I have a switchboard that allows a user to get to this main form and other
forms to add specific data to tables. So far no issues.

What I would like to do, as many will not "think ahead" to add the new
records before they start updating the main form, is to allow them to double
click a Name box on any subform (on any one of the tabs) to have an "Add
form" open for that category. I was successful in creating that "double
clikc" feature but when the subform gets control back the newest record(s)
are not visible to them in the combo box pull down. I know that the data
must be refreshed or requeried but if I manually click in the same name field
I use in the code to automate the requery when the form gets focus back, I
get an error from the code but manually going to the toolbar and selecting
REFRESH works. The data is visible in the combo box.

Again as some users are not Access savvy and I would like, to make this as
user friendly as possible, the refresh/requery action occur automatically
once the focus is given back to the subform from the "double click" action.
My problem is I can't seem to figure out what I'm doing wrong to get a the
requery to work or act like the manual process I explained above does and
works.

Here is the setup with how I populate the subforms on the TAB form. I have
a main table (one to one relationship), another table with names (one to one
relationship) and a "Key" table (Many to Many relationship) with the Primary
Key from the main table and the Primary Key from the Name table as the only
fields. I've set the relationships up, created a query with the two fields
from the "Key" table and all of the fields from the Name table. Again no
issues here. This query is what I use as the "SOURCE" for the subform data
population (performance etc). Again everything works, the correct name or
names associated with the main table records appear properly and I can add or
delete names and everything is perfect.

The issue is doing the requery so the user (not access savvy) would not have
to close or go through the refresh process explained above.

Here is what I've deduced thus far. I can see the form, subforms and the
names on the subform but when I try to give the name of the field of that
subform to requery once the subform gains focus again, it tells me that name
doesn't exist. Now am I missing something simple or is it because I'm using
a query that pulls the data from the table and that name is different than
the name on the subform through the query? Is it trying to use the NAME
field of the table and not the name of the query and thus my problem with me
being able to code the correct requery command when the subform gets focus
again?

Any help in would be appreciated.
 
K

Klatuu

What you need to do is requery the subform when you close the add form. I
would suggest the Unload event of the add form. Now, the trick is correctly
referencing the subform.

You do not address the name of the subform form that is being used as the
subform. You addess the name of the subform control on the main form. If
you look at the properties of the subform control, one of them is Source
Object. That property identifies the name of the form that is presented in
the subform control. To reference the Source Object of the subform control,
you refer to it as Form. So the correct syntax would be:

Forms!MainFormName!SubFormControlName.Form.Requery
 
W

Wiz

Thanks, the forest was too close.

Klatuu said:
What you need to do is requery the subform when you close the add form. I
would suggest the Unload event of the add form. Now, the trick is correctly
referencing the subform.

You do not address the name of the subform form that is being used as the
subform. You addess the name of the subform control on the main form. If
you look at the properties of the subform control, one of them is Source
Object. That property identifies the name of the form that is presented in
the subform control. To reference the Source Object of the subform control,
you refer to it as Form. So the correct syntax would be:

Forms!MainFormName!SubFormControlName.Form.Requery
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top