Access Call Macro from Query (Opening a Form) Run-

Question

I have an Access Query that requiers a value to be set in a combo-box within a form in order to work

Criteria: Forms![_SelectCustomer]![CmbSelectCustomer]

So far so good, however, I would like the query to open, read and close this form programatically when it is run using a macro.

I have been following @David-W-Fenton's answer in THIS similar stack overflow question and have come up with the following code:

Public Function rtnSelectCustomer() As Variant

DoCmd.OpenForm "_SelectCustomer", , , , , acDialog
  With Forms![_SelectCustomer]
    If .Tag <> "Cancel" Then
      rtnSelectCustomer = Nz(!CmbSelectCustomer, "*")
    Else
      rtnSelectCustomer = "*"
    End If
  End With
  Close acForm, "_SelectCustomer"
End Function

I call this function from within the criteria field of the property I want to filter by in the Query:

Like rtnSelectCustomer()

At this point I run into several problems:

• The first being, I'm not sure where to place the actual code: I can't seem to create a specific class or module for my query within the "Microsoft Access Class Objects" folder so I have resorted to creating my own module within the Modules folder. (Is this the correct approach?)

• The second issue is that when I run the query with the code in the current module I have created I get the following error:

  Run-time error '2486': You can't carry out this action at the present time.

Any advice would be much appreciated

---

Edit:

I should clarify that after further testing the line that seems to cause the Run-time error is the following:

DoCmd.OpenForm "_SelectCustomer", , , , , acDialog

The function is actually called as replacing the internal code with the following does actually work (although is admittedly useless)

Public Function rtnSelectCustomer() As Variant
  rtnSelectCustomer
End Function

Answer 1

Generally, I hate things that are "pre-programmed" by Microsoft, I'd rather do them myself. It seems this is your case as well...

I would do this in 2 steps.

Step1: Show things to the user as if the query was running (without actually running it) and store the values the user picks.

Step2: Use the values to parameterize the query

If your function works well, then simply remember what the user picks and then do:

set qdf = new QueryDef
' set the qdf and add all parameters to it
DoCmd.Execute qdf 

for further reference on how QueryDef works I would use this msdn site