Passing Query Parameters from form to another VBA subroutine

  • Thread starter Thread starter ScottA
  • Start date Start date
S

ScottA

I've got a form that lets the user filter a table full of
records (contacts: name, address, location, bla bla) by
selecting from values in six different fields - and am
using the selections from these combo boxes as the
parameters for the underlying query. Each time the user
changes the variables, they can re-query and update the
list. (that whole thing works just peachy)

Once the user has limited (filtered) the list to the
contacts they want, I would them to be able to click a
button and send the filtered list to Word and start a mail
merge.

I've got some really great code (courtesy of Albert
Kallal) that lets you set up just that - a button that
starts a mail merge operation based on the records in the
underlying query.

My problem is that by the time you're out there running
all these other subroutines, the focus is away from the
form where all of the parameters is stored, and I get
prompted to re-enter the parameters (enter value for
cboXYZ, and so on).

The code I'm working with passes the SQL when calling the
function that creates the mail merge -

Private Sub cmdMergeAll_Click()

Me.Refresh
MergeAllWord ("select * from " & Me.RecordSource)

Is it possible to grab the selected parameters from the
form and pass them along to the function as well? Is
there another way to do this?

Thanks!

Scott A
 
You can refer to fields on any open form (whether the
form has focus or not) by referring to them through the
forms collection, such as:

Forms!MyFormName!MyControlName

You can use this in your query or your VB code, as long
as the form is still open.

Hope this helps.

-Ted
 
Ted -

Thanks for the tip, but I was already using references to
the controls using the syntax you describe (I would have
posted the SQL, but it's frikkin humongous).

For the record (and the next programmer tackling this
problem) I was able to find an explanation as to why it
wasnt't working and have been able to work through a
solution. It said that when creating a recordset from
VBA, the Jet engine isn't able to locate the parameter
references, even if the form is open. I modified the code
to open the QueryDef prior to creating the recordset and
providing references for the parameters.

Here's what I ended up with:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Dim rstOutput As DAO.Recordset
Dim strOutFile As String ' csv file output name


Set db = CurrentDb()
'Collect the selected parameters from the open form
If IsFormOpen("fmnuContactList") Then

'Grab the parameters from the combo boxes on the
form and
'pass them to the QueryDef before creating a
recordset.
Set qdf = db.QueryDefs("qryContactList")

qdf("[Forms]![fmnuContactList]!
[cboFunctionSearch]") = Forms!fmnuContactList!
cboFunctionSearch
qdf("[Forms]![fmnuContactList]!
[cboInstituteSearch]") = Forms!fmnuContactList!
cboInstituteSearch
qdf("[Forms]![fmnuContactList]!
[cboCountrySearch]") = Forms!fmnuContactList!
cboCountrySearch
qdf("[Forms]![fmnuContactList]![cboTypeSearch]") =
Forms!fmnuContactList!cboTypeSearch
qdf("[Forms]![fmnuContactList]![cboStudySearch]")
= Forms!fmnuContactList!cboStudySearch

'Attempt to create the recordset.
Set rstOutput = qdf.OpenRecordset()

And so on...
 
Hi Scott,

Thanks for the feedback, that is good to know. I have
always used query definition objects in my code and
passed values to parameters in the query (such as:
qdef.Parameters("EnterProjID") = lngProjID), but I would
have thought that the form objects could be referenced
directly. Thanks.

-Ted
-----Original Message-----
Ted -

Thanks for the tip, but I was already using references to
the controls using the syntax you describe (I would have
posted the SQL, but it's frikkin humongous).

For the record (and the next programmer tackling this
problem) I was able to find an explanation as to why it
wasnt't working and have been able to work through a
solution. It said that when creating a recordset from
VBA, the Jet engine isn't able to locate the parameter
references, even if the form is open. I modified the code
to open the QueryDef prior to creating the recordset and
providing references for the parameters.

Here's what I ended up with:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Dim rstOutput As DAO.Recordset
Dim strOutFile As String ' csv file output name


Set db = CurrentDb()
'Collect the selected parameters from the open form
If IsFormOpen("fmnuContactList") Then

'Grab the parameters from the combo boxes on the
form and
'pass them to the QueryDef before creating a
recordset.
Set qdf = db.QueryDefs("qryContactList")

qdf("[Forms]![fmnuContactList]!
[cboFunctionSearch]") = Forms!fmnuContactList!
cboFunctionSearch
qdf("[Forms]![fmnuContactList]!
[cboInstituteSearch]") = Forms!fmnuContactList!
cboInstituteSearch
qdf("[Forms]![fmnuContactList]!
[cboCountrySearch]") = Forms!fmnuContactList!
cboCountrySearch
qdf("[Forms]![fmnuContactList]![cboTypeSearch]") =
Forms!fmnuContactList!cboTypeSearch
qdf("[Forms]![fmnuContactList]!
Click to expand...
[cboStudySearch]")
= Forms!fmnuContactList!cboStudySearch

'Attempt to create the recordset.
Set rstOutput = qdf.OpenRecordset()

And so on...

-----Original Message-----
You can refer to fields on any open form (whether the
form has focus or not) by referring to them through the
forms collection, such as:

Forms!MyFormName!MyControlName

You can use this in your query or your VB code, as long
as the form is still open.

Hope this helps.

-Ted
.
Click to expand...
.
Click to expand...
 
Back
Top