Filtered Lookup in Combo Box

[stewartsetter]

Hi All,

If this is already covered elsewhere I do apologies and would appreciate being re-directed.

Where MS Excel is concerned there is very little I can't do and I am now transferring that skill to MS Access and I'm stumbling on this one.

In Excel I would use the INDIRECT formula within the validation and named range to do this but in Access I'm struggling to get this to work.

I have in table TBL_NL_Structure a list of CLIENT_ID with NL_ACCOUNTS against them i.e.

CLIENT_ID....ACCOUNTS
900001.........4000
900001.........4001
900002.........4003

What I want to be able to do is in a sub form and a combo box named ACCOUNT that looks at the active CLIENT_ID and pulls into to combo box JUST the ACCOUNTS set to that CLIENT_ID example if the CLIENT_ID is 900001 is just brings in ACCOUNTS 4000 & 4001.

I can get the whole list to come in to the combo box but not a filtered list.

Any help or advice here would be gratefully appreciated

Stewart :banghead:

 

[CJ_London]

The sql for your cboAccount rowsource will be something like:

SELECT CLIENT_ID, ACCOUNTS FROM TBL_NL_Structure WHERE CLIENT_ID = [txtClientID]

where [txtClient_ID] is the name of the control on your form for the client id

Note that in the form current event you will need to put

cboAccount.Requery

to refresh the list for different clients

One other suggestion, although it is a matter of personal choice, don't use all capitals in your names - makes it difficult to read

 

[stewartsetter]

Thanks CJ_London for your assistance and suggestion, the SQL query I'm now good with but the requery is confusing me.

Should I have a macro for this to refer to ?

Thanks,
Stewart

 

[CJ_London]

Should I have a macro for this to refer to ?

yes - tho' I suggest you use VBA which is much more flexible. I don't use macros as I find them too limiting so can't advise how you would do it that way.

In form design,

if the properties window is not showing, ensure the design ribbon is showing then click on properties to open it,
then click on the small square box on the left just below the window bar to select form.
Then in the properties window, select the events tab and you'll see the current event at the top.
To the far right of this line, you'll see a button with a 3 dot carat (...) - click on it, you should be presented with 3 choices, select the bottom one and the vba window will open

it should say

Private Sub Form_Current()
 
End Sub

the just add the code between these two lines, changing 'cboAccount' to the name of your control