By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,410 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

check record exists with two keys

reginaldmerritt
100+
P: 201
I have a table which uses two primary keys. One is a canidate code the other is a course\programme code.

I have a fom with these two fields but automaticaly picks the candidates code. Therefore the user only needs to choose a programme code to create a record.

I have a button that alows the user to close the form. If a record already exsits obviously a new record isn't created but the user has no warning of this.

So my question is this.

How do i check a table to see if it has a record with the two values that the form is using already?

I tried using two seperate DLookup methords but i can't seem to use two at a time as dlookup will stop at the first instant it finds.

any help would be most welcome.
Thank you
Jun 9 '07 #1
Share this Question
Share on Google+
9 Replies


MSeda
Expert 100+
P: 159
You need only one dlookup that tests for both criteria.

somthing like
Expand|Select|Wrap|Line Numbers
  1. if not isnull(dlookup("[PK1]", "myTable", "[PK1] = " & me.PK1 & " AND [PK2] = " & me.PK2)
  2.  
Jun 9 '07 #2

reginaldmerritt
100+
P: 201
You need only one dlookup that tests for both criteria.

somthing like
Expand|Select|Wrap|Line Numbers
  1. if not isnull(dlookup("[PK1]", "myTable", "[PK1] = " & me.PK1 & " AND [PK2] = " & me.PK2)
  2.  
ok thanks, i did try using AND but had errors, proably missed a " or ' somewhere. Always the way.
Jun 11 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
You need only one dlookup that tests for both criteria.

somthing like
Expand|Select|Wrap|Line Numbers
  1. if not isnull(dlookup("[PK1]", "myTable", "[PK1] = " & me.PK1 & " AND [PK2] = " & me.PK2)
  2.  
You may need quotes (') if your PK fields are strings.
My example assumes both are, if only one is then I'm sure you can work it out from the examples already seen.
Expand|Select|Wrap|Line Numbers
  1. =Nz(DLookup("[PK1]", _
  2.             "myTable", _
  3.             "[PK1]='" & Me.PK1 & "' AND [PK2]='" & Me.PK2 & "'"),"")
Jun 11 '07 #4

reginaldmerritt
100+
P: 201
You may need quotes (') if your PK fields are strings.
My example assumes both are, if only one is then I'm sure you can work it out from the examples already seen.
Expand|Select|Wrap|Line Numbers
  1. =Nz(DLookup("[PK1]", _
  2.             "myTable", _
  3.             "[PK1]='" & Me.PK1 & "' AND [PK2]='" & Me.PK2 & "'"),"")
Thank you NeoPa, I'm partly guessed that string variables and numeric variables would have to be handled differently, and your example shows that. But I still get errors. Usually itís Compile Error: Wrong number of arguments or invalid property assignment.

The two primary keys are [candidatecode](integer) and [programmecode](string).
My actual code is:
Expand|Select|Wrap|Line Numbers
  1. varX = DLookup("[CandidateCode]", "Registration", "[CandidateCode]= " & Me.CandidateCode And "[ProgrammeCode]", "Registration", "[ProgrammeCode]= '" & Me.ProgrammeCode & "'")
I get no error if i split the dlookup in two two variables, one to hold each PK value. But this is no good to me; as i said earlyer Dlookup stops at the first instance it finds.

Any suggestions ?
Jun 11 '07 #5

reginaldmerritt
100+
P: 201
Also I'm declaring the variable varX as Variant, is that correct ?
Jun 11 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
Expand|Select|Wrap|Line Numbers
  1. varX = DLookup("[CandidateCode]", "Registration", "[CandidateCode]= " & Me.CandidateCode And "[ProgrammeCode]", "Registration", "[ProgrammeCode]= '" & Me.ProgrammeCode & "'")
Me.CandidateCode is a variable, but you follow it (without any quotes) with the "And". Outside the quotes this is a VBA programming construct and not part of the instructions passed to the DLookup function.
Try :
Expand|Select|Wrap|Line Numbers
  1. varX = DLookup("[CandidateCode]", _
  2.                "Registration", _
  3.                "[CandidateCode]=" & Me.CandidateCode & " And [ProgrammeCode]='" & Me.ProgrammeCode & "'")
Jun 11 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
Also I'm declaring the variable varX as Variant, is that correct ?
If it is possible not to find a record then it is necessary. DLookup() will return a Null if no record found.
Jun 11 '07 #8

reginaldmerritt
100+
P: 201
That worked a treat, thank you so much. I thought using a integer variable you didn't need the second &. Just as i expected its a matter of a missing " too. Thanks for spotting the mistakes.
Jun 12 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
No problem.
It's sometimes helpful to split up what you're doing into the constituent steps.
  1. DLookup(String1,String2,String3)
  2. Code to create String3.
Then you look at the code to create the string. You need the "And " to be within the string so the double-quotes are required.
Jun 12 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.