syntax error 3075 1

[BradCollins]

Hi all,

I have read through the countless posts on this problem and I still cant see where I have gone wrong, I am hoping someone can see where I have messed up with this code. When I run it as a runtime I get the error

3075 : Syntax error (missing operator) in query expression 'aa12-aa12-aa12'

The feild is a serial number field and I want it to ensure there is no existing serial number, of course the numbers can take any format including having alpha characters.

    Dim SerialNo As String
    Dim strSerial As String
    
    SerialNo = Me.fldSerialNo.Value
    strSerial = Nz(DLookup(SerialNo, "tblSW", "fldSerialNo = """ & Me.fldSerialNo & """"), "")
    
    If Len(strSerial) > 0 Then
        MsgBox "This serial number already exists, please" & Chr$(13) & "enter a diferent number.", _
        vbInformation, "The Final Solution"
        Cancel = True
        Else
    End If

Thanks in advance to anyone whoc an assist me

 

[nobull613]

In the Dlookup portion try
DLookup("[SerialNo]", "tblSW", "[SerialNo] = '" & Me.fldSerialNo & "'")
Unless there is a field in the table tblSW called fldSerialNo the where portion isn't right. The above is assuming that you are comparing the form field fldSerialNo to the table field SerialNo.

 

[PercyN]

Or
SerialNo = Nz(Me.fldSerialNo.Value,0)
strSerial = Nz(DLookup(SerialNo, "tblSW", "fldSerialNo = """ & SerialNo & """"), "")

 

[PHV]

The simplest way:

If Not IsNull(DLookup("SerialNo", "tblSW", "SerialNo='" & Me!fldSerialNo & "'")) Then
  MsgBox "This serial number already exists, please" & vbLfCr & "enter a diferent number.", _
        vbInformation, "The Final Solution"
  Cancel = True
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, I meant this:

If Not IsNull(DLookup("fldSerialNo", "tblSW", "fldSerialNo='" & Me!fldSerialNo & "'")) Then
  MsgBox "This serial number already exists, please" & vbLfCr & "enter a diferent number.", _
        vbInformation, "The Final Solution"
  Cancel = True
End If

BTW, it's not a good practice to have a control with the very same name than a field.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BradCollins]

PHV,

Thank you very much, I have given you a star.

Your response was spot on and I appreciate your help.
Also as I am still learning the ins and outs of access I appreciate the added tip about the field name and control being the same name, I see how that can cause confusion.

Thank you