Trouble with CreateQueryDef

[aimeepeter]

Hello,
I would appreciate some help with following.

I am using a form to set the SELECT statement for qryDemographics1 which sets the time span, program name and participant type from records stored in a table, tblRegistrations. Demographic information about the people registered are stored in the table tblContacts. This first query, qryDemographics1, is used by three other queries, qryDemographicsAge, qryDemographicsGender & qryDemographicsEthnic. The end goal is to open a report, either rptDemographicsAge, rptDemographicsGender or rptDemographicsEthnic, which has qryDemographicsAge or qryDemographicGender or qryDemographicsEthnic respectively, set as its record source.

Currently everything runs without any error messages but the report does not open. The code seems to work fine up until the Create Query Def part.

Here is the code on the Open button of the form where the user sets the desired filter for the records to include:

Private Sub cmdOpen_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset
Dim qdfNew As DAO.QueryDef
Dim qdfNew2 As DAO.QueryDef
Dim qdfNew3 As DAO.QueryDef
Dim qdfNew4 As DAO.QueryDef
Dim strSQL As String
Dim strSQL2 As String 'Age
Dim strSQL3 As String 'Gender
Dim strSQL4 As String 'Ethnic


'Check that Start Date is before End Date
If Me.txtEndDate < Me.txtStartDate Then
MsgBox ("End date must be after Start Date. Please re-enter the date range."), vbInformation, gstrAppTitle
End If

' Initialize to Null
varWhere = Null

' Start building the filter
If Me.chkAllDates = True Then
varWhere = "((tblRegistrations.ProgramDate) > #1/1/1900#)"
End If

' Do Program Dates
If Me.chkAllDates = False Then
If Not IsNothing(Me.txtStartDate) Then
' .. build the predicate
varWhere = "((tblRegistrations.ProgramDate) >= #" & Me.txtStartDate & "#)"
End If
End If

If Me.chkAllDates = False Then
If Not IsNothing(Me.txtEndDate) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & "((tblRegistrations.ProgramDate) < #" & Me.txtEndDate & "#)"
End If
End If


' Do Program Name
If Not IsNothing(Me.cmbProgName) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & "((tblRegistrations.ProgramID) = " & Me.cmbProgName & ")"
End If

' Do Participant Type
If Not IsNothing(Me.cmbContactType) Then
' .. build the predicate
varWhere = "(" & (varWhere + " AND ") & "((tblRegistrations.ParticipantType) " & _
"= " & Chr(34) & Me.cmbContactType & Chr(34) & "))"
End If


' Check to see that we built a filter
If Me.chkAllDates = False Then
If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
Exit Sub
End If
End If

' Open a recordset to see if any rows returned with this filter
Set rst = CurrentDb.OpenRecordset("SELECT tblRegistrations.* " & _
"FROM tblRegistrations WHERE " & varWhere)

' See if found none
If rst.RecordCount = 0 Then
MsgBox "No data meets your search criteria.", vbInformation, gstrAppTitle
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If

If IsNothing(Me.OptDemo) Then
MsgBox ("Please select the type of demographics report you wish to view"), vbCritical, gstrAppTitle
Exit Sub
End If

varWhere = "(" & varWhere & " AND ((tblRegistrations.IndividualOrGroup) = " & Chr(34) & "Individual" & Chr(34) & ") AND ((" & _
"tblRegistrations.Attended) = True)))"


'Create feeder SELECT statement
strSQL = "SELECT tblRegistrations.* FROM tblRegistrations WHERE " & varWhere

'SELECT statement for age query
strSQL2 = "SELECT DISTINCT qryDemographics1.ContactID, DateDiff(" & Chr(34) & "yyyy" & Chr(34) & ",[Birthdate]" & _
",Now())+Int(Format(Now()," & Chr(34) & "mmdd" & Chr(34) & ")<Format([Birthdate]," & Chr(34) & "mmdd" & Chr(34) & ")) " & _
"AS Age FROM tblContacts INNER JOIN qryDemographics1 ON tblContacts.ContactID = qryDemographics1.ContactID " & _
"GROUP BY qryDemographics1.ContactID, DateDiff(" & Chr(34) & "yyyy" & Chr(34) & ",[Birthdate],Now())" & _
"+Int(Format(Now()," & Chr(34) & "mmdd" & Chr(34) & ")<Format([Birthdate]," & Chr(34) & "mmdd" & Chr(34) & "));"

