get query criteria from a form field (1 Viewer)

[Gr3g0ry]

i have a query that will return all records for a customerId, however i want to link this query to a form so that the correct customerId will be passed to the query to be used as criteria.

in the Criteria field, i put [Forms]![AddNewRental]![Combo19] but it works only sometimes andit keeps hanging onto one value.

please help

 

[plog]

What value is getting passed to the query from Combo19? No, don't use your intuition, look at the form and assume its the value you see; actually find out.

Remove the criteria for your query and add a new field:

TestValue: [Forms]![AddNewRental]![Combo19]

Run your query again and tell me what value appears in TestValue

 

[pbaldy]

Also make sure you are exiting the combo before running the query. Selecting a value should work, or hitting tab/enter after typing in a value. The query won't see an uncommitted value.

 

[Gr3g0ry]

What value is getting passed to the query from Combo19? No, don't use your intuition, look at the form and assume its the value you see; actually find out.

Remove the criteria for your query and add a new field:

TestValue: [Forms]![AddNewRental]![Combo19]

Run your query again and tell me what value appears in TestValue

i did as instructed, and the new field shows no info, however the query returns all the data for all the customers.

1. i wanna preview a report via a button.
2. ive attached the report to a button.
2. the report is made from/attached to a query.
3. the query is supposed to get its ID from the combo box on the form to populate the report.

 

[Gr3g0ry]

Also make sure you are exiting the combo before running the query. Selecting a value should work, or hitting tab/enter after typing in a value. The query won't see an uncommitted value.

how exactly do you exit the combo before running the query ?

 

[pbaldy]

how exactly do you exit the combo before running the query ?

As mentioned: "Selecting a value should work, or hitting tab/enter after typing in a value."

Clicking on your button would accomplish the same thing in production. I've just run into that while testing. I'd type something in the form and run the query, not realizing that I hadn't "committed" the value so the query didn't see it. Not saying that's your issue, just making sure it gets eliminated as a possible cause.

 

[plog]

1. i wanna preview a report via a button.
2. ive attached the report to a button.

I suggest not filtering the query, but the report. Presumably you are running a Docmd.OpenReport (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport) to open your report. One of the arguments it takes is a filter string. Use that method instead of linking the query to the form. The added bonus of this method is you can spit out that filter string to see exactly what it is.

 

[Gr3g0ry]

As mentioned: "Selecting a value should work, or hitting tab/enter after typing in a value."

Clicking on your button would accomplish the same thing in production. I've just run into that while testing. I'd type something in the form and run the query, not realizing that I hadn't "committed" the value so the query didn't see it. Not saying that's your issue, just making sure it gets eliminated as a possible cause.

ok thanks, but im a nub at this. could you provide more details plz ?

 

[pbaldy]

ok thanks, but im a nub at this. could you provide more details plz ?

I'm not sure how to provide more details. After entering a value, hit the tab or enter key.

 

[Gr3g0ry]

I suggest not filtering the query, but the report. Presumably you are running a Docmd.OpenReport (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport) to open your report. One of the arguments it takes is a filter string. Use that method instead of linking the query to the form. The added bonus of this method is you can spit out that filter string to see exactly what it is.

ok thanks, but im a nub at this. could you provide more details plz ? how do i go about filtering a report ?
im opening the report to preview using the predefined button provided by access.

 

[Gr3g0ry]

I'm not sure how to provide more details. After entering a value, hit the tab or enter key.

sorry i replied to the wrong response

 

[pbaldy]

sorry i replied to the wrong response

No problem. This method, but with OpenReport instead of OpenForm:

Open a second form to the record

www.baldyweb.com

 

[pbaldy]

I suggest not filtering the query

My general methodology varies between the 2 main methods discussed here: criteria in the query or arguments of OpenForm/OpenReport. I'm not saying it's the best way, it's just what I've grown into.

As a rule, if a given criteria will always be used, I'll use a query criteria. If a criteria is only used sometimes, I'll use the wherecondition argument. For example, I've got a db of taxi data. There are multiple companies, drivers, and 20 years of data. Data will always be filtered by company and date range, so those are in the query. Driver is sometimes a filter, sometimes not, so I'll add that to OpenForm/OpenReport as needed.

I guess I'm lazy, so if a criteria will always be applied, I don't want to have to code a filter for every form/report that might use it.

 

[plog]

how do i go about filtering a report ?

Check the documentation for DoCmd.OpenReport (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport). In short it would work like this:

Sub OpenReport_click()
  ' when Open Report button is clicked opens YourReportNameHere and applies criteria

  strReport = "YourReportNameHere"
  ' name of report to open

  strFilter = "[customerid] = " & [Forms]![AddNewRental]![Combo19]          
  ' filter criteria to apply to report

   DoCmd.OpenReport strReport, acPreview, , strFilter
  ' opens report using filter

End Sub

I didn't test the above so it might not work, but that's the basic code to do it. When it doesn't work spit out strFilter to see exactly what it contains.

 

[zeroaccess]

Another way is to create a tblReports with the following fields:

ReportID
ReportName (actual names of the report objects)
ReportDescription

tblReports is set as the record source for the form where the combo box resides, maybe a special popup frmReports.

After update of the combo box which has your reports to choose from, DoCmd.Applyfilter to the applicable table row. A text box nearby will display the report description, and a Run Report button will open the report which is named in ReportName.

Any text boxes nearby can be used for criteria as described by plog.

I use this method with the DoCmd.OutputTo method to export queries to Excel.

 

[Gr3g0ry]

Check the documentation for DoCmd.OpenReport (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport). In short it would work like this:

Sub OpenReport_click()
  ' when Open Report button is clicked opens YourReportNameHere and applies criteria

  strReport = "YourReportNameHere"
  ' name of report to open

  strFilter = "[customerid] = " & [Forms]![AddNewRental]![Combo19]         
  ' filter criteria to apply to report

   DoCmd.OpenReport strReport, acPreview, , strFilter
  ' opens report using filter

End Sub

I didn't test the above so it might not work, but that's the basic code to do it. When it doesn't work spit out strFilter to see exactly what it contains.

thanks

 

[Gr3g0ry]

Another way is to create a tblReports with the following fields:

ReportID
ReportName (actual names of the report objects)
ReportDescription

tblReports is set as the record source for the form where the combo box resides, maybe a special popup frmReports.

After update of the combo box which has your reports to choose from, DoCmd.Applyfilter to the applicable table row. A text box nearby will display the report description, and a Run Report button will open the report which is named in ReportName.

Any text boxes nearby can be used for criteria as described by plog.

I use this method with the DoCmd.OutputTo method to export queries to Excel.

thanks

 

[Gr3g0ry]

After trying multiple suggestions, i got it working guys. thanks a million