How do I search for existing records without displaying them? Part 2 1

[shellj]

My database stores stock details. Once records are added to the Stock table using a form, a unique number is generated using the information input. For example, items added from the Designer Fornarina have a code 18, if the item is a skirt the code is 12, if the colour is black the code is 01 and if the size is Small the code is 01. The number should look as follows: 181299990101. Once this number is generated, I need to search the Stock table for this number to ensure there are no duplicates before I save the record. If a record exists with this number but the Designer, Description, Colour and Size are the same, I need to increment '9999' by one.

Can someone help me with this? I'm not sure of the code I need to write.

Thanks in advance

 

[WayneRyan]

shellj,

What you need is the DLookUp function:

Dim varIsFound As Variant

varIsFound = DLookUp("[SomeField]", "YourTable", "[SomeField] = " & 
Me.SomeNumber)
If IsNull(varIsFound) Then
   MsgBox("No Numbers")
Else
   MsgBox("Some Numbers")
End If

Wayne

 

[shellj]

Thanks Wayne, I'll give it a try.

 

[shellj]

Wayne,

Sorry to bother you. I have tried your code but i keep getting " Data type mismatch in criteria expression". When I highlight varisfound it says "varisfound = empty" and me.productcode has the number i'm looking for. Am i doing something wrong?

Thanks

 

[WayneRyan]

shellj,

If the field that you are searching on is not numeric, then
you need to add the single-quotes.

Dim varIsFound As Variant

varIsFound = DLookUp("[SomeField]", "YourTable", "[SomeField] = '" & 
Me.SomeNumber & "'")
If IsNull(varIsFound) Then
   MsgBox("No Numbers")
Else
   MsgBox("Some Numbers")
End If

Wayne

 

[shellj]

Thanks Wayne! That worked a treat!

shellj