Open Report - using a selection made by combo box

[Johanna Gronlund]

Hello,

I am trying to get Access to preview my report for a selected practice. I
have a form with a combo box (Combo0) and a command button (Command0). The
combo box has two columns, one for the PracticeID which is an autonumber and
one for PracticeName that the user actually sees. The row source is as
follows:
SELECT Tbl_Practices.Practice_code, Tbl_Practices.Senior_Partner_Surname
FROM Tbl_Practices ORDER BY Tbl_Practices.Senior_Partner_Surname;

On the form there is also a command button (Command0) that opens a report. I
have used the wizard to build the OpenReport function. The code is below:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Rpt_Calc Payments for ES"
DoCmd.OpenReport stDocName, acPreview

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

The form is based on a table ‘Tbl_Practice code store for financial summary’.
The report is based on a query ‘Qry_Calc payments for ES Summary’ that uses
table ‘Tbl_Practices’ and table ‘Tbl_Practice code store for financial
summary’ (equal joint).

The report opens but not with the right practice. It looks like the
‘Tbl_Practice code store for financial summary’ is not updating correctly
after the selection on the Combo box. There is either no practice code in the
table or there are several, including ones previously selected. Can anyone
provide any insight why this might be happening???

Sorry about the long question. I have tried asking this in a shorter format
but haven’t received a helpful reply yet.

Thanks very much for any help that you might be able to provide.

 

[Marshall Barton]

I am trying to get Access to preview my report for a selected practice. I
have a form with a combo box (Combo0) and a command button (Command0). The
combo box has two columns, one for the PracticeID which is an autonumber and
one for PracticeName that the user actually sees. The row source is as
follows:
SELECT Tbl_Practices.Practice_code, Tbl_Practices.Senior_Partner_Surname
FROM Tbl_Practices ORDER BY Tbl_Practices.Senior_Partner_Surname;

On the form there is also a command button (Command0) that opens a report. I
have used the wizard to build the OpenReport function. The code is below:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Rpt_Calc Payments for ES"
DoCmd.OpenReport stDocName, acPreview

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

The form is based on a table ‘Tbl_Practice code store for financial summary’.
The report is based on a query ‘Qry_Calc payments for ES Summary’ that uses
table ‘Tbl_Practices’ and table ‘Tbl_Practice code store for financial
summary’ (equal joint).

The report opens but not with the right practice. It looks like the
‘Tbl_Practice code store for financial summary’ is not updating correctly
after the selection on the Combo box. There is either no practice code in the
table or there are several, including ones previously selected.

What is the report's record source query's ctriteria?

The recommended practice is to leave the criteria out of the
query and change your code to something more like:

.. . .
Dim stDocName As String
Dim stCriteria As String
stDocName = "Rpt_Calc Payments for ES"
stCriteria = "Practice_code=" & Me.thecombobox
DoCmd.OpenReport stDocName, acViewPreview, _
& WhereCondition:= stCriteria
.. . .