Using QueryDefs to Build a Dynamic Query in Access

A

Amy E. Baggott

I am trying to build a dialog box that will generate a list of companies
based on certain criteria. I have two queries now, one that lists all
companies and one that excludes companies that are exhibiting in the current
show or have told us they're not going to exhibit in the current show (the
latter list is mostly used to try to get companies to exhibit in the show).

Here's where it gets complicated. I have three different ways you can
filter the data and two sets of fields to choose from. The filters are based
on all companies, selecting companies in a specific category, and selecting
companies in selected states. The two sets of fields are full contact
information for mailing and a much more limited set of data (company ID,
company name, first name, last name, phone, fax, and e-mail) to be used for
broadcast e-mails and faxes. I'm trying to set it up so that the users don't
have to go into the Excel file the procedure generates and remove fields
because I'm trying to standardize the field sets, particulary for the
broadcast fax and email program that we use.

My form has two option groups (one to select the filter and the other to
select mailing or broadcast) and a checkbox for whether to include
exhibitors. Selecting "All companies" in the first group then looks at the
checkbox and the second option group and builds the query accordingly.
Selecting either of the other two options opens a separate dialog box that
allows the user to select either the product category or the state(s),
depending on which option is selected, and forwards the values of the second
option group and the checkbox so that the second dialog box can build the
query with the added information.

So far, if I use a separate query for each possible combination (including
whether or not to include current exhibitors), I would have to write 12
queries. What I want to do is instead have the system build the queries at
runtime based on the two basic queries (all companies or non-exhibiting
companies). But when I try to run the actual code, I keep getting "Item not
found in this collection." What am I doing wrong?
 
D

Daryl S

Amy -

Post your code so we can look for the issue. Also, if you know where in the
code the error is trapped, then please include that.
 
A

Amy E. Baggott

Here is the code for the form. It bails as soon as it hits one of the "Set
qryCurr = db.QueryDefs..." statements. The queries to which it refers both
exist and both work if I run them from the database window.

Private Sub cmdOK_Click()
' Comments :
' Parameters:
' Created : 04/12/10 13:42 N
' Modified :
'
' --------------------------------------------------

'TVCodeTools ErrorEnablerStart
' On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim db As DAO.Database
Dim qryCurr As DAO.QueryDef

Set db = CurrentDb()

Select Case Me.grpChooseRecords

Case 1:
If Me.chkIncludeExhibitors = True Then
Select Case Me.grpChooseData

