rowsource asking for parmamiters (1 Viewer)

[cybertyk]

Good afternoon,
i shall cut to the chass as they say , my form has a list on it and in the list is a list of companys when a company name is pressed it loads the relevent record fine. how ever i wanted to add a company search box so i put a text box in and called it txtsearch, and added a buttion with the following code

Private Sub cmdSearch_Click()
Dim strSql As String
strSql = "SELECT tblCustomer.ID, tblCustomer.[Company Name], tblCustomer.Delete FROM tblCustomer WHERE (((tblCustomer.[Company Name]) Like '*' & " & Me.txtSearch.Value & " & '*') AND ((tblCustomer.Delete)=False));"
If Me.txtSearch.Value = Null Then
Me.lstCustomer.RowSource = "SELECT tblCustomer.ID, tblCustomer.[Company Name], tblCustomer.Delete FROM tblCustomer WHERE (((tblCustomer.Delete)=False));"
GoTo foo
Else
Me.lstCustomer.RowSource = strSql
End If
foo:
Me.txtSearch.Value = Null
End Sub

this code works fine how ever when i press the buttion and run the code i get a "Enter Parmamaiter Value" msgbox that has what i tyoed in for its value i then have to reenter the search Criteria then hit ok for it to filter the results.

why do i get this box and is there a way to stop it?

also i have debuged the code and i get the msg box on this line of code
Me.lstCustomer.RowSource = strSql

any help you can give would be appricated!


thanks

cybertyk

 

[vbaInet]

Welcome to AWF!

Your field name "DELETE" is a reserved keyword and needs to be changed to something else. After you've done that, use this:

strSql = "SELECT ID, [Company Name], [B][COLOR=Red]Delete [/COLOR][/B]FROM tblCustomer WHERE [Company Name] Like '*" & Me.txtSearch.Value & "*' AND [COLOR=Red][B]Delete[/B][/COLOR]=False;"

 

[chriscwilson]

One way of getting to the answer would be to put a message box displaying the text in strSQL as the next line after the assignment (i.e. after strSQL=)

MsgBox "strSQL is " & strSQL

You may see that the SQL statement is not properly formed because there are no quotes around the company name, which is why you are getting the parameter request?

 

[cybertyk]

thank you i will try this tomorrow and should have known this was a reserved name DUH lol



many thanks

 

[vbaInet]

Goodie! Let us know how you get on.

 

[cybertyk]

well i have changed the reseaved name and the sql command i cant beleave i made such a simple mistake lol


thank you both for your help, it works a treat. XD

 

[vbaInet]

Great! Glad we could help.

 

[cybertyk]

no probleam, i do have on more question which has been bugging me for ages after i close and open accsses a few times it wont let me open anymore accsses applications untill i end task ACCSSES.exe or what ever, is this due to my poorly writen code or is it microsoft bug?

 

[vbaInet]

I can't see all the code in your database so it's hard to tell. It looks like when you close Access it doesn't kill the process.

Have you tried it with a blank database? Or maybe with one of the template databases that come with Access?

 

[cybertyk]

with a blank one its ok :rant: its my code so ill have to tooth comb it and make sure i have written it well then lol

this might take a while

 

[vbaInet]

Create a new blank database and import all your objects into that one. See if that solves the problem.

 

[cybertyk]

okies ill try that now and im gonna also remove all the redundant code XD

time for a spring clean XD

 

[vbaInet]

Remember to close and set to Nothing any objects that you created.

Keep us informed.