base a combo box in a tab off of Form ID

[hockey8837]

I have a form 'Location' that has tabs. Two of these tabs are 'Tree Work' and 'Funding.'

A location can have multiple records for tree work, and each tree work record needs to be assigned a funding source. But the funding source may be different for each tree work record, so each needs an individual relationship (I think this is the best way to do this...).

So, in 'Funding' I have a datasheet, and on the TreeWorkID combo box, I have a select query on the row source to pull up ALL TreeWorkIDs in the DB so the user can select the tree work they just added and apply funding to it.

How do I limit this combo box to ONLY TreeWorkIDs that match the currently open LocationID and not every Location?

I've looked into info about basing one combo box off of another, but have found nothing about basing a combo box off of a record ID foreign key.

 

[John Big Booty]

What you are trying to do is a variation on a Cascading Combo Box set. Except you are using data from the form rather than another Combo.

Firstly ensure that LocationID is part of the Record Source for the form, create your Combo Box as normal making LocationID one of the field that is in the Row Source for the combo (you can make this a zero width column). Once you have finished creating the combo, save the form and go into the Combo Box's Row Source, by clicking the little button to the far right (the one with the three full stops on it), this will open up a query builder which represents the Combo's Row Source in the criteria under LocationID put the following;

Forms!FormName!LocationID

.

This will limit the data in the combo to that which is related to the current LocationID, now you will need to put the following code in the form's On Current event;

Me.CombName.Requery