checking multiple text boxes in VB

[New]

I am using Acess 2003 Code Builder.

Can I do the following? Why is the syntax wrong?

If ((txtDistrictName.Text="") Or (txtSchoolName.Text="") Or
(CountyName.Text="") Or (txtDistrictCode.Text)="") Or
(txtSchoolCode.Text="") Or (txtRegionNumber.Text="") Or and
(cboProgramName.text="") Or (cboFiscalYear.Text="") ) Then

MsgBox "Please enter at least one search criteria to see records.",
vbOKOnly, "Error"
txtDistrictName.setFocus()

End If

 

[Ken Halter]

I am using Acess 2003 Code Builder.

Can I do the following? Why is the syntax wrong?

(txtDistrictCode.Text)="") <-- extra paren and you have an 'Or And' in
there too... also, it's hard to tell if 'CountyName' is a box or a variable.

'here's another way...
If (txtDistrictName.Text = "") _
Or (txtSchoolName.Text = "") _
Or (CountyName.Text = "") _
Or (txtDistrictCode.Text = "") _
Or (txtSchoolCode.Text = "") _
Or (txtRegionNumber.Text = "") _
Or (cboProgramName.Text = "") _
Or (cboFiscalYear.Text = "") Then

MsgBox "Please enter at least one search criteria to see records.",
vbOKOnly, "Error"
txtDistrictName.SetFocus

End If

Here's the "long hand" way... using copy/paste, it's not much more typing
but it's far easier to read (imo) plus, using Len is faster than comparing
strings.
'==========
Private Sub Command1_Click()
Dim bEmptyBox As Boolean

If Len(txtDistrictName.Text) = 0 Then
bEmptyBox = True
End If

If Len(txtSchoolName.Text) = 0 Then
bEmptyBox = True
End If

If Len(CountyName.Text) = 0 Then
bEmptyBox = True
End If

If Len(txtDistrictCode.Text) = 0 Then
bEmptyBox = True
End If

If Len(txtSchoolCode.Text) = 0 Then
bEmptyBox = True
End If

If Len(txtRegionNumber.Text) = 0 Then
bEmptyBox = True
End If

If Len(cboProgramName.Text) = 0 Then
bEmptyBox = True
End If

If bEmptyBox Then

MsgBox "Please enter at least one search criteria to see records.",
vbOKOnly, "Error"
txtDistrictName.SetFocus

End If

End Sub
'==========

 

[Mike Williams]

I am using Acess 2003 Code Builder.
Can I do the following? Why is the syntax wrong?

You're in the wrong newsgroup. This one is for Classic VB (VB6). You need to
look for a VBA (VB for Applications) or an Acesss newsgroup. VBA is a cut
down version of Classic VB, and is not the same (although in many cases the
standard syntax is very similar). In your specific case it looks like you've
got an extra ) after the equals sign following txtDistrictCode.Text. Get rid
of it. Also, what's the "Or and" doing in there? Where did that wortd
"and" come from? Also, I'm assuming that one of your text boxes is called
txtCountyName - if so then you've missed out the initial "txt" bit.

By the way, your email system is hard wrapping line in your messages. This
means that your code sample cannot be pasted into some code. If you want to
post code samples with long lines of code then either stop your email system
doing the hard wrap or alternatively use to "space underscore" method of
breaking up code lines into a number of smaller sections.

Mike

 

[expvb]

I am using Acess 2003 Code Builder.

Can I do the following? Why is the syntax wrong?

If ((txtDistrictName.Text="") Or (txtSchoolName.Text="") Or
(CountyName.Text="") Or (txtDistrictCode.Text)="") Or
(txtSchoolCode.Text="") Or (txtRegionNumber.Text="") Or and
(cboProgramName.text="") Or (cboFiscalYear.Text="") ) Then

MsgBox "Please enter at least one search criteria to see records.",
vbOKOnly, "Error"
txtDistrictName.setFocus()

End If

Besides what others suggested, you have to replace all "Or" with "And" for
the text in MsgBox to be relevant.

 

[Dirk Goldgar]

(txtDistrictCode.Text)="") <-- extra paren and you have an 'Or And'
in there too... also, it's hard to tell if 'CountyName' is a box or a
variable.

