query in a form to a report

[M. Bakker]

Hi,
I have a database for projects. Letters and faxes are related to these projects, and shown in a subform. Above it –in the main form-, I’ve made a combobox (cboProjects). When nothing is selected here, all letters and faxes are shown in the subform. When I select a project, only the letters related to that project are shown.
Next to the combobox I’ve made a button to get the info shown in the subform to a report. This does’nt work.

My knowledge of VB is very poor, who can help? I’ve added the code that has to be modified below.

Thanks!
M. Bakker

'+++ start code:
Private Sub PrintReport_Click()
On Error GoTo Err_PrintReport_Click

Dim stDocName As String
Dim strSQL As String
If Len(Me!cboProjects) Then
strSQL = "SELECT * FROM [qryLetters] " & _
"WHERE [UitvrId]=" & Me!cboProjects
Else
strSQL = "SELECT * FROM [qryLetters]"
End If
stDocName = "rpProjectRelatedLetters"
DoCmd.OpenReport stDocName, acPreview
.RecordSource = strSQL
.Form.Requery
End With
' +++ end code

 

[Gary Miller]

Well... Have you tried compiling your code? I would think
that if this is exactly what you have there would be an
error as I see an 'End With' with no 'With' for one. Not the
problem that you are asking about.

Normally, a Report is created with a RecordSource intitially
and I will assume that this is the case for yours and the
RecordSource is your "qryLetters". If this is the case, you
do not need to specify that again. You just need to specify
the "Where" part of the query. If I am correct, try the
following modification... (Note that I am also changing the
Len on the If statement to be greater than zero.)

Dim stDocName As String
Dim strSQL As String

stDocName = "rpProjectRelatedLetters"

If Len(Me!cboProjects) > 0 Then
strSQL = "[UitvrId]= " & Me!cboProjects"
DoCmd.OpenReport stDocName, acPreview, , strSQL
Else
DoCmd.OpenReport stDocName, acPreview
End
.Form.Requery

Gary Miller
Sisters, OR


message

Hi,
I have a database for projects. Letters and faxes are

related to these projects, and shown in a subform. Above
it -in the main form-, I've made a combobox (cboProjects).
When nothing is selected here, all letters and faxes are
shown in the subform. When I select a project, only the
letters related to that project are shown.

Next to the combobox I've made a button to get the info

shown in the subform to a report. This does'nt work.

My knowledge of VB is very poor, who can help? I've added

the code that has to be modified below.

Thanks!
M. Bakker

'+++ start code:
Private Sub PrintReport_Click()
On Error GoTo Err_PrintReport_Click

Dim stDocName As String
Dim strSQL As String
If Len(Me!cboProjects) Then
strSQL = "SELECT * FROM [qryLetters] " & _
"WHERE [UitvrId]=" & Me!cboProjects
Else
strSQL = "SELECT * FROM [qryLetters]"
End If
stDocName = "rpProjectRelatedLetters"
DoCmd.OpenReport stDocName, acPreview
.RecordSource = strSQL
.Form.Requery
End With
' +++ end code