Case 1:
Set qryCurr = db.QueryDefs("SELECT
qryAllCompanyRecords.* FROM qryAllCompanyRecords")

Case 2:
Set qryCurr = db.QueryDefs("SELECT
qryAllCompanyRecords.CGI_CompanyID, qryAllCompanyRecords.[Company Name],
qryAllCompanyRecords.exh_first, qryAllCompanyRecords.exh_last,
qryAllCompanyRecords.exh_phon, qryAllCompanyRecords.Fax,
qryAllCompanyRecords.exh_prefix FROM qryAllCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
Else
Select Case Me.grpChooseData

Case 1:
Set qryCurr = db.QueryDefs("SELEC
qryNonExhibitingCompanyRecords.* FROM qryNonExhibitingCompanyRecords")

Case 2:
Set qryCurr = db.QueryDefs("SELECT
qryNonExhibitingCompanyRecords.CGI_CompanyID,
qryNonExhibitingCompanyRecords.[Company Name],
qryNonExhibitingCompanyRecords.exh_first,
qryNonExhibitingCompanyRecords.exh_last,
qryNonExhibitingCompanyRecords.exh_phon, qryNonExhibitingCompanyRecords.Fax,
qryNonExhibitingCompanyRecords.Email FROM qryNonExhibitingCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
End If

DoCmd.OpenQuery qryCurr
DoCmd.OutputTo acOutputQuery, qryCurr, acFormatXLS, , True
DoCmd.Close acQuery, qryCurr

Case 2:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If

DoCmd.OpenForm "fdlgChooseCategory"
Forms!fdlgChooseCategory![grpChoose] = Me.grpChooseData
Forms!fdlgChooseCategory![IncludeExhibitors] =
Me.chkIncludeExhibitors

Case 3:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If
DoCmd.OpenForm "fdlgProsByState"
Forms!fdlgProsByState![grpChoose] = Me.grpChooseData
Forms!fdlgProsByState![IncludeExhibitors] =
Me.chkIncludeExhibitors

Case Else:
Call MsgBox( _
"Please choose which group of companies you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select

DoCmd.Close acForm, "fdlgProspectLists"

'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd

End Sub
 
D

Douglas J. Steele

You need to the name of the query, not its SQL, when referring to the
QueryDefs collection.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Amy E. Baggott said:
Here is the code for the form. It bails as soon as it hits one of the
"Set
qryCurr = db.QueryDefs..." statements. The queries to which it refers
both
exist and both work if I run them from the database window.

Private Sub cmdOK_Click()
' Comments :
' Parameters:
' Created : 04/12/10 13:42 N
' Modified :
'
' --------------------------------------------------

'TVCodeTools ErrorEnablerStart
' On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim db As DAO.Database
Dim qryCurr As DAO.QueryDef

Set db = CurrentDb()

Select Case Me.grpChooseRecords

Case 1:
If Me.chkIncludeExhibitors = True Then
Select Case Me.grpChooseData

Case 1:
Set qryCurr = db.QueryDefs("SELECT
qryAllCompanyRecords.* FROM qryAllCompanyRecords")

Case 2:
Set qryCurr = db.QueryDefs("SELECT
qryAllCompanyRecords.CGI_CompanyID, qryAllCompanyRecords.[Company Name],
qryAllCompanyRecords.exh_first, qryAllCompanyRecords.exh_last,
qryAllCompanyRecords.exh_phon, qryAllCompanyRecords.Fax,
qryAllCompanyRecords.exh_prefix FROM qryAllCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
Else
Select Case Me.grpChooseData

Case 1:
Set qryCurr = db.QueryDefs("SELEC
qryNonExhibitingCompanyRecords.* FROM qryNonExhibitingCompanyRecords")

Case 2:
Set qryCurr = db.QueryDefs("SELECT
qryNonExhibitingCompanyRecords.CGI_CompanyID,
qryNonExhibitingCompanyRecords.[Company Name],
qryNonExhibitingCompanyRecords.exh_first,
qryNonExhibitingCompanyRecords.exh_last,
qryNonExhibitingCompanyRecords.exh_phon,
qryNonExhibitingCompanyRecords.Fax,
qryNonExhibitingCompanyRecords.Email FROM qryNonExhibitingCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
End If

DoCmd.OpenQuery qryCurr
DoCmd.OutputTo acOutputQuery, qryCurr, acFormatXLS, , True
DoCmd.Close acQuery, qryCurr

Case 2:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If

DoCmd.OpenForm "fdlgChooseCategory"
Forms!fdlgChooseCategory![grpChoose] = Me.grpChooseData
Forms!fdlgChooseCategory![IncludeExhibitors] =
Me.chkIncludeExhibitors

Case 3:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If
DoCmd.OpenForm "fdlgProsByState"
Forms!fdlgProsByState![grpChoose] = Me.grpChooseData
Forms!fdlgProsByState![IncludeExhibitors] =
Me.chkIncludeExhibitors

Case Else:
Call MsgBox( _
"Please choose which group of companies you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select

DoCmd.Close acForm, "fdlgProspectLists"

'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd

End Sub

--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Daryl S said:
Amy -

Post your code so we can look for the issue. Also, if you know where in
the
code the error is trapped, then please include that.
Click to expand...
Click to expand...
 
D

Dirk Goldgar

Amy E. Baggott said:
Here is the code for the form. It bails as soon as it hits one of the
"Set
qryCurr = db.QueryDefs..." statements. The queries to which it refers
both
exist and both work if I run them from the database window.

Private Sub cmdOK_Click()
' Comments :
' Parameters:
' Created : 04/12/10 13:42 N
' Modified :
'
' --------------------------------------------------

'TVCodeTools ErrorEnablerStart
' On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim db As DAO.Database
Dim qryCurr As DAO.QueryDef

Set db = CurrentDb()

Select Case Me.grpChooseRecords

Case 1:
If Me.chkIncludeExhibitors = True Then
Select Case Me.grpChooseData

Case 1:
Set qryCurr = db.QueryDefs("SELECT
qryAllCompanyRecords.* FROM qryAllCompanyRecords")

Case 2:
Set qryCurr = db.QueryDefs("SELECT
qryAllCompanyRecords.CGI_CompanyID, qryAllCompanyRecords.[Company Name],
qryAllCompanyRecords.exh_first, qryAllCompanyRecords.exh_last,
qryAllCompanyRecords.exh_phon, qryAllCompanyRecords.Fax,
qryAllCompanyRecords.exh_prefix FROM qryAllCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
Else
Select Case Me.grpChooseData

Case 1:
Set qryCurr = db.QueryDefs("SELEC
qryNonExhibitingCompanyRecords.* FROM qryNonExhibitingCompanyRecords")

Case 2:
Set qryCurr = db.QueryDefs("SELECT
qryNonExhibitingCompanyRecords.CGI_CompanyID,
qryNonExhibitingCompanyRecords.[Company Name],
qryNonExhibitingCompanyRecords.exh_first,
qryNonExhibitingCompanyRecords.exh_last,
qryNonExhibitingCompanyRecords.exh_phon,
qryNonExhibitingCompanyRecords.Fax,
qryNonExhibitingCompanyRecords.Email FROM qryNonExhibitingCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
End If

DoCmd.OpenQuery qryCurr
DoCmd.OutputTo acOutputQuery, qryCurr, acFormatXLS, , True
DoCmd.Close acQuery, qryCurr

Case 2:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If

DoCmd.OpenForm "fdlgChooseCategory"
Forms!fdlgChooseCategory![grpChoose] = Me.grpChooseData
Forms!fdlgChooseCategory![IncludeExhibitors] =
Me.chkIncludeExhibitors

Case 3:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If
DoCmd.OpenForm "fdlgProsByState"
Forms!fdlgProsByState![grpChoose] = Me.grpChooseData
Forms!fdlgProsByState![IncludeExhibitors] =
Me.chkIncludeExhibitors

Case Else:
Call MsgBox( _
"Please choose which group of companies you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select

DoCmd.Close acForm, "fdlgProspectLists"

'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd

End Sub
Click to expand...


A QueryDef represents a stored query, so when you make a reference like:

Set qryCurr = db.QueryDefs("something")

.... you are asking for the stored query named "something". Your code is
putting SQL statements as the "something" -- and you don't have stored
queries with those names. It looks like you are trying to both open an
existing querydef and change its SQL at the same time, and only in memory.
That won't work.

What you could do is have a separate querydef object that you use only for
exporting data, change its SQL on the fly, then export it. Code would be
something like this (assuming this special querydef has already been created
manually, and is named "qryExport"):

'------ start of revised code ------
Private Sub cmdOK_Click()
' Comments :
' Parameters:
' Created : 04/12/10 13:42 N
' Modified :
'
' --------------------------------------------------

'TVCodeTools ErrorEnablerStart
' On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim strSQL As String

Select Case Me.grpChooseRecords

Case 1:
If Me.chkIncludeExhibitors = True Then
Select Case Me.grpChooseData

Case 1:
strSQL = "SELECT * FROM qryAllCompanyRecords"

Case 2:
strSQL = _
"SELECT CGI_CompanyID, [Company Name], " & _
"exh_first, exh_last, exh_phon, Fax, " & _
"exh_prefix " & _
"FROM qryAllCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
Else
Select Case Me.grpChooseData

Case 1:
strSQL = "SELECT * FROM qryNonExhibitingCompanyRecords"

Case 2:
strSQL = _
"SELECT CGI_CompanyID, Company Name], " & _
"exh_first, exh_last, exh_phon, Fax, Email " & _
"FROM qryNonExhibitingCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
End If

CurrentDb.QueryDefs("qryExport").SQL = strSQL
DoCmd.OutputTo acOutputQuery, "qryExport", acFormatXLS, , True

Case 2:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If

DoCmd.OpenForm "fdlgChooseCategory"
Forms!fdlgChooseCategory![grpChoose] = Me.grpChooseData
Forms!fdlgChooseCategory![IncludeExhibitors] = _
Me.chkIncludeExhibitors

Case 3:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If
DoCmd.OpenForm "fdlgProsByState"
Forms!fdlgProsByState![grpChoose] = Me.grpChooseData
Forms!fdlgProsByState![IncludeExhibitors] = _
Me.chkIncludeExhibitors

Case Else:
Call MsgBox( _
"Please choose which group of companies you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select

DoCmd.Close acForm, "fdlgProspectLists"

'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Set qdf
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd

End Sub
'------ end of revised code ------
 
A

Amy E. Baggott

So would I first define qryExport in Access using the query builder, or do I
create it in code?

--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Dirk Goldgar said:
Amy E. Baggott said:
Here is the code for the form. It bails as soon as it hits one of the
"Set
qryCurr = db.QueryDefs..." statements. The queries to which it refers
both
exist and both work if I run them from the database window.

Private Sub cmdOK_Click()
' Comments :
' Parameters:
' Created : 04/12/10 13:42 N
' Modified :
'
' --------------------------------------------------

'TVCodeTools ErrorEnablerStart
' On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim db As DAO.Database
Dim qryCurr As DAO.QueryDef

Set db = CurrentDb()

Select Case Me.grpChooseRecords

Case 1:
If Me.chkIncludeExhibitors = True Then
Select Case Me.grpChooseData

Case 1:
Set qryCurr = db.QueryDefs("SELECT
qryAllCompanyRecords.* FROM qryAllCompanyRecords")

Case 2:
Set qryCurr = db.QueryDefs("SELECT
qryAllCompanyRecords.CGI_CompanyID, qryAllCompanyRecords.[Company Name],
qryAllCompanyRecords.exh_first, qryAllCompanyRecords.exh_last,
qryAllCompanyRecords.exh_phon, qryAllCompanyRecords.Fax,
qryAllCompanyRecords.exh_prefix FROM qryAllCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
Else
Select Case Me.grpChooseData

Case 1:
Set qryCurr = db.QueryDefs("SELEC
qryNonExhibitingCompanyRecords.* FROM qryNonExhibitingCompanyRecords")

Case 2:
Set qryCurr = db.QueryDefs("SELECT
qryNonExhibitingCompanyRecords.CGI_CompanyID,
qryNonExhibitingCompanyRecords.[Company Name],
qryNonExhibitingCompanyRecords.exh_first,
qryNonExhibitingCompanyRecords.exh_last,
qryNonExhibitingCompanyRecords.exh_phon,
qryNonExhibitingCompanyRecords.Fax,
qryNonExhibitingCompanyRecords.Email FROM qryNonExhibitingCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
End If

DoCmd.OpenQuery qryCurr
DoCmd.OutputTo acOutputQuery, qryCurr, acFormatXLS, , True
DoCmd.Close acQuery, qryCurr

Case 2:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If

DoCmd.OpenForm "fdlgChooseCategory"
Forms!fdlgChooseCategory![grpChoose] = Me.grpChooseData
Forms!fdlgChooseCategory![IncludeExhibitors] =
Me.chkIncludeExhibitors

Case 3:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If
DoCmd.OpenForm "fdlgProsByState"
Forms!fdlgProsByState![grpChoose] = Me.grpChooseData
Forms!fdlgProsByState![IncludeExhibitors] =
Me.chkIncludeExhibitors

Case Else:
Call MsgBox( _
"Please choose which group of companies you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select

DoCmd.Close acForm, "fdlgProspectLists"

'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd

End Sub
Click to expand...


A QueryDef represents a stored query, so when you make a reference like:

Set qryCurr = db.QueryDefs("something")

... you are asking for the stored query named "something". Your code is
putting SQL statements as the "something" -- and you don't have stored
queries with those names. It looks like you are trying to both open an
existing querydef and change its SQL at the same time, and only in memory.
That won't work.

What you could do is have a separate querydef object that you use only for
exporting data, change its SQL on the fly, then export it. Code would be
something like this (assuming this special querydef has already been created
manually, and is named "qryExport"):

'------ start of revised code ------
Private Sub cmdOK_Click()
' Comments :
' Parameters:
' Created : 04/12/10 13:42 N
' Modified :
'
' --------------------------------------------------

'TVCodeTools ErrorEnablerStart
' On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim strSQL As String

Select Case Me.grpChooseRecords

Case 1:
If Me.chkIncludeExhibitors = True Then
Select Case Me.grpChooseData

Case 1:
strSQL = "SELECT * FROM qryAllCompanyRecords"

Case 2:
strSQL = _
"SELECT CGI_CompanyID, [Company Name], " & _
"exh_first, exh_last, exh_phon, Fax, " & _
"exh_prefix " & _
"FROM qryAllCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
Else
Select Case Me.grpChooseData

Case 1:
strSQL = "SELECT * FROM qryNonExhibitingCompanyRecords"

Case 2:
strSQL = _
"SELECT CGI_CompanyID, Company Name], " & _
"exh_first, exh_last, exh_phon, Fax, Email " & _
"FROM qryNonExhibitingCompanyRecords")

Case Else:
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select
End If

CurrentDb.QueryDefs("qryExport").SQL = strSQL
DoCmd.OutputTo acOutputQuery, "qryExport", acFormatXLS, , True

Case 2:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If

DoCmd.OpenForm "fdlgChooseCategory"
Forms!fdlgChooseCategory![grpChoose] = Me.grpChooseData
Forms!fdlgChooseCategory![IncludeExhibitors] = _
Me.chkIncludeExhibitors

Case 3:
If IsNull(Me.grpChooseData) Then
Call MsgBox( _
"Please choose which type of list you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT
End If
DoCmd.OpenForm "fdlgProsByState"
Forms!fdlgProsByState![grpChoose] = Me.grpChooseData
Forms!fdlgProsByState![IncludeExhibitors] = _
Me.chkIncludeExhibitors

Case Else:
Call MsgBox( _
"Please choose which group of companies you want.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Did you forget something?")
GoTo PROC_EXIT

End Select

DoCmd.Close acForm, "fdlgProspectLists"

'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Set qdf
Exit Sub

PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd

End Sub
'------ end of revised code ------

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Amy E. Baggott said:
So would I first define qryExport in Access using the query builder, or do
I
create it in code?
Click to expand...


I would do in the query builder. It doesn't really matter what SQL you use
when creating it; you may as well use "SELECT * FROM qryAllCompanyRecords".
Every time you run the code I posted, it will change the SQL, then export
the query. The changed SQL will persist until the next time it is changed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Query Defs in a Form 3
Problem with SET & REF function 0
Create a chart from a table in word thats dynamic 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Dynamic Naming of Form Button 3
QBF & Query Malfunction 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Ranked-choice poll in Outlook? 0

Top