VBA help in validating certain textboxes/combo box 1

[iuianj07]

Hello guys,

I need help in creating VBA for this database I am currently working on.

For example in my form I have Name, address and position textboxes and have gender as a combobox, and I have a checkbox for completed.

I would want to have my form that when I click the chexbox, it will first check if all other fields (Name, Address and position) textboxes are already filled up (have an error message maybe that Address have yet to be entered yet?) before I could check the checkbox.

Any help is greatly appreciated.

Thanks

 

[SkipVought]

What code do you have so far? Please post, and indicate where you are having problems.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

In your checkbox's before_update event, you'll want to check each of the other controls for values. If you don't have a bunch of other controls of the same type, then you can just loop through all controls, checking those of a certain type.

So...

 

[kjv1611]

...I'll wait to see the response to Skip's question first.

 

[iuianj07]

what I have is

this is for the before update of the checkbox

If me.Name is not null then
me.setfocus
else
Msgbox("Please Enter Name", vbCritical, "Error Message") As VbMsgBoxResult

End If


I only tried one textbox to see if it'll work, but it gives an error saying "Statement Invalid outside type block"

Thanks

 

[MajP]

to check a control for a value either

if not isnull(me.controlName) then

or better to handle null, empty strings, and spaces

if trim(me.controlname & " ") = "" then

 

[dhookom]

You should not name anything with a name of name. Name is a property of everything in Access so it is a reserved word. Me.Name in a form will probably return the name of the form, not the value in a name field.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

How about this:

On a Form, have txtName, txtAddress, txtPosition (text boxes) and cmdInsert (command button):

Option Explicit

Private Sub UserForm_Initialize()
    cmdInsert.Enabled = False
    cmdInsert.Caption = "Insert Record"
End Sub

Private Sub txtAddress_Change()
Call DealWithInsert
End Sub

Private Sub txtName_Change()
Call DealWithInsert
End Sub

Private Sub txtPosition_Change()
Call DealWithInsert
End Sub

Private Sub DealWithInsert()

If Len(Trim(txtName.Text)) = 0 Or _
    Len(Trim(txtAddress.Text)) = 0 Or _
    Len(Trim(txtPosition.Text)) = 0 Then
        cmdInsert.Enabled = False
Else
    cmdInsert.Enabled = True
End If

End Sub

Or if you don't like command buttons and have your heart set on checkboxes, all text boxes are the same as above, but instead of command button place a CheckBox1 on the Form:

Option Explicit

Private Sub UserForm_Initialize()
    CheckBox1.Locked = True
    CheckBox1.Caption = "All Completed"
End Sub

Private Sub txtAddress_Change()
Call DealWithCheckBox
End Sub

Private Sub txtName_Change()
Call DealWithCheckBox
End Sub

Private Sub txtPosition_Change()
Call DealWithCheckBox
End Sub

Private Sub DealWithCheckBox()

If Len(Trim(txtName.Text)) = 0 Or _
    Len(Trim(txtAddress.Text)) = 0 Or _
    Len(Trim(txtPosition.Text)) = 0 Then
        CheckBox1.Value = False
Else
    CheckBox1.Value = True
End If

End Sub

Have fun.

---- Andy

 

[dhookom]

Andy,
I assume the OP is posting a question about Access. Your reply is not Access VBA. Please don't add to someone's confusion. I'm not aware of an Initialize event in Access forms and the Text property of a control is very rarely used and will create errors in the code you suggested.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

@dhookom

I apologize for the confusion, in the database I am working on, I do not have "Name" as the name of anything, I just used this as an example for my post here.

 

[kjv1611]

iuianj07,

I think it'd be worth mentioning the Reddick naming convention. Though you don't HAVE to stick to such, having some sort of naming convention that you always follow is usually best. It makes it easier for you (and anyone behind you) to go back and review your code). It also makes troubleshooting easier, b/c you can find elements in your code much more quickly with your eye.

Andrzejek's code example is a good example of such usage.

For a reference, look here:

http://www.xoc.net/standards/rvbanc.asp

I didn't look at the details of Andrzejek's code to see what was going on, but I did notice the use of a naming convention/standard there at a quick glance. So, that's all I'm saying about it.

Follow-up with where you're at, currently. I'll do my best to check back on here either this evening or tomorrow morning.

 

[iuianj07]

thanks kjv1611! I'll keep that in mind going forward...

 

[dhookom]

I assume your question still hasn't been answered correctly. Sample code might look like:

If IsNull(Me.txtName) Then
   Msgbox "Please Enter Name", vbCritical, "Error Message"
   Me.txtName.SetFocus
End If

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Thanks dhookom!