DLookup where clause

[Chris]

Hello all.

I have two cbo boxes on a form (based on tables). The first cbo filters the
next cbo. the first contains Bank names and the second contains branches.
On the double click event of the cbo boxes I have the option of adding the
bank/branch to the table(s). I've used the following code to check of the
Bank is already in the table

If inList = IsNull(DLookup("[BankName]", "tblBanks", "[BankName]= " &
cboBankname.text)) Then
MsgBox "The bank already exists."

I would like the code to check if the branch exists too. However this is
creating problems as there may be two banks with the same branch name e.g.
Halifax / Glasgow , Bank of Scotland / Glasgow.

I'm pretty sure the way forward is to have some sort of where clause in the
'Crieria' section ( "[BankNameID]= " & cboBankname.value And "[Branch]= "
&cbobranch.text) , but, I do not know how to add it.

tblBanks
BankID - pk - An
BankName - str

tblBranch
BranchID - pk An
BankID - fk - An
BranchName - str

 

[Douglas J. Steele]

GIven that BankName is a string, I don't understand how you can be using
what you posted successfully, as you need to enclosed strings in quotes:

(DLookup("[BankName]", "tblBanks", "[BankName]= '" & cboBankname.text & "'")

To add a second criteria to the Where part, you need to have the AND inside
quotes as well:

"[BankNameID]= " & cboBankname.value & " And [BranchName]= '"
&cbobranch.text & "'"

Exagerated for clarity, that's:

"[BankNameID]= " & cboBankname.value & " And [BranchName]= ' "
&cbobranch.text & " ' "

And hopefully you made an error in what you posted as the details for
tblBranch. If BankID is a foreign key pointing back to the BankID in the
tblBanks table, it needs to be a Long, not an Autonumber.