Message Box When Duplicate Information Is Enterred

Y

Yael

I have created a DB that will be used by a few agents selling a certain
number of lots. I need help creating a message box that will inform the user
that the lot number that was just entered has been used earlier. I want the
user to still be able to save the new record using the same lot number (this
is of course not the primary key) if the previous record using the same lot
number has been cancelled. I already have a combobox named Client Status with
"cancelled", "closed" and "contract".
Please let me know if I need to provide more information if my question is
not clear.
Thank you!
 
A

Arvin Meyer [MVP]

If there is a large number of records, you can use a recordset to look up
the value and if the recordcount > than 0, Open a msgbox telling the user
that there is an existing record and giving him/her the option of cancelling
or continuing. If there are less than a few thousand records, using DLookup
or DCount is a slight bit easier. Air code:

Sub txtLotNumber_AfterUpdate()
Dim x As Variant

x = DCount("LotNumber", "YourTable", "LotNumber = " & Me.txtLotNumber)

If x >= 1 Then
If MsgBox ("Lot Number found. save anyway", vbYesNo, "Found") = vbYes
Then
DoCmd.RunCommand acCmdSaveRecord
End If
End If
End Sub
 
Y

Yael

Thank you for your answer!

It will be less than a few thousand records, we are selling 250 lots, and
there are always a few canceled ones, but the total records will still not
come close to a thousand; so DCount is probably the best way to go. I do
however have a question as I might not have been totally clear when I posted
my initial question. I only want the messagebox to pop up if the user will
asign a lot number that has already been used, not everytime assigning a lot
number. If the lot number has been used earlier on AND the client status
combobox sais "Canceled" I want the message box to pop up.

I am using a combobox listing the numbers 1-250 to store the lot number. I
tried changing the code to match my combobox (Combo215) instead of the
standard textbox:

Sub Combo215_AfterUpdate()

'txtLotNumber is replaced with Combo215
'For reference: My table is called RegisteredClientInformation
'I also replaced LotNumber with Lot, just as it is called in my table

Dim x As Variant

x = DCount("Lot", "Registered Client Information", "Lot = " & Me.Combo215)

If x >= 1 Then
If MsgBox("Lot number has been used previously, but was canceled. Save
new client?", vbYesNo, "Found") = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
End If
End If
End Sub

I keep getting an error message:
'"Runtime error 3464":
Data type mismatch in criteria expression.'
The data type of my Lot combobox is text. Do you have any suggestions?

Thank you for your time and help!
 
Top