vba if statement error

[vicsaccess]

ok I've got this control button on my form that advances the record to the next record, I've noticed that the operators are forgetting to fill some of the boxes so I am putting some code into the on click event to check the boxes to see if they are null, if so msgbox stating which box and sets focus on that box. works great till I get to my control called "tag" then I get a compile error: invalid qualifier, don't see any difference other than the control "Tag" that is getting the error is a long Text. anyone have any ideals?


If IsNull([Forms]![coilf]![IP]) Then
MsgBox "Check IP Number"


Me.IP.SetFocus
Exit Sub
End If
If IsNull([Forms]![coilf]![Tag]) Then
MsgBox "Check tag Number"
Me.Tag.SetFocus
Exit Sub
End If
If IsNull([Forms]![coilf]![Original_Coil_Weight]) Then
MsgBox "Check Coil Weight"
Me.Original_Coil_Weight.SetFocus
Exit Sub
End If
If IsNull([Forms]![coilf]![Coil_Start_Time]) Then
MsgBox "Check Start Time"
Me.Coil_Start_Time.SetFocus
Exit Sub
End If

[pbaldy]

Tag is a reserved word (forms and controls have a Tag property). One option is to rename the control, another (untested) is:

Me.[Tag].SetFocus

[Missinglinq]

Actually, Tag is not a Reserved Word...it's a Control Property...so Pauls' untested suggestion of using Square Brackets won't work, you'll have to change the Control's Name, as he originally said. Oddly, though, you apparently can actually let the name of the Field, in the Table, remain as Tag!

Your validation code needs to be in the Form_BeforeUpdate event...not in the code for the Command Button; otherwise, if the user decides to quit entering Records and simply Close the Form/Database...and has forgotten to enter data in any of the Controls, that Record will be saved sans the data. Placing the code in the Form_BeforeUpdate event ensures that no Record will be saved only partially filled out, regardless of how the Current Record is exited.

Also, when referring to a Control on a Form, in that Form's code, it's much easier to to use the Me. construct, as in

Me.IP

rather than the longer reference of

[Forms]![coilf]![IP]

Here's a modification of your code, using the above and renaming your Control named Tag with the name TagNum.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
 If IsNull(Me.IP) Then
  MsgBox "Check IP Number"
  Me.IP.SetFocus
  Cancel = True
  Exit Sub
 End If

 If IsNull(Me.TagNum) Then
  MsgBox "Check tag Number"
  Me.TagNum.SetFocus
  Cancel = True
  Exit Sub
 End If
  
 If IsNull(Me.Original_Coil_Weight) Then
  MsgBox "Check Coil Weight"
  Me.Original_Coil_Weight.SetFocus
  Cancel = True
  Exit Sub
 End If

 If IsNull(Me.Coil_Start_Time) Then
   MsgBox "Check Start Time"
   Me.Coil_Start_Time.SetFocus
   Cancel = True
   Exit Sub
 End If

End Sub

Now simply reduce the code of your Command Button to just what is needed to move to another, New Record, and when it is clicked Access will fire the Form_BeforeUpdate event, and the validation code will run.

Linq ;0)>

[vicsaccess]

thanks missinglinq, what is the cancel function you added?

[Missinglinq]

The Form_BeforeUpdate event is the last event before a Record is saved...which is why you always place validation code, whose purpose is to ensure that Control(s) are populated, there. Tying this type of validation code to a Control, itself, will fail by the user simply never entering the Control.

And since the Form_BeforeUpdate event is the last event before a Record is saved...if something is wrong, such as one or more Controls are empty...you need to Cancel the saving of the Record...hence the

Cancel = True

If you look at the Sub Header for the Form_BeforeUpdate event, you'll see

Sub Form_BeforeUpdate(Cancel As Integer)

with the Parameter Cancel As Integer which means that this event can be canceled. But you cannot do this for an OnClick event...if you look at that Sub Header, you'll see

Sub WhateverButtonName_Click()

with no Parameter.

And while it says Cancel As Integer...in VBA, True actually evaluates as any Integer other than 0.

Hope this helps!

Linq ;0)>