DLOOKUP with IF Function in MS ACCESS DB

[Parminder Singh]

Hello Guys,

Can anyone look into this code for me?
Not getting IF statement working!
.................................................. ........................................


Private Sub Machine_drawing_Number_AfterUpdate()
Dim MachNum As String
Dim Mat As String
Dim Tim As String
Dim stLinkCriteria2 As String
Dim Result As String


Mat = Me.Combo222.Value
Result = Me.Machine_drawing_Number.Value
stLinkCriteria2 = "[Combo222]= " & "'" & Mat & "'"


MachNum = DLookup("[Casting drawing Number]", "SlidingStem_Table", "[Machine_drawing_Number]= '" & Me.Machine_drawing_Number & "'")
Tim = DLookup("[Casting drawing Number]", "SlidingStem_Table", stLinkCriteria2)


If MachNum = Tim Then
MsgBox "This Machine Drawing Number : " & Result & " has already been entered in database with same Material" _
& vbCr & vbCr & "Please check the Machine number again.", vbInformation, "Duplicate information."
Me.Undo
Else
MsgBox "You can go ahead!"

End If

End Sub

[CJ_London]

step through the code and check you are getting the expected values

however this looks wrong

stLinkCriteria2 = "[Combo222]= " & "'" & Mat & "'"

implies you have a field in your SlidingStem_Table called Combo222

[June7]

Should post lengthy code within CODE tags to retain indentation and easier readability.

What does 'not work' mean - error message, wrong results, nothing happens?

DLookup returns Null if no match. Cannot do comparison with Null. Review http://allenbrowne.com/casu-12.html

The only variable type that can hold Null is Variant. Your code will error if DLookup returns Null because String variable cannot hold Null.

stLinkCriteria2 makes no sense. I presume Combo222 is combobox on form, not a field in table, so how can the DLookup apply filter criteria? Replace with correct field name.

Try both criteria together.

If Not IsNull(DLookup("[Casting drawing Number]", "SlidingStem_Table", "[Machine_drawing_Number]= '" & Result & "' AND [Material]='" & Mat & "'")) Then

Example with DCount, which returns 0 if no match.

If DCount("*", "SlidingStem_Table", "[Machine_drawing_Number]= '" & Result & "' AND [Material]='" & Mat & "'") > 0 Then