"ARGUMENT NOT OPTIONAL" Help

[Travis33]

I HAVE THIS CODE BELOW THAT i AM TRYING TO HAVE MY FORM WHEN I ENTER IN NUMBERS INTO A COMBO BOX SCROLL THROUGH THE RECORDS THAT MATCH PART OR ALL OF THE STRING.

**********************************************************
Dim db As database, rst As Recordset, strsql As String
Set db = CurrentDb
Str = "select * from [mtusparts01 query]= " & Me.[partnumber] & " * "
Set rst = db.openrecordset(strsql)
If rst.RecordCount >= 1 Then
Me.RecordSource = strsql
Else
Exit Sub
End If
rst.Close
Set db = Nothing
***********************************************************

when I try to compile the code it gives me an error on
me.[partnumber]
it tells me "Argument Not Optional"
How do I fix this so my code will run
Thank You,
Travis

 

[RickSpr]

The SQL statement you gave is malformed. It should be something like:
Str = "select * from [mtusparts01 query] WHERE (something) LIKE " & Me.[partnumber] & " * "

For "(something)" substitute the query field name that you want to search.

 

[scking]

Let's assume you have intellisense turn on and the Me.[partnumber] is a control on your form. You should always name your controls based on the type control, i.e. txtpartnumber otherwise there is a much greater liklihood of confusing the system with fields in the table. With intellisense on you should then reference your control as Me.txtpartnumber. The second you don't see the list of valid controls pop up with intellisense you know something is wrong.

Another assumption we must make based on your construct is that partnumber does not contain anything but numeric characters. If it also contains other characters like a dask then you need to construct it as a string i.e. & """" & Me.txtpartnumber & """" & "*;" This construction can be checked to verify it in debug by stopping the processing right on the docmd.runsql line. I think this will solve your problem.