One-to-many problem, multiple row per record

[jzwp22]

From what I remember you cannot reference a form control like this:

Me("Filter" & intCounter)

You must use the actual form control name, you cannot construct the control name.

You will have to evaluate each control and add it incrementally to the WHERE clause of the query that supplies data to the report.

 

[lostwithaccess]

You will have to evaluate each control and add it incrementally to the WHERE clause of the query that supplies data to the report.

Thank you. Unfortunately I don't know where to get started with doing that! Would you be able to give me a few more pointers?

 

[jzwp22]

Basically you have to treat each combo box separately and if something is selected then add it to the filter. I took your code and modified it slightly so it would work in an application I had on hand. The report name is different as well as the field name being filtered (a last name field: strLastName)

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim strSQL As String
Dim stDocName As String
stDocName = "rptOfficerInfo"
    
    
If Nz(Me.cboLastName, "") <> "" Then
    strSQL = "strLName='" & Me.cboLastName & "' AND "
End If

'Strip Last " And "
If strSQL <> "" Then

    strSQL = Left(strSQL, (Len(strSQL) - 5))
    Debug.Print strSQL
    'Set the Filter property
    DoCmd.OpenReport stDocName, acPreview
    Reports![rptOfficerInfo].Filter = strSQL
    Reports![rptOfficerInfo].FilterOn = True
    
Else
    MsgBox "No criteria specified, opening the report unfiltered"
    DoCmd.OpenReport stDocName, acPreview
End If
    
Exit_Command4_Click:
    Exit Sub

Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click
    
End Sub

