How to verify if record already exist in another table

[TXNEWT]

Hi,

I need some help with some VBA code using a DLookup. I am using a text box on a form to add a serial# to a table. When the serial# is entered I need to make sure that the serial number being added already exist in another table. If the record does not exist I need to display a message saying record does not exist and not allow the record to be added.

*Notes
The serial# is text not a number.
The serial number field in the table is called UNITID in both the 'Production' table and 'Consigned' table.

[zmbd]

+ Normalize your database and this situation doesn't occur
Database Normalization and Table Structures

However, if you insist on the current database design... and I really think you have a design issue:

(+) You can do this using recordsets too; however,
DCOUNT Function might be a better solution than DLOOKUP() - if the value isn't found the result is zero.

The MS link has several examples; however, the one thing of note there is that all of the examples show to build the criteria string within the DCOUNT().

However, building the string within the function makes troubleshooting most difficult as you cannot test for a properly formatted conditional string...
Their example:

=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

How I would modify this:

Dim zCriteria As String
...
zCriteria = "[ShipRegion] = 'CA'"
=DCount("[OrderID]", "Orders",zCriteria )

The most common problem is a malformed conditional string. Using this method, you can insert a Debug.Print ZCriteria and output the resolved string to the immediate pane ( [ctrlG] ) to check the result!

Now you simply test for a non-zero count.

[TXNEWT]

Thanks, but this is not quite what im looking for. There was an old post on this site that i tried to bump because it was very close to what i was looking for, but it got deleted by the mods for bumping...

This was the question
"Good morning. I have two tables (Batches) and (Archive). (Batches) is a linked table that contains all of the existing data and the other (Archive) I am keying data into, through a form. I want to be able to key into a form that writes to (Archive), but before it saves or moves to the next field, I want to make sure that the data already exists in (Batches) and prompt the user if it doesn't. The reason being is that I don't want to save any records in (Archive) that don't exist in (Batches). Also, just to be clear. There is only one field [BatchID] that I want to be able to check between the two tables."

and this was the solution;

 Private Sub BatchID_AfterUpdate()
Dim tmpBID As Long
 
   tmpBID = nz(DLookup("[BatchID]","Batches","[BatchID]=" & Me!BatchID),0)
   If tmpBID = 0 Then
      Msgbox "This BatchID does not exist in table Batches"
   End If
 
End Sub

This is what i'm looking for, but i think the fact that this is declared as a 'Long' may be my issue. I tried changing to a string, but I don't think I am getting the syntax correct. Here is what I have:

Private Sub UNITID_AfterUpdate()
 
Dim tmpBID As String
 
   tmpBID = Nz(DLookup("UNITID", "BARCODESUB", "UNITID= ' ME!UNITID' "), " ")
 
   If tmpBID = " " Then
      MsgBox "This Serial# does not exist in proir production manifest!", vbOKCancel
 
 
   End If
 
End Sub

When I run the program it gives me the msgbox even if the record exist.

Thanks!
JIN

[zmbd]

Ah yes... we do try to keep threads/questions separate otherwise the context becomes too muddied for any use :) As a work around, we ask that a new thread be started and that a link to the old thread be inserted along with the new question/criteria.
(in this case the old thread is: Need to verify data exists in another table )

Now for the issue at hand:
Take a look at line 5 of your code:
tmpBID = Nz(DLookup("UNITID", "BARCODESUB", "UNITID= ' ME!UNITID' "), " ")

Your criteria is literally being returned as:
UNITID= ' ME!UNITID'
as written, this is impossible to trouble shoot as I've indicated in my prior post regarding building the string within the function.

Instead build your string first:
zstr = "UNITID= '" & ME!UNITID & "'"

Then insert into the function
tmpBID = Nz(DLookup("UNITID", "BARCODESUB", zstr), " ")

This is what I was talking about in my post for the modifications to the example given in my post.

NOW

IF you do not plan to use the [UNITID] for anything else in the code, then returning that value is not needed, instead a simple count returned is enough; thus, no need to use the NZ() function.

comme ça:

Private Sub UNITID_AfterUpdate()
' 
 Dim tmpBID As String
 tmpBID = "UNITID= '" &  ME!UNITID & "'"
'Debug.print tmpBID
'
 If DCount("[UNITID]", "BARCODESUB",tmpBID)=0 then
   MsgBox "This Serial# does not exist in prior production manifest!", vbOKCancel
  End If
End Sub

> Now one can simply uncomment line5 to check if there is a properly resolved string depending on the value of ME!UNITID.

In any case, you have nothing to lose by trying my suggestion.

[TXNEWT]

Thanks ZMBD!

Sorry for the delayed response. Building the string first worked like a champ.

Now that this part is working, any advise on how to stop the record from being added when this condition is not met. As it is now I get the message box, but the record still gets added to the table.

Justin

[zmbd]

Related question moved to:
Check if Record already exists and prevent duplicated Record