Running query in VBA with where condition (1 Viewer)

path479 · Jun 28, 2016

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 · Jun 28, 2016

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

Minty · Jun 28, 2016

Ranman256 said:
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 · Jun 28, 2016

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 · Jun 28, 2016

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

path479 · Jun 29, 2016

arnelgp said:
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 · Jun 29, 2016

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 · Nov 16, 2016

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 · Nov 16, 2016

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:

Code:

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 · Nov 16, 2016

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

Running query in VBA with where condition (1 Viewer)

path479

Registered User.

Ranman256

Well-known member

Minty

AWF VIP

Ranman256

Well-known member

arnelgp

..forever waiting... waiting for jellybean!

path479

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

curtyrut

Registered User.

sxschech

Registered User.

curtyrut

Registered User.

Similar threads

Users who are viewing this thread