CODEWITHSUNDEEP
sqlms-accessvba
Johnny Bigoode
Johnny Bigoode

Reputation: 591

How can I run a query passing information from a form?

I guess this is a silly question, but I'm having problems figuring this out.

I have two tables with loads of information used to make reports.

I have a query that joins these two tables.

The same query adds two simple filters: A date range and an ID checker.

The reports are supposed to be printed withing a date range with a specific ID.

Every time I need to change the query data range I need to manually edit it and change the parameters.

How can I make a form and pass this information to the query so I won't have to manually update the query every week?

I made a new form with two date fields and I would need some code to pass this information to the query before opening it, but DoCmd.OpenQuery Method doesn't permit I pass any information.

Ideally I'd prefer to use a SQL command to set the query and then open it, is this even possible?

Upvotes: 0

Views: 1419

Answers (4)

HK1
HK1

Reputation: 12230

Open the report with a filter:

Dim sWhere as String
sWhere = "Tbl_Swift.Data >= #10/01/2012# AND Tbl_Swift.Data <= #10/10/2012#"
DoCmd.OpenReport "rptMyReportName", acViewPreview, ,sWhere

(I'm not sure if "BETWEEN" will work or not, although I think it should if you'd write it properly.)

Upvotes: 1

KekuSemau
KekuSemau

Reputation: 6852

Actually, I am using this method often:
Declare variables in a Module and write a function for each one that simply returns the value. You can then use the function call within the Query-Designer (e. g. criteria = Between getMyValue1() AND getMyValue2()) You can then set the global variables before opening the query. (This has worked better for me than Query-Parameters or direct references to form fields within queries.)

Upvotes: 2

fthiella
fthiella

Reputation: 49089

You could also put a condition in your query that refers to a field in your form:

SELECT ... WHERE ID = [Forms]![FormName]![TextField]

Upvotes: 1

Murtuza Kabul
Murtuza Kabul

Reputation: 6524

You have two options.

  1. Do not put values of parameters in the query. Access will always ask for these values on every execution of query.

  2. use DATE() function to get the current date and calculate the week start and end accordingly. This way, you will never have to provide the date.

Upvotes: 0

Related Questions