Run time error '3075': Syntax error (missing operator) in query expression

[FusionAtomix]

hello,

I'm trying to get my list box database to open up another form that contains more information on the selected record. I can get it to open the form just fine, but it always opens o the first record, rather than the one I have selected.

My error message is: Run-time error '3075': Syntax error (mising operator) in query expression 'Serial Number = 7632-562'.

All my records in the database are made up numbers and letters, as I am just testing it out now before using real entries.

My code is:

Private Sub SearchList_DblClick(Cancel As Integer)
 
    DoCmd.OpenForm "frmInfo", acNormal, , "Serial Number = " & Me![SearchList].Value
 
End Sub

the listbox is called Search List, and the form i want to open is called frmInfo.

Thanks to anyone with advice!

[DJRhino1175]

If it was me, I would put in the query of the form under the serial column:

Like[Enter Serial No.]

And have your code just open the form.

[FusionAtomix]

@DJRhino1175
I have a textbox up at the top that is used for searching through the databse by serial number, and the results are displayed in the listbox, so I though the best idea was to be able to click on one of the records in the listbox and just have that one come up.

[twinnyfo]

It appears the issue is that you are searching for a record that has a text serial number, but using the syntax for a numeric value. Just because a "Serial Number" looks like numbers, it is not necessarily numeric (notice that there is a dash in your Serial Number).

Try this:

Private Sub SearchList_DblClick(Cancel As Integer)
 
    DoCmd.OpenForm "frmInfo", _
                   acNormal, , _
                   "Serial Number = '" & Me.SearchList & "'"
 
End Sub

Also note, there is no need to refer to the "value" of the control, as the Value is the default reference.

Hope this hepps!

[FusionAtomix]

@twinnyfo
Hey thanks for you response! I didn't think about it that way, good insight. I copied and pasted the code you gave me into access but i still got the same error, can you think of a reason why that would happen?

[twinnyfo]

Square braces around your field name.

Private Sub SearchList_DblClick(Cancel As Integer)
 
    DoCmd.OpenForm "frmInfo", _
                   acNormal, , _
                   "[Serial Number] = '" & Me.SearchList & "'"
 
End Sub

You had a space in your Field Name, which you should try to avoid in the future, to avoid such errors.

Hope this hepps.

[FusionAtomix]

@twinnyfo
Works perfectly! thank you!

[twinnyfo]

Glad I could be of service. Let us know if you need any more hepp!

[NeoPa]

Twinny has already answered perfectly adequately but it may help to have a look at Quotes (') and Double-Quotes (") - Where and When to use them for a fuller understanding.