RowSource or DLookUp for changing a control source in txtbox?

[d9pierce1]

HI all,
I have a bound form to where i am trying to display the records only that are related to the combo selection.
I cant figure out how to make this work. I want to open form, with PrimaryRole Records related to whatever is in CboPrimaryIndustry


and if i change the CboPrimaryIndustry i want it to change to related records? I can do it with a combo box but i cant do it with a text box?

Attached is a mach up db i put together for this.
Thanks

Dave
Test.zip

[pbaldy]

This:

Me.TxtPrimaryRole.ControlSource = "Select * From PrimaryRoleTbl Where PrimaryIndustryID=" & Me.CboPrimaryIndustry

would need to be more like:

Me.TxtPrimaryRole = DLookup("PrimaryRole", "PrimaryRoleTbl", "PrimaryIndustryID=" & Me.CboPrimaryIndustry)

but it doesn't make sense to be a textbox since there could be multiple records that match the criteria.

[d9pierce1]

Hi, What i want to do is just display the records from the table "PrimaryRoleTbl" that are related to the Combo "CboPrimaryIndustry"
So if Construction is Listed in CBO, I would only show the records related to ConstructionID in the PrimaryRoleTbl...
If I change the combo to a different industry, then it would change the records that relate to that industry...

[pbaldy]

You'll need a combo or listbox to list multiple records. If you want to see them like:

Engineer, General Contractor, ...

you'll need to use a recordset to loop the values and build that string. Or you could use:

http://allenbrowne.com/func-concat.html

which implements that technique.

[d9pierce1]

I dont want them to be side by side, i want to show all records that relate to the combo ID in a continous form.
So if
Construction Industry Selected,
Then Continuous form would show the all records relating to that selection such as:

Architect
Engineer
General Contractor
And so on...

[pbaldy]

Oh, you could do this:

Me.RecordSource = "Select * From PrimaryRoleTbl Where PrimaryIndustryID=" & Me.CboPrimaryIndustry

or set the Filter:

Me.Filter = "PrimaryIndustryID=" & Me.CboPrimaryIndustry
Me.FilterOn = True

[Bob Fitz]

The attached db show two possible ways to do this

On one form the forms Record Source is changed dependent on the combo selection

On the other form a filter is applied dependent on the combo selection

[d9pierce1]

Thank you,
That worked perfect!
Again, Thank you...

[pbaldy]

Happy to help! Sorry about misunderstanding the goal.

[Bob Fitz]

Thank you,
That worked perfect!
Again, Thank you...

The attached db shows a third way:

[d9pierce1]

I have only one issue with all the methods! It doesnt put the PrimaryIndustryID in the PrimaryRoleTbl when adding a new record? Is there a way to fix that so i can add new records?
Thanks

[orange]

If you add a new record to PrimaryIndustryTbl, how would Access know which PrimaryRole to apply? And would an existing Role apply, or would/could a new Role be required?

[Bob Fitz]

In the attached db the PrimaryIndustryID is added to the new record provided there is already a selection made in the header combo

EDIT:
Should have said that the suggested solution can be found using the third version of the form

[d9pierce1]

If you add to PrimaryIndustryTbl it will require you to add new records... Records would be blank or null till you inserted new records to that Industry. But the methods above will allow me to add a new record but will not put the ID in for the PrimaryIndustryID into the PrimaryRoleTbl? In the methosd above, Method 3 works with the exception to not being able to save the PrimaryIndustryID as is unbound, the other methods change first record to a different PrimaryIndustryID which is bazzar to me? Basically if i add a new record to this i want it to be saved with the PrimaryIndustryID from the current Combo in the form.

[d9pierce1]

That works super fine! Thank you so much...I will get this one day, i promise....