synchronize combo boxes on Subform

[hockey8837]

Hi,
My DB contains a Table 'TreeWork' and a table 'Funding'.

I have a datasheet subform within a Funding tab on my form 'TreeWorkDetails' which has the Link Master Fields and Link Child Fields matched so that only Funding for that tree work is shown.

I have a lookup set to the 'TreeWorkIDFK' in this datasheet so that the user can select the specific site of the tree to apply funding to, which is displayed as the "Address, Site #", but the ID# is what is stored.

Right now, this lookup shows ALL possible Tree Work locations in the entire DB, and I would like it to only provide as an option the 'TreeWorkIDFK's of the current record which I have open.

I've looked at info about synchronizing two combo boxes, one based off the selection of the other...but I need the first combo's selection to be automatically made to narrow the 'TreeWorkIDFK' to the open record.

Any suggestions?

 

[highandwild]

A listbox or combo box can have a query as its row source.

The query can reference a textbox on the form.

If the value in the textbox changes then the listbox needs to be requeried.

 

[hockey8837]

A listbox or combo box can have a query as its row source.

The query can reference a textbox on the form.

If the value in the textbox changes then the listbox needs to be requeried.

How do I get the query to reference the ID# of the parent form only, so that it limits the options in my query to only those that apply to the ID of the record that I have open?

Basically, what is the Code to call this? Do I put something into the source of the datasheet? Does it go into the source of the Child Foreign Key combo box within the datasheet? Or is there a separate Macro command I should be ordering?

Thanks!

 

[highandwild]

This will do the trick:

put Forms![frm_MainForm]![txtMainRecordId] in the id field in the query which the sub form is based upon where frm_MainForm is the form with your main record on and where txtMainRecordId is the text box on the form containing the unique id for the record

Whenever the id changes on the main form then you need to requery the subform but if have set the link up when you added in the subform then it will do it automatically.

Cheers

 

[hockey8837]

This will do the trick:

put Forms![frm_MainForm]![txtMainRecordId] in the id field in the query which the sub form is based upon where frm_MainForm is the form with your main record on and where txtMainRecordId is the text box on the form containing the unique id for the record

Hi,
In my subform in the 'Budget' tab of my Location Details form, I tried to set the code

Forms![Location Detail]![LocationID]

into the lookup query for TreeWorkID, which was the control that was showing ALL possible IDs that I wanted to only show those relevant to the open record. In the query, the above code was column 0, in column 1 was the TreeWorkID, and column 2 was the extended name to be able to identify the TreeWorkID by name (i.e. Street address & site #).

When I did this, column 0 showed a weird box/symbol and I was unable to select a record and this caused an error.

So, I put the code in the record source of the LocationIDFK column instead, and it appropriately shows the ID of the current open Location Details record, but it still doesn't narrow down the selections in the TreeWorkID lookup column to only those for this Location record.
-This is the way that I've left it for now.

I've attached the DB for you to get a better idea of what I'm describing. Open up a record on the Location List to get to the Details page.

Its in access 2007, but, I can convert to earlier version if needed.
Thanks!

 

[highandwild]

You will have to submit a 2003 version

 

[hockey8837]

Here ya go.