MS Access: Use variables instead of text.SetFocus method to query

Question

I have two parameters form a FROM and THRU textbox. The code object is txtFROM and txtTHRU. Now I tried to open the query and reports with a txtFROM.SetFocus and txtTHRU.SetFocus and used in the query criteria: Between [FORMS]![ReportName]![txtFROM].[Text] and [FORMS]![ReportName]![txtTHRU].[Text]. However nothing turns up when I link a button to the query and report to show the data with those two parameters. I think it may be due to the fact that the .SetFocus method will only work on one parameter, so I think writing VBA variables to pass into a query might work if possible. The thing is I do not know if it is possible to call a VBA variable while running to a query as it were an object. The variables would otherwise read .SetFocus to ready the parameter to be passed to the Access query.

  DoCmd.SetWarnings False

If IsNull(txtFROM.Value) = False And IsNull(txtTHRU.Value) = False Then
dataFROM = CDate(txtFROM.Value)
dataTHRU = CDate(txtTHRU.Value)
End If

    DoCmd.OpenQuery ("Expiring")
    DoCmd.OpenReport ("Expirees"), acViewPreview
    DoCmd.SetWarnings True

The above variables dataFROM and dataTHRU would be what I would like to fit in the query criteria to reference the Form which displays reports.

DO NOT USE the .text property (that is from VB6, or vb.net). In Access VBA and code etc. you use .value property - NEVER use .text for general reference of a control. — Albert D. Kallal, Sep 17 '15 at 17:50

Johnny Bones · Answer 1 · 2015-09-17T16:47:20.777

2

You might need to script the query "on the fly" by using CreateQueryDef. Sort of like:

Dim db as Database
Dim qdf as QueryDef

Set db = CurrentDB
Set qdf = db.CreateQueryDef("Expiring", "SELECT * FROM MyTable WHERE " &_
   "MyDate >= #" & CDate(txtFROM.Value) & "# and MyDate =< #" CDate(txtTHRU.Value) & "#")
DoCmd.OpenReport "Expirees", acViewPreview

Of course, you'll probably need to add some code at the beginning to delete that query if it already exists. Definitely inside an If/Then because if the code happens to burp and doesn't create the query one time, it'll crash the next time you run it.

Edit

As suggested by HansUp, another option is simply to alter the query's SQL statement, which you can do in code.

Set myquery = db.OpenQueryDef("Expiring")

strsql = "SELECT * FROM MyTable WHERE " &_
       "MyDate >= #" & CDate(txtFROM.Value) & "# and MyDate =< #" CDate(txtTHRU.Value) & "#"

myquery.SQL = strsql
myquery.Close

edited Sep 17 '15 at 16:47

answered Sep 17 '15 at 15:52

Johnny Bones

8,786
7
52
117

I take it that this method will not use the original query but just design a new one on the fly. So I may keep those original parameters in the query? Yes the query already exists. And I am not familiar with VBA use for deleting a query if need be. Would the delete be permanent since it is used in another context for printing? – safron6 Sep 17 '15 at 16:02
Thanks, @HansUp! Oversight on my part. And yes, this will recreate the query every time it's run. You will need to permanently delete the query using DeleteQueryDef, then create the new one using CreateQueryDef. – Johnny Bones Sep 17 '15 at 16:26
No worries, JB ... your intention was clear enough. :-) Think I might tell them to not ever delete the query; just change its `.SQL` property as needed. – HansUp Sep 17 '15 at 16:29

score 0 · Answer 2 · answered Sep 18 '15 at 23:53

0

It looks like there was a mixup in my query code, the FROM was duplicated, FROM FROM, not FROM THRU. The code works as it should have with the reference to the Reports and Form which the text controls. Keep with the usual method then.

answered Sep 18 '15 at 23:53

safron6

149
1
2
13

MS Access: Use variables instead of text.SetFocus method to query

2 Answers2