Multiple If Then Else Statments

[Thorson]

I am new to coding and fairly new to access. I set up a dialog form that has
two control boxes, the first one is a regular text box with a date mask and
the second one is a combo box with three drop down options "DSAC"; "ORR";
"URB". I created two Command buttons, the first one is to view a query of
all the current animals, the second one is to run a macro which then opens a
report to view. If the report button is clicked I would like it to run a
different macro based on what is selected in the second combo box (DSAC, ORR,
or URB).

I'm assuming that I would use an if then else statement for this, I took a
guess and tried something but as you can see below it obviously didn't work
for several reasons. Can someone help me out?

Private Sub View_Monthly_Report_Click()

If (Me.cboDate.Value) And (cboUnit = DSAC) Then
DoCmd.RunMacro "mcrRunMonthlyInventoryReportDSAC"
Else
If (Me.cboDate.Value) And (cboUnit = ORR) Then
DoCmd.RunMacro "mcrRunMonthlyInventoryReportORR"
Else
If (Me.cboDate.Value) And (cboUnit = URB) Then
DoCmd.RunMacro "mcrRunMonthlyInventoryReportURB"
Else: MsgBox "Please Select Date and Unit"
End If
End Sub

 

[Rob Parker]

Hi Thorson,

I generally find it much easier to use a Select Case construct rather than
nested If statements for choosing between more than 2 alternative actions.
My approach to this would be as shown below. But first, a few comments on
your existing code:

I'm puzzled by the test If (Me.cboDate.Value) And ... This will return False
for any valid date except 29 December 1899 (the date with a numeric value
of -1, which corresponds to True). If you want to test whether the control
is empty, you need to test for null, via If IsNull(cboDate) ...

The options for the entry in cboUnit are text strings, and must be delimited
by appropriate text delimiters.

You say that the date control is a textbox, but you have it named with the
naming convention for a combobox. If it's a textbox, call it txtDate.

Your nested If statements do not each have an End If.

The colon following the Else keyword in the third-last line will have the
effect of turning that into a label, which you can use in a GoTo statement
(bad practice in structured code) or a Resume statement (commonly used for
error-trapping).

Now, to what I think should suit (without changing name of the date
control):

Private Sub View_Monthly_Report_Click()
If IsNull(cboDate) Or IsNull(cboUnit) Then
MsgBox "Please Select Date and Unit"
Else
Select Case cboUnit
Case "DSAC"
DoCmd.RunMacro "mcrRunMonthlyInventoryReportDSAC"
Case "ORR"
DoCmd.RunMacro "mcrRunMonthlyInventoryReportORR"
Case "URB"
DoCmd.RunMacro "mcrRunMonthlyInventoryReportURB"
Case Else
MsgBox "Invalid entry in Unit drop-down"
End Select
End If
End Sub

HTH,

Rob

 

[Thorson]

Thank you so much! I will try out your suggestions and see how they work. As
far as the (Me.cboDate.Value) etc. I had originally copied the if then else
statement from a step by step guide and then tried modifying it, as you can
see I really was not sure how to put anything in. And I did forget to
re-name my text box for the date. Thanks I'll try it out!

 

[Thorson]

Rob,

It works perfectly!! I do have another question though, if I change the
combo box "cboUnit" to a list box "lstUnit" how would my code change? I
tried it and it didn't work (even when I changed the name to correspond in
the code). I would like to change it to a list box for another function in
the form, I have another command button that opens a query, I would like to
give the user the option to select multiple Units. I guess that it might
also be useful to do the same thing for the first command button.

Would this be to complicated? Or is it feasible?

 

[Rob Parker]

Hi Thorson,

The code will work fine with a single-select listbox (Multi-Select set to
None), rather than a combobox. You need to change the control name in two
places - in the IsNull test in the If statemenet, and in the Select Case
line.

If the listbox is multi-select, then IsNull test will fail. You can
determine whether an entry has been selected by checking the listbox
ItemSelected.Count. So the If statement would become:
If IsNull(txtDate) Or (lstUnit.ItemsSelected.Count = 0) Then

If you want to select the case for the first item selected in the list (ie.
the top item, NOT the first one clicked on to select it), you can do so by
changing the Select Case statement to:
Select Case lstUnit.ItemData(0)

You'd have to define exactly what you would want to happen if this is not
the case.

As an aside, using a multi-select listbox to set criteria for a query (I
assume that's what you're wanting to do) is not trivial - you can't just put
[Forms].[FormName].[lstUnit] into the criteria box in the query design grid.
You need to build the SQL string for the criteria from the selected items in
the listbox (sample code is available on Allen Browne's excellent site,
here: http://allenbrowne.com/ser-50.html), then create (or modify) the query
definition via code to incorporate the SQL filter string (code similar to
that shown here: http://allenbrowne.com/func-DAO.html#CreateQueryDAO).

Again, HTH,

Rob