PDA

View Full Version : Best way to do an add/update button.



Sir Phoenix
10-03-2005, 01:49 PM
*sigh*I'm coding this button on a form. When you press it, I want it to verify the values in all appropriate input fields, then create a string (name) based on the number of entries in the list. Here's the code:

Private Sub cmdAdd_Click()
Dim daysOff As Long
Dim rngLook As Range, rngFound As Range

daysOff = 0
If cmbSunday.Value = "off" Then
daysOff = daysOff + 1
End If
If cmbMonday.Value = "off" Then
daysOff = daysOff + 1
End If
If cmbTuesday.Value = "off" Then
daysOff = daysOff + 1
End If
If cmbWednesday.Value = "off" Then
daysOff = daysOff + 1
End If
If cmbThursday.Value = "off" Then
daysOff = daysOff + 1
End If
If cmbFriday.Value = "off" Then
daysOff = daysOff + 1
End If
If cmbSaturday.Value = "off" Then
daysOff = daysOff + 1
End If
If cmbSchedType.Value = "" Then
MsgBox ("Select a schedule type and fill out the week.")
Else
If chkStatic.Value = True And cmbStaticName.Value = "" Then
MsgBox ("Select a person to assign this schedule to!")
Else
If cmbSunday.Value = "" Or cmbMonday.Value = "" Or _
cmbTuesday.Value = "" Or cmbWednesday.Value = "" Or _
cmbThursday.Value = "" Or cmbFriday.Value = "" Or _
cmbSaturday.Value = "" Then
MsgBox ("Select time, off, or other for every day of the week!")
Else
If (cmbSunday.Value = "other" And txtSunday.Value = "") Or _
(cmbMonday.Value = "other" And txtMonday.Value = "") Or _
(cmbTuesday.Value = "other" And txtTuesday.Value = "") Or _
(cmbWednesday.Value = "other" And txtWednesday.Value = "") Or _
(cmbThursday.Value = "other" And txtThursday.Value = "") Or _
(cmbFriday.Value = "other" And txtFriday.Value = "") Or _
(cmbSaturday.Value = "other" And txtSaturday.Value = "") Then
MsgBox ("Please enter time for 'other' days!")
Else
If (cmbSunday.Value <> "off" And txtSunAssign.Value = "") Or _
(cmbMonday.Value <> "off" And txtMonAssign.Value = "") Or _
(cmbTuesday.Value <> "off" And txtTueAssign.Value = "") Or _
(cmbWednesday.Value <> "off" And txtWedAssign.Value = "") Or _
(cmbThursday.Value <> "off" And txtThuAssign.Value = "") Or _
(cmbFriday.Value <> "off" And txtFriAssign.Value = "") Or _
(cmbSaturday.Value <> "off" And txtSatAssign.Value = "") Then
MsgBox ("Please enter assignments for all working days!")
Else
If daysOff <> 2 Then
If MsgBox("You've selected something other than 2 days off. Continue?", vbOKCancel) = vbCancel Then
Stop
Else
addMe
End If
End If
End If
End If
End If
End If
End If
End Sub

As you've noticed, there's lot's of nesting. First, for the bottom level, I want to know if stop is the correct method to use to stop the current method. (That is, make the button do nothing)

addMe is as of yet, uncoded. What it's supposed to do is look at the contents of one combo box (which will be one of three things.)
Then look at a dynamically expanding range. If the combo box says 'foo', it should count the number of entries where the left 3 characters are foo. (foo1 and foo2 and foo3 and foo4 count as 4 items of type foo). If the combobox says 'blah', it should do the same thing. (only counting blah's). After it has gone through the list, it needs to add to the bottom (empty row) the combobox contents+count#. (In the foo example the added cell would be foo5).

Finally, I know that's a ton of nesting, so if you can help me clean it up, I'd appreciate it. (not necessary, though!)

Zack Barresse
10-03-2005, 03:26 PM
Hey Sir Phoenix,

One thing you can do is those first If/Then's, you can make them one-liners ...

If cmbSunday.Value = "off" Then daysOff = daysOff + 1

The other thing I would suggest is you may want to think about putting your controls (i.e. cmbSunday, cmbMonday, etc) values into an array. This will help you test for values later on and probably save you some space.