Running query with variable input in where clause

[jpl458]

My Daily question. I have a form upon which is a button that is supposed run a query, where the user can input a Company Name, then the data is returned into a listbox. I probably went about this wrong. The query is prewritten and is in the queries list, and runs, but not on the form. Here is the code for the on click event in the button.

Me.DisplayQrylb.Visible = True
Me.DisplayQrylb.Height = 3700
Me.DisplayQrylb.Width = 24000
Me.DisplayQrylb.RowSourceType = "Table/Query"
Me.DisplayQrylb.RowSource = "QryInputCoName"
Me.DisplayQrylb.ColumnCount = 12
Me.DisplayQrylb.ColumnWidths = "1.5in;.8in;1.0in;.8in;2.2in;1in;1in;1.3in;1.5in;1in;1in;1in;1in"

The SQL for the query is as followsIt was created in the grid)

SELECT MasterTbl3.companyname1, MasterTbl3.callingnumber, MasterTbl3.calldate, MasterTbl3.starttime,
MasterTbl3.Duration, MasterTbl3.[1stcontact], MasterTbl3.[2ndcontact], MasterTbl3.[3rdCtctClass],
MasterTbl3.personcallbackno, MasterTbl3.recordingname
FROM MasterTbl3
WHERE (((MasterTbl3.companyname1)=[CompanyName]));

When running with breakpoint up until the RowSource. It opens a dialogue box, and when I fill it in, it goes back to the first satament in the code

Is VBA the best way to do this, or is there an easier way?

 

[plog]

Is VBA the best way to do this, or is there an easier way?

Yes. VBA is the best way to do this and there is an easier way.

First, I would open a report based on a query not the query itself. Second, I would put a drop down on your form so users can select a CompanyName instead of having them type it in exactly as it is in the database. Here's how you get there:

1. Strip out the WHERE clause of your query. Let all records go thru.
2. Build a report based on that query.
3. Add a combo box to your form to show all CompanyName values.
4. Modify your VBA code using DoCmd.OpenReport

DoCmd.OpenReport method (Access)

Office VBA reference topic

learn.microsoft.com

5. Make that work and open up your report when the button is clicked.
6. Build a filter string in VBA using your combo box and use it in the DoCmd.OpenReport so that the report opens to just the records you want.

 

[CJ_London]

@plog - where does a report come into this?

@jpl458

not sure why you are reconfiguring your list box every time? unless it's multi purpose? Either way, you can't use parameter queries like this in this way

I would just have a textbox called 'CompanyName' on your form and have the user enter the name there instead. You can then do one of two things after they have entered the name (perhaps the control after update event or a button click event)

1. modify your query WHERE clause to

WHERE (((MasterTbl3.companyname1)=[Forms]![NameOfYourForm]![CompanyName]));
then either use your code
Me.DisplayQrylb.RowSource = "QryInputCoName"

or if the listbox is not multi purpose just
Me.DisplayQrylb.requery

or 2 (assuming your listbox is not multipurpose)
copy/paste the sql exactly as you have it to your rowsource and use
Me.DisplayQrylb.requery

 

[jpl458]

@plog - where does a report come into this?

@jpl458

not sure why you are reconfiguring your list box every time? unless it's multi purpose? Either way, you can't use parameter queries like this in this way

I would just have a textbox called 'CompanyName' on your form and have the user enter the name there instead. You can then do one of two things after they have entered the name (perhaps the control after update event or a button click event)

1. modify your query WHERE clause to

WHERE (((MasterTbl3.companyname1)=[Forms]![NameOfYourForm]![CompanyName]));
then either use your code
Me.DisplayQrylb.RowSource = "QryInputCoName"

or if the listbox is not multi purpose just
Me.DisplayQrylb.requery

or 2 (assuming your listbox is not multipurpose)
copy/paste the sql exactly as you have it to your rowsource and use
Me.DisplayQrylb.requery

During dinner I canned the idea of a button. Thanks for the answer, it is very useful

Yes. VBA is the best way to do this and there is an easier way.

First, I would open a report based on a query not the query itself. Second, I would put a drop down on your form so users can select a CompanyName instead of having them type it in exactly as it is in the database. Here's how you get there:

1. Strip out the WHERE clause of your query. Let all records go thru.
2. Build a report based on that query.
3. Add a combo box to your form to show all CompanyName values.
4. Modify your VBA code using DoCmd.OpenReport

DoCmd.OpenReport method (Access)

Office VBA reference topic

learn.microsoft.com

5. Make that work and open up your report when the button is clicked.
6. Build a filter string in VBA using your combo box and use it in the DoCmd.OpenReport so that the report opens to just the records you want.

Thanks you so much