'here's another way...
If (txtDistrictName.Text = "") _
Or (txtSchoolName.Text = "") _
Or (CountyName.Text = "") _
Or (txtDistrictCode.Text = "") _
Or (txtSchoolCode.Text = "") _
Or (txtRegionNumber.Text = "") _
Or (cboProgramName.Text = "") _
Or (cboFiscalYear.Text = "") Then

MsgBox "Please enter at least one search criteria to see
records.", vbOKOnly, "Error"
txtDistrictName.SetFocus

End If

Here's the "long hand" way... using copy/paste, it's not much more
typing but it's far easier to read (imo) plus, using Len is faster
than comparing strings.
'==========
Private Sub Command1_Click()
Dim bEmptyBox As Boolean

If Len(txtDistrictName.Text) = 0 Then
bEmptyBox = True
End If

If Len(txtSchoolName.Text) = 0 Then
bEmptyBox = True
End If

If Len(CountyName.Text) = 0 Then
bEmptyBox = True
End If

If Len(txtDistrictCode.Text) = 0 Then
bEmptyBox = True
End If

If Len(txtSchoolCode.Text) = 0 Then
bEmptyBox = True
End If

If Len(txtRegionNumber.Text) = 0 Then
bEmptyBox = True
End If

If Len(cboProgramName.Text) = 0 Then
bEmptyBox = True
End If

If bEmptyBox Then

MsgBox "Please enter at least one search criteria to see
records.", vbOKOnly, "Error"
txtDistrictName.SetFocus

End If

End Sub
'==========

However, if these are Access text boxes, you shouldn't use the .Text
property for this. That property is only available when the control has
the focus, and has only very special applications. In Access, you
mostly use the control's Value property, which is the default property
of all data controls such as text boxes and combo boxes.

There's a complication, because the control may hold either a
zero-length string ("") or the pseudo-value Null, and they aren't the
same. You can test for them both in one go by concatenating the
control's value to "", which will convert it to a zero-length string if
it's Null:

If Len(txtDistrictName & "") = 0 _
Or Len(txtSchoolName & "") = 0 _
Or Len(CountyName & "") = 0 _
Or Len(txtDistrictCode & "") = 0 _
Or Len(txtSchoolCode & "") = 0 _
Or Len(txtRegionNumber & "") = 0 _
Or Len(cboProgramName & "") = 0 _
Or Len(cboFiscalYear & "") = 0 _
Then

 

[Ken Halter]

However, if these are Access text boxes, you shouldn't use the .Text
property for this. That property is only available when the control has
the focus, and has only very special applications. In Access, you

Sheesh... shows what I know about VBA, eh? <g> It seems like more and more
VBA users are posting here. Is it because the VBA groups are collecting
spider webs? or just because "VB" is in the name. I assumed it was the
spider web thing and that's why I've been "attempting" to answer.

 

[Dirk Goldgar]

Besides what others suggested, you have to replace all "Or" with
"And" for the text in MsgBox to be relevant.

You're absolutely right! We've been sidetracked by the technical
issues. Good eye!

 

[Dirk Goldgar]

Sheesh... shows what I know about VBA, eh? <g> It seems like more and
more VBA users are posting here. Is it because the VBA groups are
collecting spider webs? or just because "VB" is in the name. I
assumed it was the spider web thing and that's why I've been
"attempting" to answer.

It's a cross-post. Sometimes the straight VB groups are relevant -- at
least "classic" VB -- but most Access VBA questions depend on a
knowledge of the Access object model, and really belong only in the
Access coding/programming groups.

 

[argusy]

I disagree!!!

Besides what others suggested, you have to replace all "Or" with "And" for
the text in MsgBox to be relevant.

What if only one text box has been missed?

If you 'and' the lot, all text boxes have to be blank for the message box to appear.

Which is not what the message box is saying

Argusy

 

[Douglas J. Steele]

I disagree!!!



What if only one text box has been missed?

If you 'and' the lot, all text boxes have to be blank for the message box
to appear.

Which is not what the message box is saying

Actually, that IS what the message box is saying. The message says "Please
enter at least one search criteria", so you only want it to appear if none
of the search criteria have been entered.

Unfortunately, an additional complication is that if nothing is in the
textbox, it's going to show up as Null, so checking for = "" won't work.
What's really required is:

