List box query criteria (1 Viewer)

JPR

Registered User.
Local time
Today, 06:11
Joined
Jan 23, 2009
Messages
192
Hello. I would appreciate some help in trying to solve this problem. I apologize if I have not been too clear.

My db has a form with a combo, a list box and two tables.

The combo is used as query criteria and has it's record source in tbl1
Tbl1 has has three fields: Codes, Prefix and Countries.

Examples of Codes are 00, 11, 22, 33
Example of Prefix: A, B, C
Examples of Countries are USA, UK and France

Some Countries may have a similar Code, reason why there is a Prefix.

Tlb2 has two fields: Accounts and Branch

After users select the Code in the combo, the listbox returns all records from tbl2 that show the first two digits in Accounts similar to the query criteria:

Example: Selection in combo: 00
Listbox: Returns all records that show in field Accounts the first two digits 00

As I previously said, there may be multiple records with the same Accounts and would like to be able to determine the Country.

I was thinking of associating to the click event of the listbox a code that will specify the Country (probably in a txtbox or label) through the Prefix.

Example:

Combo selection: 00

Listbox:

Record1: Account 0011111 Branch: A132 (txtbox/label should show Country Name based on prefix)
Record2: Account 0011111 Branch: B321
Record3: Account 0011111 Branch: C000

Thank you
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:11
Joined
Feb 19, 2013
Messages
16,553
in a textbox controlsource put

=dlookup("Country","tbl1","Code='" & left(listbox,column(0),2) & "' AND Prefix='" & left(listbox.column(1),1) & "'")
 

cheekybuddha

AWF VIP
Local time
Today, 13:11
Joined
Jul 21, 2014
Messages
2,237
I was going to suggest the same as CJ_London.

Did you try it? You have to replace 'listbox' with the name of your listbox.

I think the expression given also has a comma where there should be a dot: it should read ... listbox.column(0) ...

So:
Code:
=DLookUp("Country","tbl1","Code='" & Left([YourListBoxName].[column](0),2) & "' AND Prefix='" & Left([YourListBoxName].[column](1),1) & "'")
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:11
Joined
Feb 19, 2013
Messages
16,553
think the expression given also has a comma where there should be a dot: it should read ... listbox.column(0) ...
good spot!
 

Users who are viewing this thread

Top Bottom