1

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
Community
  • 1
  • 1
Chopo87
  • 1,240
  • 4
  • 19
  • 32

1 Answers1

0

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

parsley72
  • 8,449
  • 8
  • 65
  • 98
ex-man
  • 369
  • 2
  • 7
  • Ok, I need to think about that a bit to see if it is applicable in my circumstance. The reason I want the query to run the form directly and not vice versa (as is often done) is so that the query can easily be executed from a report. The intended end user of the tool is not very skilled and if he cannot integrate the query into a report using the report wizard, the query will be useless to him. Can a report execute a macro to get data? – Chopo87 Jan 06 '14 at 10:24
  • Sorry... this is out of my skill level. I always create my own forms and my own excel reports, as you can play around with them, as a user and use buttons and other features as developer. It may be a bit more work, but I think it is worth the flexibility – ex-man Jan 06 '14 at 18:18