DLookup

[Denise Pollock]

I have a from that takes 2 fields on the form combines them together with a
dash inbetween them and creates the primary key in the table. I am trying to
create a code that will check to see if that combination already exists in
the database before running the SQL code to insert all the data into the
table. I stole a code from another post but it does not seem to work for me,
I assume because I am trying to combine two fields. When I run the code I
get the msgbox that the Weld_ID already exists everytime.

Here is the code I currently have

Dim strSQL As String
If IsNull(DLookup("[Weld_ID]", "[Welds]", "[WELD_ID]= '" & assy_mark_fk &
"-" & weld_num_fk & _
"'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."
Else
'Weld_ID doesn't exist
strSQL = "blah blah big long sql query goes here, this part already works"
DoCmd.RunSQL strSQL
MsgBox "WeldID " & assy_mark_fk & "-" & weld_num_fk & " Created"
End If

Would appreciate it if someone could fix this for me.

Thanks,

 

[SuzyQ]

What about creating a unique index on those two fields. If the record is
already there, it will not add it again?

 

[John W. Vinson]

I have a from that takes 2 fields on the form combines them together with a
dash inbetween them and creates the primary key in the table.

Well, don't DO that!!!!

It's redundant, it risks data anomalies (e.g. if you correct an erroneous
assy_mark_fk and don't make the corresponding change in the primary key), it
wastes space, and it wastes time.

Maybe you're not aware that a primary key can consist of up to TEN fields - it
doesn't need to be just one. Open the table in design view; ctrl-click the two
fields; click the Key icon. These will now be a joint, two-field primary key;
either element of the key can be duplicated but the combination cannot. The
composite field should simply not exist in your table.

 

[Denise Pollock]

I did not know the primary key could be two fields, that simplifies thing. I
still need the lookup command to work, because access is rather horrible at
handling duplicate entries, and I have to dumbdown this database as much as
possible as the people that are using it, don't normally use a computer, for
anything.

So I need the dlookup to verify that the combination of Assy_mark_fk and
Weld_num_fk does not already exist in the table.

 

[John W. Vinson]

I did not know the primary key could be two fields, that simplifies thing. I
still need the lookup command to work, because access is rather horrible at
handling duplicate entries, and I have to dumbdown this database as much as
possible as the people that are using it, don't normally use a computer, for
anything.

So I need the dlookup to verify that the combination of Assy_mark_fk and
Weld_num_fk does not already exist in the table.

Well, tweaking your existing code should work. You were checking if the lookup
is null, I think you should check to see that if it is NOT null (i.e. already
there):

Dim strSQL As String
If Not IsNull(DLookup("[Weld_ID]", "[Welds]", _
"[ASSY_MARK_FK]= '" & assy_mark_fk & "'" _
" AND WELD_NUM_FK = '" & weld_num_fk & "'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."

Is this is in the form's BeforeUpdate event, set Cancel to true at this point.

 

[Denise Pollock]

As of this morning I have 3 primary keys now instead of 2. The form I have
is not attached to the table, all the fields are unbound. All my code is on
a create button which takes the data in the fields and inserts them into the
table. I no longer have the field Weld_ID since I can have 3 primary keys
that field becomes unecessary. I did create another field called ID that is
an autonumber.

So I need the lookup to check and see if the combination of Assy_mark_FK,
Weld_num_FK, and Job_Number on the form already exists in the table Welds, If
it exists it pops a message box that says Weld already exists. Otherwise it
runs my sql string which inserts the data into the table, then pops a message
box saying the weld was created.

So right now I have

If Not IsNull(DLookup("[ID]", "[Welds]", "[ASSY_MARK_FK]= '" & assy_mark_fk
& _
"'" And "[WELD_NUM_FK] = '" & weld_num_fk & "'" And "[Job_Number] = '" &
Job_Number & _
"'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."
Else
'Weld_ID doesn't exist
DoCMD.RunSQL strSQL
MsgBox "Weld Created"
End If

Its giving me a type mismatch error when I try and run it. I am assuming it
has something to do with [ID] cause I am not really sure what is supposed to
be there, that was where I had Weld_ID, which I am no longer using.

I did not know the primary key could be two fields, that simplifies thing. I
still need the lookup command to work, because access is rather horrible at
handling duplicate entries, and I have to dumbdown this database as much as
possible as the people that are using it, don't normally use a computer, for
anything.

So I need the dlookup to verify that the combination of Assy_mark_fk and
Weld_num_fk does not already exist in the table.

Well, tweaking your existing code should work. You were checking if the lookup
is null, I think you should check to see that if it is NOT null (i.e. already
there):

Dim strSQL As String
If Not IsNull(DLookup("[Weld_ID]", "[Welds]", _
"[ASSY_MARK_FK]= '" & assy_mark_fk & "'" _
" AND WELD_NUM_FK = '" & weld_num_fk & "'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."

Is this is in the form's BeforeUpdate event, set Cancel to true at this point.

 

[John Spencer]

Assuming that the three fields are all text fields then you need something
like the following.

DLookup("[ID]", "[Welds]",
"[ASSY_MARK_FK]= '" & assy_mark_fk & _
"' And [WELD_NUM_FK] = '" & weld_num_fk & _
"' And [Job_Number] = '" & Job_Number & "'")



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

As of this morning I have 3 primary keys now instead of 2. The form I have
is not attached to the table, all the fields are unbound. All my code is on
a create button which takes the data in the fields and inserts them into the
table. I no longer have the field Weld_ID since I can have 3 primary keys
that field becomes unecessary. I did create another field called ID that is
an autonumber.

So I need the lookup to check and see if the combination of Assy_mark_FK,
Weld_num_FK, and Job_Number on the form already exists in the table Welds, If
it exists it pops a message box that says Weld already exists. Otherwise it
runs my sql string which inserts the data into the table, then pops a message
box saying the weld was created.

So right now I have

If Not IsNull(DLookup("[ID]", "[Welds]", "[ASSY_MARK_FK]= '" & assy_mark_fk
& _
"'" And "[WELD_NUM_FK] = '" & weld_num_fk & "'" And "[Job_Number] = '" &
Job_Number & _
"'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."
Else
'Weld_ID doesn't exist
DoCMD.RunSQL strSQL
MsgBox "Weld Created"
End If

Its giving me a type mismatch error when I try and run it. I am assuming it
has something to do with [ID] cause I am not really sure what is supposed to
be there, that was where I had Weld_ID, which I am no longer using.

I did not know the primary key could be two fields, that simplifies thing. I
still need the lookup command to work, because access is rather horrible at
handling duplicate entries, and I have to dumbdown this database as much as
possible as the people that are using it, don't normally use a computer, for
anything.

So I need the dlookup to verify that the combination of Assy_mark_fk and
Weld_num_fk does not already exist in the table.

Well, tweaking your existing code should work. You were checking if the lookup
is null, I think you should check to see that if it is NOT null (i.e. already
there):

Dim strSQL As String
If Not IsNull(DLookup("[Weld_ID]", "[Welds]", _
"[ASSY_MARK_FK]= '" & assy_mark_fk & "'" _
" AND WELD_NUM_FK = '" & weld_num_fk & "'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."

Is this is in the form's BeforeUpdate event, set Cancel to true at this point.

 

[Denise Pollock]

Actually only assy_mark_fk is text field.

Assuming that the three fields are all text fields then you need something
like the following.

DLookup("[ID]", "[Welds]",
"[ASSY_MARK_FK]= '" & assy_mark_fk & _
"' And [WELD_NUM_FK] = '" & weld_num_fk & _
"' And [Job_Number] = '" & Job_Number & "'")



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

As of this morning I have 3 primary keys now instead of 2. The form I have
is not attached to the table, all the fields are unbound. All my code is on
a create button which takes the data in the fields and inserts them into the
table. I no longer have the field Weld_ID since I can have 3 primary keys
that field becomes unecessary. I did create another field called ID that is
an autonumber.

So I need the lookup to check and see if the combination of Assy_mark_FK,
Weld_num_FK, and Job_Number on the form already exists in the table Welds, If
it exists it pops a message box that says Weld already exists. Otherwise it
runs my sql string which inserts the data into the table, then pops a message
box saying the weld was created.

So right now I have

If Not IsNull(DLookup("[ID]", "[Welds]", "[ASSY_MARK_FK]= '" & assy_mark_fk
& _
"'" And "[WELD_NUM_FK] = '" & weld_num_fk & "'" And "[Job_Number] = '" &
Job_Number & _
"'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."
Else
'Weld_ID doesn't exist
DoCMD.RunSQL strSQL
MsgBox "Weld Created"
End If

Its giving me a type mismatch error when I try and run it. I am assuming it
has something to do with [ID] cause I am not really sure what is supposed to
be there, that was where I had Weld_ID, which I am no longer using.

On Thu, 25 Mar 2010 10:38:01 -0700, Denise Pollock

I did not know the primary key could be two fields, that simplifies thing. I
still need the lookup command to work, because access is rather horrible at
handling duplicate entries, and I have to dumbdown this database as much as
possible as the people that are using it, don't normally use a computer, for
anything.

So I need the dlookup to verify that the combination of Assy_mark_fk and
Weld_num_fk does not already exist in the table.
Well, tweaking your existing code should work. You were checking if the lookup
is null, I think you should check to see that if it is NOT null (i.e. already
there):

Dim strSQL As String
If Not IsNull(DLookup("[Weld_ID]", "[Welds]", _
"[ASSY_MARK_FK]= '" & assy_mark_fk & "'" _
" AND WELD_NUM_FK = '" & weld_num_fk & "'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."

Is this is in the form's BeforeUpdate event, set Cancel to true at this point.

.

 

[Denise Pollock]

And now I got it. Had to remove the 's around my number fields, which your
post made me realize. Didn't realize VB handled that the same as SQL.

Thanks for the help John S., and John V.

Assuming that the three fields are all text fields then you need something
like the following.

DLookup("[ID]", "[Welds]",
"[ASSY_MARK_FK]= '" & assy_mark_fk & _
"' And [WELD_NUM_FK] = '" & weld_num_fk & _
"' And [Job_Number] = '" & Job_Number & "'")



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

As of this morning I have 3 primary keys now instead of 2. The form I have
is not attached to the table, all the fields are unbound. All my code is on
a create button which takes the data in the fields and inserts them into the
table. I no longer have the field Weld_ID since I can have 3 primary keys
that field becomes unecessary. I did create another field called ID that is
an autonumber.

So I need the lookup to check and see if the combination of Assy_mark_FK,
Weld_num_FK, and Job_Number on the form already exists in the table Welds, If
it exists it pops a message box that says Weld already exists. Otherwise it
runs my sql string which inserts the data into the table, then pops a message
box saying the weld was created.

So right now I have

If Not IsNull(DLookup("[ID]", "[Welds]", "[ASSY_MARK_FK]= '" & assy_mark_fk
& _
"'" And "[WELD_NUM_FK] = '" & weld_num_fk & "'" And "[Job_Number] = '" &
Job_Number & _
"'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."
Else
'Weld_ID doesn't exist
DoCMD.RunSQL strSQL
MsgBox "Weld Created"
End If

Its giving me a type mismatch error when I try and run it. I am assuming it
has something to do with [ID] cause I am not really sure what is supposed to
be there, that was where I had Weld_ID, which I am no longer using.

On Thu, 25 Mar 2010 10:38:01 -0700, Denise Pollock

I did not know the primary key could be two fields, that simplifies thing. I
still need the lookup command to work, because access is rather horrible at
handling duplicate entries, and I have to dumbdown this database as much as
possible as the people that are using it, don't normally use a computer, for
anything.

So I need the dlookup to verify that the combination of Assy_mark_fk and
Weld_num_fk does not already exist in the table.
Well, tweaking your existing code should work. You were checking if the lookup
is null, I think you should check to see that if it is NOT null (i.e. already
there):

Dim strSQL As String
If Not IsNull(DLookup("[Weld_ID]", "[Welds]", _
"[ASSY_MARK_FK]= '" & assy_mark_fk & "'" _
" AND WELD_NUM_FK = '" & weld_num_fk & "'")) Then
'Weld_ID does exist
MsgBox "Weld already exists."

Is this is in the form's BeforeUpdate event, set Cancel to true at this point.

.