Results 1 to 5 of 5
  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    vba if statement error

    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

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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.

    Code:
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks missinglinq, what is the cancel function you added?

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-22-2014, 08:54 AM
  2. Error 3144: Syntax error in UPDATE statement??
    By Paintballlovr in forum Programming
    Replies: 7
    Last Post: 03-26-2014, 12:53 PM
  3. Error FROM clause of your SQL statement
    By Stephanie53 in forum Forms
    Replies: 8
    Last Post: 05-15-2013, 04:04 PM
  4. Compile Error: Syntax Error in DoCmd.RunSQL Statement
    By Evilferret in forum Programming
    Replies: 1
    Last Post: 08-27-2012, 12:32 PM
  5. Replies: 7
    Last Post: 08-17-2011, 01:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums