Finding Missed Controls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The code I have pasted below checks my form for values in all the fields and
gives me a message box. However, the message indicates which fields have been
filled in and I want to know which fields are NOT filled in.

Any suggestions?

Dim ctl As Control
Dim strMissedControls As String

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
If Len(ctl.Value & "") > 0 Then
strMissedControls = strMissedControls & vbCrLf & ctl.Name
End If
End If
Next ctl

If Len(strMissedControls) > 0 Then
MsgBox "You have not completed the following fields: " & strMissedControls
Cancel = True
End If
 
Hi, Shel.
I want to know which fields are NOT filled in.

Replace:

If Len(ctl.Value & "") > 0 Then

with:

If (Nz(ctl.Value, "") = "") Then


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
 
Thank you! I feel so stupid. I should have realized that. I tried changing it
to < 0 but... anyway. thanks for your help
 
The code I have pasted below checks my form for values in all the fields and
gives me a message box. However, the message indicates which fields have been
filled in and I want to know which fields are NOT filled in.

Any suggestions?

Dim ctl As Control
Dim strMissedControls As String

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
If Len(ctl.Value & "") > 0 Then
strMissedControls = strMissedControls & vbCrLf & ctl.Name
End If

If the length of the value is greater than 0 then there's something in
the control. If it's equal to zero then there isn't.

Just change the >0 to =0.

John W. Vinson[MVP]
 
Thank you!

You're welcome!
I should have realized that.

Don't feel bad. When one is learning to write code and someone shows a
solution that works, that solution instantaneously _looks_obvious_. (It was
nothing of the sort 10 seconds prior, though.)

Actually, you could have replaced the > sign with the = sign in the original
line of code and it would have worked. I just simplified the statement as
much as possible to get the same effect, hoping the new version would be a
little easier for you to understand.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Back
Top