Printing a report determined by which combo box is not null 2

[scliffe]

I'm looking to change the design of a form so that there are 3 combo boxes (cmbStaff, cmbRoom, cmbClass) that either have a value to filter by or a Null value (e.g. I've set the code to make Null cmbRoom and cmbClass when you select an item from cmbStaff etc.) so only 1 combo box has a value.

I want to add a command button that will print one of three reports depending on which combo box has a value. I've played around with If and nested If but its Friday and my brain isn't working... Any suggestions? The code as it stands is below. Its probably staring me in the face (wood for the trees...):

****************
Private Sub btnTTview_Click()

Dim stDocName As String
If cmbRoom <> "" Then
stDocName = "rpt_TT_Room"
DoCmd.OpenReport stDocName, acPreview
If cmbClass <> "" Then
stDocName = "rpt_TT_Class"
DoCmd.OpenReport stDocName, acPreview
If cmbStaff <> "" Then
stDocName = "rpt_TT_Staff"
DoCmd.OpenReport stDocName, acPreview
End If
End If
Else
MsgBox "Please select a Room, Class or Member of Staff to view a Timetable"
End If

End Sub
****************

Thanks

Simon

 

[Remou]

How about:

If Not IsNull(cmbRoom) Then
   stDocName = "rpt_TT_Room"
End If
If Not IsNull(cmbClass) Then
   stDocName = "rpt_TT_Class"
End If           
If Not IsNull(cmbStaff) Then
   stDocName = "rpt_TT_Staff"
End If

If Trim(stDocName & "")<>"" Then
   DoCmd.OpenReport stDocName, acPreview
Else
   MsgBox "Please select a Room, Class or Member of Staff to view a Timetable"
End If

 

[TheAceMan1]

or this:

[blue]Private Sub btnTTview_Click()
   Dim stDocName As String
   
   If Trim(cmbRoom.Column(1) & "") <> "" Then
      stDocName = "rpt_TT_Room"
   ElseIf Trim(cmbClass.Column(1) & "") <> "" Then
      stDocName = "rpt_TT_Class"
   ElseIf Trim(cmbStaff.Column(1) & "") <> "" Then
      stDocName = "rpt_TT_Staff"
   End If
   
   If stDocName <> "" Then
      DoCmd.OpenReport stDocName, acPreview
   Else
      MsgBox "Please select a Room, " & _
             "Class or Member of Staff to view a Timetable"
   End If

End Sub[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

Aceman1

Scliffe mentions that "I've set the code to make Null cmbRoom and cmbClass when you select an item from cmbStaff etc." (my embolding).

Therefore, it cannot be necessary to check for blanks and nulls again (If Trim ... & ""), so

[tt]If Not IsNull(cmbRoom)[/tt]

Should be the most suitable.

 

[scliffe]

Thanks for the responses. I'm not back in work until tomorrow but its all clear to me now! I'll be going with Remou's and will get back to you if I run into a problem.

 

[scliffe]

OK, correction... Its obviously not clear to me yet! Neither solution works fully though Aceman's does have the Staff and Class options working, though not the room one (which produces the msgbox error message... Remou, yours works fine for Staff but the Room and Class options produce a blank timetable (though no error messages)

 

[Remou]

The code I suggested is only voncerned with which report is opened, not the contents of the report. That is another issue. Try opening the form and then opening a report 'by hand', to check. You might also wish to step through the code to ensure that the report you expect to be selected is selected, if it is not, then check that the combo box is being correctly set to null.

 

[scliffe]

I did have the reports opening correctly beforehand (I had 3 command buttons, one for each report) and was trying to simplify the interface/form to contain just 1 button but leave the 3 combo boxes in place. The code for setting each other to null when making a selection from one is...

Private Sub cmbStaff_Click()
cmbRoom = ""
cmbClass = ""
End Sub

(with the names changed for each combo)

 

[scliffe]

Sorted. Thanks for the pointers Remou - with setting the cmbRoom to "" seemed to be a problem but solved it with the code as follows:

Private Sub btnTTview_Click()

Dim stDocName As String
If IsNull(cmbRoom) = False Then
If cmbRoom <> "" Then
stDocName = "rpt_TT_Room"
End If
End If
If IsNull(cmbClass) = False Then
If cmbClass <> "" Then
stDocName = "rpt_TT_Class"
End If
End If
If IsNull(cmbStaff) = False Then
If cmbStaff <> "" Then
stDocName = "rpt_TT_Staff"
End If
End If
If Trim(stDocName) <> "" Then
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "Please select a Room, Class or Member of Staff to view a Timetable"
End If

End Sub

All is working well now thanks.

 

[Remou]

There is a big difference between an empty string ("") and Null. The construct that Aceman1 showed is commonly used to trap Nulls, empty strings, and space filled strings, that is:

Trim(txtText & "") <> ""

Adding an empty string to the field eliminates a Null and Trim eliminates spaces, so the control can then be tested to see if it is an empty string.