DLookup gives Invalid Use of Null Error

[CBenfer]

I'm sure I'm missing some sort of parentheses or something simple, but here goes. I'm trying to run the code below. The basic idea is that I need to insert a record into a table and then return the Key field so that I can use it to populate another table. This was working just fine for a while, then it stopped and naturally I can't think of anything I did to make it not work. I get an Invalid Use of Null error at the bolded step. Any help would be greatly appreciated.


Dim SQL As String
Dim SpecID As Long
Dim VerifySpec As Variant

' Check for empty strings
If Len(Me.txtNewPartNumber & vbNullString) = 0 Then
Response = MsgBox("Part Number cannot be blank.", vbExclamation, "Missing Data")
Exit Sub
End If

If Len(Me.txtNewOperation & vbNullString) = 0 Then
Response = MsgBox("Operation cannot be blank.", vbExclamation, "Missing Data")
Exit Sub
End If

If Len(Me.txtNewMeasurement & vbNullString) = 0 Then
Response = MsgBox("Measurement Name cannot be blank.", vbExclamation, "Missing Data")
Exit Sub
End If

' Check to see if the specification already exists
VerifySpec = DLookup("sSpecificationID", "tblSpecifications", _
"[sOperation] = '" & Me.txtNewOperation & "'" & _
" And [sPartNumber] = '" & Me.txtNewPartNumber & "'" & _
" And [sMeasurementName] = '" & Me.txtNewMeasurement & "'")

If VerifySpec > 0 Then
Response = MsgBox("The values you have entered match an existing specification. Edit that specification or create another.", vbExclamation, "Duplicate Data")
Exit Sub
End If

' Enter the values into the table
SQL = "INSERT INTO tblSpecifications (sOperation, sPartNumber, sMeasurementName) " & _
"VALUES (" & Me.txtNewOperation & ",'" & Me.txtNewPartNumber & "','" & Me.txtNewMeasurement & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

' Get the Autonumber SpecID from the table for the record just created
SpecID = DLookup("sSpecificationID", "tblSpecifications", _
"[sOperation] = '" & Me.txtNewOperation & "'" & _
" And [sPartNumber] = '" & Me.txtNewPartNumber & "'" & _
" And [sMeasurementName] = '" & Me.txtNewMeasurement & "'")

 

[pr2-eugin]

SpecID is declared as Long, and Dlookup will return a Null when the criteria is not met. So you have two options use Variant data type for SpecID or the better option use a Nz() function around the DLookup.

 

[plog]

Its got to be your criteria argument of the Dlookup, so find out what that string actually is. Instead of running the Dlookup, spit out the criteria so you can see it with your own eyes.

 

[CBenfer]

Well, the reason SpecID is not a Variant is because it should never be Null. I just added the record, so there HAS to be something there that matches, right? Or have I just looked at the code so long that I'm missing something dumb?

For reference, sSpecificationID is an autonumber field.

I should also note that when I look in tblSpecifications the record that I just created appears to be there. That's why I was thinking this had to do with some sort of parentheses. One more thing that I did check is the three text boxes referenced in the command show up with what looks like the proper values when I hover over them in the debugger.

Thanks.

 

[CBenfer]

OK, I played around with looking at the string and it looks like it has to do with the way I'm storing one of the fields. It is supposed to be text, but somehow the leading "0" if I put in "060" or something like that is being truncated. I'll see if I can fix it, otherwise I might be back soon.

 

[Minty]

The same Dlookup is functioning okay when you assign it to VerifySpec earlier in the code which is a Variant, correct? If it is then that must be the reason?

Debug.print VerifySpec to see what it returns?

Then do the lookup against VerifySpec after you run the SQL - if it works that is your problem.

 

[vbaInet]

' Get the Autonumber SpecID from the table for the record just created
SpecID = DLookup("sSpecificationID", "tblSpecifications", _
"[sOperation] = '" & Me.txtNewOperation & "'" & _
" And [sPartNumber] = '" & Me.txtNewPartNumber & "'" & _
" And [sMeasurementName] = '" & Me.txtNewMeasurement & "'")

pr2-eugin and plog have already mentioned what the problem is, one or more of the criteria in red isn't bringing up a matching record which is resulting in the DLookup returning Null and SpecID (being a Long) cannot accept Null. I believe CBenfer has already found the root cause and is working on it.