how do i recognize previous control's value?

[rocket5292]

i'm creating a userform in excel that is basically a deposit slip and has three columns of controls running down it. The first column is comboboxes that are populated with every company that we get a check from. The user selects the company and then tabs to the second column which is a textbox where theyre suppose to type in the checknumber. The third column is a textbox where they put in the check amount. I have 20 rows of these three controls on my userform so that someone can enter up to 20 checks per deposit. How would I write code so that i can have a msgbox pop up if someone only partially fills in one of the rows. i.e. someone puts in a check amount but not a check number? thanks for the help.

 

[fumei]

The only way is the long way - careful (and often tedious) validation logic.

WHEN do you want to do this? When the user presses an OK let's continue button?

Gerry
My paintings and sculpture

 

[rocket5292]

Yeah, I'm sorry I should have put that in there. I've got a submit commandbutton at the top thats gonna dump all of this into a database and i would like to check to make sure everything is filled out before i run the insert queries. I think I figured it out though. I just changed all of the names to the controltype and what line theyre one. so the combobox on line 20 is named CmbLine20 and so forth. This is the code I have so far but as you can see I'm not anywhere near finished with it. It seems to work so far but I wouldnt mind suggestions if anyone has any.

Private Sub CommandButton2_Click()
Dim CmbLine As Control
Dim TxtNumLine As Control
Dim TxtLine As Control

Dim x As Integer

For x = 1 To 30

For Each CmbLine In UserForm2.Controls
If CmbLine.Name = "CmbLine" & x Then
Exit For
End If
Next

For Each TxtNumLine In UserForm2.Controls
If TxtNumLine.Name = "TxtNumLine" & x Then
Exit For
End If
Next

For Each TxtLine In UserForm2.Controls
If TxtLine.Name = "TxtLine" & x Then
Exit For
End If
Next


If IsNull(CmbLine) = True And (Trim(TxtNumLine.Value) <> "" Or Trim(TxtLine.Value) <> "") Then
MsgBox ("You Did Not Completely Fill out Line " & x)
Exit For
Exit Sub
End If

...

Next

End Sub



Thanks.

 

[fumei]

Not bad. really it is just a question of determining your logic.

Do ALL the textboxes have to filled? In other words, if you have 20 possible entry lines (combobox with company, textbox with check number, textbox with check amount), what if they only put in 11 entries. Is this an error if the remaining nine lines (18 textboxes) are blank?

I would not think so...unless of course you DO want all 20 possible entries to be filled.

So...it is not that the textbox is blank, but that a textbox is blank on a line associated with a combobox with a selected item.

You could have the comboboxes have as the first item = "Select a company". Now when you start your logic you would first test for each combobox value.

If it is NOT "Select a company", then check the value of the associated textboxes.

If it IS "Select a company", then you can ignore the associated textboxes. Both in terms of checking their values, and also any use of inadvertant values.

By associated, I mean a judicious use of control names.

Line 1:
cboCompany1 txtCompany1CheckNum txtCompany1CheckAmt

Line 2:
cboCompany2 txtCompany2CheckNum txtCompany2CheckAmt

etc.

That would make it easier to make the associations.

You check each combobox. Is it "Select a company"? Go to next combobox. It is NOT "Select a company", strip off the relevant part of the combobox name (say, a string...strCurrentcbo = "Company1"), and use that string to build a control name string ("txt" & strCurrentcbo & "CheckNum") and check that.

Dim oCtl As Control
Dim strCurrentcbo As String
For Each oCtl in Me.Controls
 If TypeOf oCtl Is Combobox Then
   If oCtl.Value <> "Select a company" Then
     strCurrentcbo = Right(oCtl.Name, Len(oCtl.Name) - 3)
     If Me.Controls("txt" & _
        strCurrentcbo & "CheckNum").Text = "" Or _
        Me.Controls("txt" & _
            strCurrentcbo & "CheckAmt").Text = "" Then
[COLOR=red]     ' either build an string array of [b]all[/b]
     ' missing textbox values, and display at the
     ' end of validation, or display one at a time
     ' personally I would build an array of error
     ' messages and display them all as ONE message[/color red]
     End If
   End If
 End If
Next

There are of course many ways to go about this, but at least with this way if a company combobox has no company selected your validation logic does not care about the associated textboxes.


Gerry
My paintings and sculpture