Enter Parameter Value?

[ctroyp]

Hello,

I am fairly new to Access and I am trying to find a way to print results from a query into a listbox on a form. I actually have it working, but how can I modify it to keep the "Enter Parameter Value" dialog box from popping up? I am reading a text entry from the first textbox, appending it to the query, and setting the query to the rowsource after clicking the "Search" button.

Here is the code for the button click event:
-----------------------
Option Compare Database

Private Sub cmdSearch_Click()
On Error Resume Next

Dim sSQL As String

'Start the SQL statement.
sSQL = "SELECT Customers.CompanyName FROM Customers WHERE Customers.CompanyName = Me.txtSQL.Value;"

'Assign the query to the results listbox.
Me.lstResults.RowSource = sSQL

End Sub
-----------------------

Thanks in advance...

 

[ZmrAbdulla]

sSQL = "SELECT Customers.CompanyName FROM Customers " & _
                 "WHERE Customers.CompanyName='" & Me.txtSQL.Value & "’ "

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[ctroyp]

Well, the dialog box doesn't pop up, but nothing else happens either. Now it doesn't even show my results???

 

[ctroyp]

Also, how could I have the query return the CompanyID along with the CompanyName? The example result would be "My Company Name, My Company ID"

Thanks...

 

[PHV]

Perhaps this ?
sSQL = "SELECT CompanyName,CompanyID FROM Customers " & _
"WHERE CompanyName Like '" & Me.txtSQL.Value & "*’"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

Is it just me missing something (again), or is it a curly thingie at the end of the statement and an ordinary single quote before the criterion?

Roy-Vidar

 

[PHV]

Good catch Roy !
sSQL = "SELECT CompanyName,CompanyID FROM Customers " & _
"WHERE CompanyName Like [tt]'" & Me.txtSQL.Value & "*'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ctroyp]

Okay...now the Enter Parameter Value dialog box opens again.

Also, the CompanyID does not display with the CompanyName.

Any ideas...I appreciate your help very much.

 

[PHV]

the Enter Parameter Value dialog box opens again
And which parameter is asked ?
Are you sure that the Customers table has fields named CompanyName and CompanyID ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ctroyp]

I found why the dialog was popping up and fixed it. I had forgotten to remove some code.

Now I cannot get any results to display. I am entering "Chloe's Toys" in the textbox and clicking the search button. No results are displayed in the list box.

I confirmed that the text, "Chloe's Toys" is in the field named, "CompanyName", which is in the "Customers" table...CompanyID is defined as well.

???

 

[PHV]

sSQL = "SELECT CompanyName,CompanyID FROM Customers WHERE " & _
"CompanyName=[tt]'" & Replace(Me.txtSQL.Value, "'", "''") & "'" [/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ctroyp]

That did it! It works. Only thing is now is that it is asking for a parameter for the CustomerID???

 

[PHV]

What is your REAL SQL code ?
Are you sure your selected fields are correctly spelled and really exist in the table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ctroyp]

The fields definitely exist and are not mispelled. I just confirmed again.

The only SQL statement I am using is the one in the VB code.

 

[PHV]

So where is CustomerID coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ctroyp]

Just figured it out...

I didn't realize I needed to create a query outside of what I was already doing in the code. I understand the concept now thanks to your help.

It works perfect now.

One more question:
For "Chloe's Toys", how would I get the results to show other matches if I just searched for the text "toy"? I would like it to return "Chloe's Toys", and "Toy Attic", etc...

Know what I mean?

 

[PHV]

sSQL = "SELECT CompanyName,CompanyID FROM Customers WHERE " & _
"CompanyName Like [tt]'*"[/tt] & Replace(Me.txtSQL.Value, [tt]"'", "''") & "*'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ctroyp]

That did it!

Thank you so much for the help.

One more thing I am going to do is find a way to display the field name above the two results columns.

Thanks again,
Troy

 

[PHV]

Have a look at the ColumnHeads property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ctroyp]

Yeah, just found that right before you posted.

Thanks again!