Subforms and Refreshing Combo Boxes

[Pringle9984]

Right... the relevant tables are

quote_Mai
quote_Conten
quote_Section
quote_Location

The latter 3 are linked to quote_Main via the field quoteID

I have a form for each of the tables

frm_quot
subfrm_quoteConten
subfrm_quoteSection
subfrm_quoteLocation

Obviously the frm_quote is the main form and all the others are su
forms upon it

They all work fine apart from 1 thing on subfrm_quoteContents; one o
the fields is a combo box location_id; the query for this combo bo
is

SELECT quote_locations.location_id
quote_locations.quote_num, quote_locations.location_nam
FROM quote_location
WHER
(((quote_locations.quote_num)=forms.frm_quote.quote_num)
ORDER BY quote_locations.location_name;

Which should populate the combo boxes with those locations that hav
the same quoteID as the currently displayed quote - it works th
first time you click on the combo box but then if you cycle throug
records it doesn't update the contents

E.G. You open the quote_main form... hit 'next record' a few time
till you get to quote 5; in the subfrm_quoteContent you click on th
'location' combo box and it drops down with the correct content
(basically exactly what is shown in subfrm_quoteLocations) but the
you cycle to Quote 3 and click on the combo box - it still shows th
locations from quote 5

So... effectively what I want to do is 'refresh' the combo box'
contents whenever I click on it (or whenever I change record AN
whenever a new record is added to subfrm_quoteLocations)

I've tried 'requery' but because it requery's the combo box every tim
it won't actually let me select a value

If I requery the entire form it resets me back to the first record s
that's annoying as well

I have a few theories but they're not the ideal way I'd like to do i
so any help you could offer would be greatly appreciated

 

[Marshall Barton]

Right... the relevant tables are:

quote_Main
quote_Content
quote_Sections
quote_Locations

The latter 3 are linked to quote_Main via the field quoteID.

I have a form for each of the tables:

frm_quote
subfrm_quoteContent
subfrm_quoteSections
subfrm_quoteLocations

Obviously the frm_quote is the main form and all the others are sub
forms upon it.

They all work fine apart from 1 thing on subfrm_quoteContents; one of
the fields is a combo box location_id; the query for this combo box
is:

SELECT quote_locations.location_id,
quote_locations.quote_num, quote_locations.location_name
FROM quote_locations
WHERE
(((quote_locations.quote_num)=forms.frm_quote.quote_num))
ORDER BY quote_locations.location_name;

Which should populate the combo boxes with those locations that have
the same quoteID as the currently displayed quote - it works the
first time you click on the combo box but then if you cycle through
records it doesn't update the contents.

E.G. You open the quote_main form... hit 'next record' a few times
till you get to quote 5; in the subfrm_quoteContent you click on the
'location' combo box and it drops down with the correct contents
(basically exactly what is shown in subfrm_quoteLocations) but then
you cycle to Quote 3 and click on the combo box - it still shows the
locations from quote 5.

So... effectively what I want to do is 'refresh' the combo box's
contents whenever I click on it (or whenever I change record AND
whenever a new record is added to subfrm_quoteLocations).

I've tried 'requery' but because it requery's the combo box every time
it won't actually let me select a value.

Requery is the right thing to do. The question is where to
do it. For changing records in the main form, use the main
form's Current event:
Me.subfrm_quoteContents.Form.location_id.Requery

and you also want to perform the equivalent in the locations
Form's AfterUpdate event:
Parent.subfrm_quoteContents.Form.location_id.Requery

 

[Pringle9984]

Thank you; works perfectly

I had the code right for the main form just didn't know where to pu
it to make it run every time a record changed

As for the locations subform - I knew where to put the code, I jus
didn't know about 'parent' I'll remember that one in future

Many thanks again, it's hugely appreciated

 

[Guest]

How do I go about building the Requery with my tables ? I'm trying to
understand your response here as I think I have the same problem. My combo
box is on the main form & I want the other fields on the mainform & on the
subform to update (requery) when the user selects a record from the combo
box. Thanks

 

[Marshall Barton]

That's a totally different question. The way I read your
question, you want to navigate to the form record that
matches the combo box selection.

If so, then you can use the combo box wizard to generate the
code to sync the main form. The subform control's Link
Master/Child properties should be used to sync the subform
to the main form.

 

[Guest]

Yes, I want to navigate to the form record that matches the combo box
selection.
Here is TblOrg
OrgId (PK) autonum
Org text
Addr text
City text
State text
Zip Number
Here is TblQty
OrgId (pk) number
Qtyyear (pk) number
QtyAmount text

TblOrg is a list of organizations & TblQty is a list of donations made by
them (one per year) - Qtyyear for the year - 2002, 2003 2004 etc, QtyAmount
amount given

My Form consists of all the fields from TblOrg with Org initially being a
text box but deleted & readded as the combo box. Plus a subform based on a
query of all TblQty fields. I used a query because in criteria for QtyYear I
specify >(Year(Date())-2) so that I just show last years & this years giving.

You say "combo box wizard to generate the code to sync the main form." Is
that when I added the combo box & it stepped me through the options ? If so I
did not see where to add the code to sync? Also I see in the properties data
for the subform the Link Master/Child which is curently set to Orgid. Sounds
as though you are saying I need to change that to sync the subform ?
Right now, not using the combo box I can use the record selector to move
through the records & the subform stays synced with the master.
I truly appreciate the help. I am new to Access & am trying to help out a
charity keep records. Many thanks, hope I've explained it.

That's a totally different question. The way I read your
question, you want to navigate to the form record that
matches the combo box selection.

If so, then you can use the combo box wizard to generate the
code to sync the main form. The subform control's Link
Master/Child properties should be used to sync the subform
to the main form.
--
Marsh
MVP [MS Access]

How do I go about building the Requery with my tables ? I'm trying to
understand your response here as I think I have the same problem. My combo
box is on the main form & I want the other fields on the mainform & on the
subform to update (requery) when the user selects a record from the combo
box.

 

[Marshall Barton]

The text box (not combo box) that is **bound** to the Org
field can not be used to navigate to another record. This
text box is used to enter the name of a new orgization or to
edit an existing name.

The **unbound** combo box that you want to use for
navigation must be a separate control. I usually place this
control in the form's header section and make its back color
a different color so the users can see tht it is not a data
entry control. This is the one the combo box wizard can be
used to generate the navigation code. Because of some
wizard's schizophrenic nature with regard to DAO vs. ADO,
check it to make sure it generated the correct code. If you
have trouble with it, post a Copy/Paste of the code and I'll
check it for you.

From your description, the subform Link Master/Child
properties are already set correctly.

 

[Guest]

Worked like a charm, thanks so much.

The text box (not combo box) that is **bound** to the Org
field can not be used to navigate to another record. This
text box is used to enter the name of a new orgization or to
edit an existing name.

The **unbound** combo box that you want to use for
navigation must be a separate control. I usually place this
control in the form's header section and make its back color
a different color so the users can see tht it is not a data
entry control. This is the one the combo box wizard can be
used to generate the navigation code. Because of some
wizard's schizophrenic nature with regard to DAO vs. ADO,
check it to make sure it generated the correct code. If you
have trouble with it, post a Copy/Paste of the code and I'll
check it for you.

From your description, the subform Link Master/Child
properties are already set correctly.
--
Marsh
MVP [MS Access]

Yes, I want to navigate to the form record that matches the combo box
selection.
Here is TblOrg
OrgId (PK) autonum
Org text
Addr text
City text
State text
Zip Number

Here is TblQty
OrgId (pk) number
Qtyyear (pk) number
QtyAmount text

TblOrg is a list of organizations & TblQty is a list of donations made by
them (one per year) - Qtyyear for the year - 2002, 2003 2004 etc, QtyAmount
amount given

My Form consists of all the fields from TblOrg with Org initially being a
text box but deleted & readded as the combo box. Plus a subform based on a
query of all TblQty fields. I used a query because in criteria for QtyYear I
specify >(Year(Date())-2) so that I just show last years & this years giving.

You say "combo box wizard to generate the code to sync the main form." Is
that when I added the combo box & it stepped me through the options ? If so I
did not see where to add the code to sync? Also I see in the properties data
for the subform the Link Master/Child which is curently set to Orgid. Sounds
as though you are saying I need to change that to sync the subform ?
Right now, not using the combo box I can use the record selector to move
through the records & the subform stays synced with the master.
I truly appreciate the help. I am new to Access & am trying to help out a
charity keep records. Many thanks, hope I've explained it.