To handle multiple combo boxes, you would just add and IF..THEN END IF for each as shown below (I've added two additional ones).

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim strSQL As String
Dim stDocName As String
stDocName = "rptOfficerInfo"
strSQL=""    
    
If Nz(Me.cboLastName, "") <> "" Then
    strSQL = strSQL & "strLName='" & Me.cboLastName & "' AND "
End If

If Nz(me.cboFirstName, "")<>"" then
   strSQL= strSQL & "strFName='" & me.cboFirstName & "' AND "
End if

If Nz(me.cboAddress, "")<> "" then
 strSQL= strSQL & "strAddress='" & me.cboAddress & "' AND "
End if



'Strip Last " And "
If strSQL <> "" Then

    strSQL = Left(strSQL, (Len(strSQL) - 5))
    Debug.Print strSQL
    'Set the Filter property
    DoCmd.OpenReport stDocName, acPreview
    Reports![rptOfficerInfo].Filter = strSQL
    Reports![rptOfficerInfo].FilterOn = True
    
Else
    MsgBox "No criteria specified, opening the report unfiltered"
    DoCmd.OpenReport stDocName, acPreview
End If
    
Exit_Command4_Click:
    Exit Sub

Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click
    
End Sub

BTW, where are your field names in this statement? Is your field name actually called filter1, filter2 etc.?

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

 

[lostwithaccess]

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

I am not sure what to do with this code? I have applied the code you gave me, and changed a few of my field names to make it clearer. My code is now as follows:

Option Compare Database

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click

Dim strSQL As String
Dim stDocName As String
stDocName = "rptInstitutions"


If Nz(Me.cboCountry, "") <> "" Then
strSQL = "strCountry='" & Me.cboCountry & "' AND "
End If


If Nz(Me.cboInstitute, "") <> "" Then
strSQL = strSQL & "strInstitute='" & Me.cboInstitute & "' AND "
End If


If Nz(Me.cboLastName, "") <> "" Then
strSQL = strSQL & "strLastName='" & Me.cboLastName & "' AND "
End If


'Strip Last " And "
If strSQL <> "" Then

strSQL = Left(strSQL, (Len(strSQL) - 5))
Debug.Print strSQL
'Set the Filter property
DoCmd.OpenReport stDocName, acPreview
Reports![rptInstitutions].Filter = strSQL
Reports![rptInstitutions].FilterOn = True

Else
MsgBox "No criteria specified, opening the report unfiltered"
DoCmd.OpenReport stDocName, acPreview
End If

Exit_cmdFilter_Click:
Exit Sub

Err_cmdFilter_Click:
MsgBox Err.Description
Resume Exit_cmdFilter_Click

End Sub

But I have been having problems populating the combo boxes and getting it to work. In frmReportFilter I now have;

cboCountry
cboInstitute
cboLastName
'which I would like as my filter options.

and:
cmdClear
cmdFilter
cmdClose

The coding is in On Click of cmdfilter.
Rather than having to choose from a list of every country in the world (I want only those countries to which I have a record relating to), I queried tblStaffInstitutions, used the totals function and selected Group By txtCountryName, then selected this query as the Row Source for cboCountry.

My plan was to then make a similar queries to populate cboInstitute and cboLastName, but when I try applying the filter it will not work, returning 'Enter Parameter Value'. If I clear this then the report will open as normal. cmdClear and cmdClose are not currently working but I am focusing on the bigger problems at the moment.

 

[jzwp22]

Rather than having to choose from a list of every country in the world (I want only those countries to which I have a record relating to), I queried tblStaffInstitutions, used the totals function and selected Group By txtCountryName, then selected this query as the Row Source for cboCountry.

The above should be OK, you could also have used a query that selects unique values only

SELECT DISTINCT txtCountryName
FROM tblStaffInstitutions

...but when I try applying the filter it will not work, returning 'Enter Parameter Value'.

This usually indicates that the record source for the report is looking for something. Is the report based on a query? Can you post the SQL text of that query?

 

[lostwithaccess]

you could also have used a query that selects unique values only

SELECT DISTINCT txtCountryName
FROM tblStaffInstitutions

I prefer this option, seems simpler so I might try that.

SQL to the best of my knowledge (this is the first time I have accessed SQL) is:

SELECT tblCountry.txtCountryName, First(tblInstitutions.txtInstitutionName) AS FirstOftxtInstitutionName, First(tblInstitutions.txtCity) AS FirstOftxtCity, First(tblStaffInstitutions.txtLinkContactPos) AS FirstOftxtLinkContactPos, First(tblStaff.txtLastName) AS FirstOftxtLastName, First(tblStaffInstitutions.txtFormalisedContact) AS FirstOftxtFormalisedContact, First(tblStaffInstitutions.txtFurtherDevelThoughts) AS FirstOftxtFurtherDevelThoughts, First(tblStaffInstitutions.txtNotes) AS FirstOftxtNotes
FROM (tblDisciplines INNER JOIN tblStaff ON tblDisciplines.pkDisciplineID=tblStaff.fkDisciplineID) INNER JOIN ((tblCountry INNER JOIN tblInstitutions ON tblCountry.pkCountryID=tblInstitutions.fkCountryID) INNER JOIN tblStaffInstitutions ON tblInstitutions.pkInstitutionID=tblStaffInstitutions.fkInstitutionID) ON tblStaff.pkStaffID=tblStaffInstitutions.fkStaffID
GROUP BY tblCountry.txtCountryName;


On a side note relating to another report, is it possible to make a report open from a command button in Report view rather than acViewPreview?

 

[jzwp22]

What are you trying to show in the report? If it is just a simple report, then you probably do not need an aggregate query which is what you show. Access has a tendency to create aggregate queries if you go through the wizard.

If you copy the SQL text you provided to a new query and run it do you get the parameter error as you did when you tried to run the report? If so, did you change field names in your tables since you created the query? Is everything spelled correctly (field and table names)?

On a side note relating to another report, is it possible to make a report open from a command button in Report view rather than acViewPreview?

Opening it in Report view will print it. You will not be able to preview it first.

 

[lostwithaccess]

The report needs to show 8 fields, although I only need to be able to filter on 3.

I ran the same SQL in a new query, created a report from that, then changed the filter coding to use that, and still got the same error.

I haven't changed any field names, and everything is spelled correctly.
If I enter no filter, the report will open as normal.

The parameter it is asking for is 'strCountry'

The SQL for the query to select the relevant countries is:

SELECT DISTINCT tblCountry.txtCountryName
FROM (tblCountry INNER JOIN tblInstitutions ON tblCountry.pkCountryID =
tblInstitutions.fkCountryID) INNER JOIN tblStaffInstitutions ON
tblInstitutions.pkInstitutionID = tblStaffInstitutions.fkInstitutionID;

 

[jzwp22]

Since the field name is txtCountryName not strCountryName that is where the problem lies. In your code you have the following; the parts shown in red must match the field names in the respective tables.

If Nz(Me.cboCountry, "") <> "" Then
strSQL = "[COLOR="Red"]strCountry[/COLOR]='" & Me.cboCountry & "' AND "
End If


If Nz(Me.cboInstitute, "") <> "" Then
strSQL = strSQL & "[COLOR="Red"]strInstitute[/COLOR]='" & Me.cboInstitute & "' AND "
End If


If Nz(Me.cboLastName, "") <> "" Then
strSQL = strSQL & "[COLOR="Red"]strLastName[/COLOR]='" & Me.cboLastName & "' AND "
End If

I am also thinking that we may need to use a standard query for the report's record source rather than the aggregate query, but try the above first and see if it works.

 

[lostwithaccess]

It works! I had looked at the 'str' bit before but thought it was just a necessary bit of code that I hadn't seen before, I had not properly adapted the example you gave me.

I'll try creating queries for the other two combo boxes to check they are ok

 

[jzwp22]

Great. Let us know if you encounter any problems with the other combo boxes.

 

[lostwithaccess]

The remaining combo boxes have thankfully all worked,

I tried adding a cmdPrint button to my report, but it was not allowing any print options, as it was just throwing the report straight to the (wrong) printer. I found the following module which I was hoping to use which should bring up the print options, but am not quite sure how to modify it to get it to work:

Option Compare Database
Option Explicit

Public strDocName As String 'Report
Public strCriteria As String

Sub stdPrint()

DoCmd.OpenReport strDocName, acPreview, , strCriteria
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, strDocName

End Sub

--------
I tried:

Option Compare Database
Option Explicit

Public rptInstitutions As String 'Report
Public strCriteria As String

Sub stdPrint()

DoCmd.OpenReport rptInstitutions, acPreview, , strCriteria
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, rptInstitutions

End Sub

I am not sure how to change strCriteria, assuming this is the right way to go about this I dont really want to have to go print preview - print options, I'd rather stick with report view?

 

[jzwp22]

I tried adding a cmdPrint button to my report, but it was not allowing any print options

You want to add a command button to a report? or did you mean form?

To what specific print options are you referring?

 

[lostwithaccess]

I'm guessing this is bad practice... It is on a report, would it be better instead to add a print button to frmReportFilter?

I would like the print button to open the window you get if you go menu > print > print, so the user can select the right printer / number of pages etc.

 

[lostwithaccess]

I also have rptInstitutions which On Close opens frmMenu, but this On Close is being triggered when a filter is applied through frmReportFilter, so I end up with my filtered report (good), frmReportFilter still open (fine) and frmMenu (not fine in this instance).

Is there a way to make frmMenu open only when the report is closed, rather than when the filter is applied?

 

[jzwp22]

I would like the print button to open the window you get if you go menu > print > print, so the user can select the right printer / number of pages etc.

I'm not really sure how to do that (that's why I usually show the report in preview mode), so you might want to pose that question in a new thread in the Reports section of the forum.

I also have rptInstitutions which On Close opens frmMenu...

I'm not following. Do you have a docmd to open the frmMenu in the On Close event of the report named rptInstitutions? I would think that would work whether or not the report is filtered.

 

[lostwithaccess]

I have a DoCmd to open frmMenu, which does work fine, but it is also being fired when I filter the report, almost as if Access closes and reopens the report to apply the filter

 

[jzwp22]

I have a DoCmd to open frmMenu

Where specifically do you have it? What event and for what object?

 

[lostwithaccess]

The DoCmd is in On Close of rptInstitutions

Private Sub Report_Close()
DoCmd.OpenForm "Menu", acNormal
End Sub

 

[jzwp22]

I don't know why opening the report filtered would by pass the on close event of the report. Maybe another new thread question?