If (Len(txtDistrictName & "") = 0 _
And Len(txtSchoolName & "") = 0 _
And Len(CountyName & "") = 0 _
And Len(txtDistrictCode & "") = 0 _
And Len(txtSchoolCode & "") = 0 _
And Len(txtRegionNumber & "") = 0 _
And Len(cboProgramName & "") = 0 _
And Len(cboFiscalYear & "") = 0 Then

MsgBox "Please enter at least one search criteria to see records.", _
vbOKOnly, "Error"
txtDistrictName.SetFocus

End If

(although to be more efficient it would be better to use vbNullString than
"")

 

[Larry Serflaten]

I disagree!!!

What if only one text box has been missed?

If you 'and' the lot, all text boxes have to be blank for the message box to appear.

But the message indicates any 'one' entry is OK, so they all have to
be blank for the input to be invalid.

"Please enter at least one search criteria" ...

Another way to write that would have been:

If Len(txtDistrictName & _
txtSchoolName & _
CountyName & _
txtDistrictCode & _
txtSchoolCode & _
txtRegionNumber & _
cboProgramName & _
cboFiscalYear & _
"") = 0 _
Then
MsgBox "Please enter at least one search criteria to see records.", vbOKOnly, "Error"
End If


Again, any value in any textbox would add to the length so that the
message would not be shown....

HTH
LFS

 

[Rick Rothstein]

Another way to write that would have been:

If Len(txtDistrictName & _
txtSchoolName & _
CountyName & _
txtDistrictCode & _
txtSchoolCode & _
txtRegionNumber & _
cboProgramName & _
cboFiscalYear & _
"") = 0 _
Then
MsgBox "Please enter at least one search criteria to see records.",
vbOKOnly, "Error"
End If

Or, assuming VB is as efficient at getting the length of text in a TextBox
as it is in getting the length of text in a String variable...

If Len(txtDistrictName) + Len(txtSchoolName) + _
Len(CountyName) + Len(txtDistrictCode) + _
Len(txtSchoolCode) + Len(txtRegionNumber) + _
Len(cboProgramName) + Len(cboFiscalYear) = 0 Then

Rick

 

[Larry Serflaten]

Or, assuming VB is as efficient at getting the length of text in a TextBox
as it is in getting the length of text in a String variable...

If Len(txtDistrictName) + Len(txtSchoolName) + _
Len(CountyName) + Len(txtDistrictCode) + _
Len(txtSchoolCode) + Len(txtRegionNumber) + _
Len(cboProgramName) + Len(cboFiscalYear) = 0 Then

But from what Dirk said:

"In Access, you mostly use the control's Value property, which is the
default property of all data controls such as text boxes and combo boxes."

And:

"There's a complication, because the control may hold either a
zero-length string ("") or the pseudo-value Null, and they aren't the
same. "


I was going to use Len, but when I tried this:

If (Len(Null) + Len(Null)) = 0 Then
Debug.Print "0"
Else
Debug.Print "Not 0"
End If


The result was "Not 0" so I figured using Len may not work....

LFS

 

[Douglas J. Steele]

That's correct, Larry. However, you can get around it by taking advantage of
the difference between & and + with respect to string concatenation:

If Len(txtDistrictName & "") + Len(txtSchoolName & "") + _
Len(CountyName & "") + Len(txtDistrictCode & "") + _
Len(txtSchoolCode & "") + Len(txtRegionNumber & "") + _
Len(cboProgramName & "") + Len(cboFiscalYear & "") = 0 Then

 

[Dirk Goldgar]

That's correct, Larry. However, you can get around it by taking
advantage of the difference between & and + with respect to string
concatenation:

If Len(txtDistrictName & "") + Len(txtSchoolName & "") + _
Len(CountyName & "") + Len(txtDistrictCode & "") + _
Len(txtSchoolCode & "") + Len(txtRegionNumber & "") + _
Len(cboProgramName & "") + Len(cboFiscalYear & "") = 0 Then

I thought Larry's original suggestion -- checking the length of the
concatenation of them all with "" -- was pretty good.

 

[Douglas J. Steele]

Yeah, definitely. One concatenation is sufficient. Was that Larry's
suggestion? (I'm losing track of the players!)

 

[argusy]

I Like that approach, Rick - I have never thought of concatenating the lot
I guess that's what's called lateral thinking
BTW, I feel a bit embarrassed about my logic on a previous reply.
But, then, I always did have to sit down and think about 'and' , 'or', 'nand'
,'nor' and 'not' operators in logic
What's really embarrassing is that I taught basic computer logic for eight years.
I should know it backwards, in my sleep

Argusy