Running query in VBA with where condition

[path479]

I have a query set up in Access without any criteria or parameters set.

In VBA, I can use DoCmd.OpenQuery to run it. However it doesn't have the parameter to run it with where condition. Whereas DoCmd.OpenReport you can

Is there any way to run the query in VBA with where condition? Or is there anyway to get around it without changing the original query?

 

[Ranman256]

docmd.openquery "myQuery",,,"where condition"

 

[Minty]

docmd.openquery "myQuery",,,"where condition"

I don't think this is correct. There is no Where clause when opening a query.

You can amend or create a query definition in VBA and then open it or use it as a recordset / recordsource.

 

[Ranman256]

oops , sorry, that was openForm
for form filters you have a query showing all recs on the form
usually the form has controls to filter what you want
then apply the filter using me.filter
me.filter = "[country]='" & cboCountry & "'"
me.filterOn = true

 

[arnelgp]

docmd.openquery "myQuery"
screen.activedatasheet.filter = "id = 3" 'put your filter here
screen.activedatasheet.filteron = True 'activate the filter

 

[path479]

docmd.openquery "myQuery"
screen.activedatasheet.filter = "id = 3" 'put your filter here
screen.activedatasheet.filteron = True 'activate the filter

Hi arnelgp

Thank you so much. This does exactly filter what I need. However it is my fault I didn't explain very well what I am trying to do.

We want to have a button on a form which when click would:
- trigger the VBA code to run the query, (with the where condition or filter the records)
- export the filtered records into an excel file.

We prefer not to have these filtered records shown on screen, and certainly not in edit mode.

Would you have any thoughts on how we could achieve this?

Thank you!

 

[arnelgp]

it would be better to create a query with a form parameter that will filter your records.
therefore when exporting it to excel will not be difficult.

to export to excel:

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"query1","z:\zxy.xlsx",true

 

[curtyrut]

Hello, can you help me resolve my problem? I created a form with a command button that will open a query. However, my objective is to open the query then filter based on the end user group. For example, I have the follow code:

DoCmd.OpenQuery "HrOwner", acViewNormal, acReadOnly
DoCmd.SetFilter wherecondition:=[Hiring Owner] = HrGroup

HrGoup is a variable that changes based on the end user setup.

I continue to receive a run-time error 438, which states "Object doesn't support this property or method.

I tried many of different ways to apply filter and to no avail was not successful. Your help is greatly appreciated.

 

[sxschech]

They may have been talking about putting the parameter in the query and then getting the value from the form rather than with a filter.

In your query in the criteria box you would put something like:

Field: [Hiring Owner]
Table:
Sort:
Show:
Criteria:[COLOR=Blue][Forms]![YourFormName].[YourParameterTextBoxComboBoxEtc][/COLOR]

Then when the query is run, it will get the criteria from the form.

 

[curtyrut]

Hello sxschech, thank you for the speedy reply and your remedy work! Once again thank you for helping me resolve my problem.