rs.NoMatch Not behaving the way it should

[craig1988]

Hi All



I am trying to return information from a record to an unbound text box based on information in a bound text box. I have the procedure working perfectly in an Form_Current() when it is retrieving information from a table. When I try and replicate the code and base it on a query I get an error when it fails the "If rs.NoMatch = True" criteria.

The error says Invalid use of Null

Anyone have any ideas??

See the below code.

Dim rs As DAO.Recordset
    Dim LastSerial As String
    
    Set rs = CurrentDb.OpenRecordset("qry11OrderHistory", dbOpenSnapshot, dbReadOnly)
    
    rs.FindLast "AccNo='" & Me.AccNo & "'"
    
    LastSerial = DLookup("EndSerialNo", "qry11OrderHistory", "AccNo ='" & Me.AccNo.Value & "'")
    
    If rs.NoMatch = True Then
        MsgBox "No Details with that account no on file", vbInformation, "Oops"
        Exit Sub
    Else
        Forms![frmApprove11]![txtSerialNo] = LastSerial
    End If

[John_G]

The error may be here:

LastSerial = DLookup("EndSerialNo", "qry11OrderHistory", "AccNo ='" & Me.AccNo.Value & "'")

You have declared LastSerial as a string, but if the dllokup finds no match, it returns a Null, which causes an error when you attempt to assign the value to the string variable.

Move that line to the Else part of your If and it should work.

Actually, you can do what you want without using a recordset by using the Dlookup and the Nz function:

 
    Dim LastSerial As String
    LastSerial = nz(DLookup("EndSerialNo", "qry11OrderHistory", "AccNo ='" & Me.AccNo.Value & "'"),"N/A")
    
    If lastSerial = "N/A"  then
        MsgBox "No Details with that account no on file", vbInformation, "Oops"
        Exit Sub
    Else
        Forms![frmApprove11]![txtSerialNo] = LastSerial
    End If

The Nz function assigns a value of "N/A" to LastSerial instead of an actual serial number.

[craig1988]

John_G

All Makes perfect sense... (blushes that he should have got this)

Worked perfectly, and it will come in handy further down the line.

Thank you!!!