Filter Subform based on Combo Box

[nka]

Hello everyone!

I think I am asking the impossible, but can't think why it can't be done!

Here's the dilema:

I am working with an unbound form to search a Mobile Register.

On the form I have an unbound combo box which looks up User details from a table (LKP_User).

When I select a user, I want the subform below to filter all the details for that user and show me which mobiles they have.

PROBLEM:
Combo Box has 3 fields, User_ID, User, Dept_ID. User_ID is bound column but not displayed in combo (only User) is displayed.

I need to link the User_ID with the User_ID on the subform, but don't know how to tell it to look at column 1 of the combo box.

Can anyone point me in the right direction? Do I need some fancy code to get this to work?

Cheers in anticipation
NKA

 

[RuralGuy]

Put an invisible TextBox on your MainForm and use it as the MasterLinkField for the SubFormControl. Set the ControlSource of the TextBox to:
=Me.ComboBoxName.Column(0)
Using your ComboBox name of course!

 

[nka]

Thank RG!

I see the principle of this... but not sure I have the hang of it! I have created the text box as suggested and left it visible so I can see it working for the moment...

Am I supposed to put some kind of requery/refresh on it when I change the value in the combo box? At the moment, when I open my form it displays #Name? in the text box (guess that's because there is no value selected in the combo)

I was expecting the text box to change and display what I selected in the combo box... but it doesn't.

What am I doing wrong? (I am using Access 2003 for this if that helps)

 

[nateobot]

Try it without the Me.

 

[nka]

It's OK! I cracked it... EDIT - the textbox issue anyway! I only have to reference the combo box, it automatically selects the column for me!

Now the next problem, for some reason it's not linking to my subform. I am getting an error message saying "cannot build a link between unbound forms". However, I have another situation where I have created links by just typing the field names at that seems to work - but this one won't!

Thanks for your help and advise in anticipation... so near, yet so far!

NKA

 

[RuralGuy]

Go to the properties sheet for the SubFormControl and type in the name of your TextBox in the Link Master Field line. Sorry about the Me BooBoo. I really do know better.

 

[Janjang]

thanks..it works..but only 1 data per clickin.i mean, when i click some date, it only shows 1 data instead many of data had same date...

how bout i want Subfrom list down that criteria selected from Cbo...