'SELECT statement for gender query
strSQL3 = "SELECT DISTINCT qryDemographics1.ContactID, tblContacts.Gender FROM tblContacts INNER JOIN " & _
"qryDemographics1 ON tblContacts.ContactID=qryDemographics1.ContactID WHERE (((tblContacts.Gender) Is Not Null));"


'SELECT statement for Ethnic Origin query
strSQL4 = "SELECT DISTINCT qryDemographics1.ContactID, tblContacts.EthnicOrigin FROM tblContacts INNER JOIN " & _
"qryDemographics1 ON tblContacts.ContactID = qryDemographics1.ContactID WHERE (((tblContacts.EthnicOrigin) Is Not Null));"

' now create a reusable stored query def
On Error Resume Next
' is run for the first time
With CurrentDb ' it would be better to check to see if the
' querydef exists and then delete it
.QueryDefs.Delete ("qryDemographics1")
' createquerydef command line follows
Set qdfNew = .CreateQueryDef("qryDemographics1", strSQL)
.Close
End With


'Open the chosen report
Select Case Me.OptDemo
Case 1 'Age
With CurrentDb ' it would be better to check to see if the
' querydef exists and then delete it
.QueryDefs.Delete ("qryDemographicsAge")
' createquerydef command line follows
Set qdfNew2 = .CreateQueryDef("qryDemographicsAge", strSQL2)
.Close
End With
DoCmd.OpenReport "rptDemographicsAge", acViewPreview

Case 2 'Gender
With CurrentDb ' it would be better to check to see if the
' querydef exists and then delete it
.QueryDefs.Delete ("qryDemographicsGender")
' createquerydef command line follows
Set qdfNew3 = .CreateQueryDef("qryDemographicsGender", strSQL3)
.Close
End With
DoCmd.OpenReport "rptDemographicsGender", acViewPreview
Case 3 'Ethnic Origin
With CurrentDb ' it would be better to check to see if the
' querydef exists and then delete it
.QueryDefs.Delete ("qryDemographicsEthnic")
' createquerydef command line follows
Set qdfNew4 = .CreateQueryDef("qryDemographicsEthnic", strSQL4)
.Close
End With
DoCmd.OpenReport "rptDemographicsEthnic", acViewPreview
End Select

'close filter form
DoCmd.Close acForm, Me.Name

' Clean up recordset
rst.Close
Set rst = Nothing

End Sub


Any suggestions?

Cheers,
aimeepeter

 

[boblarson]

Okay, a few things:

1. You have an On Error Resume Next which could be causing you not to see a report being opened, because there is an error occuring but it will never display.

2. When you open a recordset you need to use a .MoveLast to be able to get a record count, or else it will always be 0.

3. You are going about this wrong. You can't set a querydef and then open a report - the report isn't going to use the querydef (at least not in the way you have your code).

4. You don't need the querydefs anyway. Just create the base query for the report IN THE REPORT and then set the WHERE clause in the code and open the report with

DoCmd.OpenReport "ReportNameHere", acViewPreview, , strWhere

 

[aimeepeter]

Thank you for your feedback. I am very familiar with opening the report using the WHERE clause.

I think my confusion is coming from trying to use a two-step query.

By this I mean I am using the first query (qryDemographics1) to set the date range and select the program name and participant type from the records retrieved from tblRegistrations. Then I am using the second query to SELECT DISTINCT the ContactIDs. Each registrant has several registrations but I want each individual registrant (ContactID) to appear in the statistics once.

I am using the two-step query because if I combine this into one query I cannot get a distict record for each contactID.


Is there a different way to go about selecting distinct records from my contacts and registration tables?

Here are the table fields which are involved. I want my reports to show demographic info which I can have the user filter using a pop up form.

tblContacts:
ContactID (primary key)
Birthdate
Gender
EthnicOrigin

tblRegistrations:
RegistrationID (primary key)
ContactID
ProgramID
ProgramDate
ParticipantType

Again thanks for your help!

 

[aimeepeter]

OK I have deleted the On Error Resume Next and it is working now.
Thanks.