Results 1 to 9 of 9
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    VBA If getting error because I know it is wrong

    I have



    Code:
    If Me.frmSubBookingsControl.Form!StatusID <> 5 Then
    DoCmd.Close
    End If
    If Me.frmSubBookingsControl.Form!StatusID = 5 And Not IsNull(Me.frmSubBookingsControl.Form!CancellationDate) And IsNull(Me.frmSubBookingsControl.Form!CancellationReasonID) And IsNull(Me.frmSubBookingsControl.Form!CancellationContact) Then
    MsgBox "Please fill all the fields for the cancellation details"
    End If
    If Me.frmSubBookingsControl.Form!StatusID = 5 And IsNull(Me.frmSubBookingsControl.Form!CancellationReasonID) Then
    MsgBox "Please fill all the fields for the cancellation details"
    End If
    If Me.frmSubBookingsControl.Form!StatusID = 5 And IsNull(Me.frmSubBookingsControl.Form!CancellationContact) Then
    MsgBox "Please fill all the fields for the cancellation details"
    End If
    If Me.frmSubBookingsControl.Form!StatusID = 5 And Not IsNull(Me.frmSubBookingsControl.Form!CancellationDate) And Not IsNull(Me.frmSubBookingsControl.Form!CancellationReasonID) And Not IsNull(Me.frmSubBookingsControl.Form!CancellationContact) Then
    DoCmd.Close
    End If
    How do I write this correctly? I know I can't have two docmd.close currently

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Because of the first If Then statement your code was always going to close the form. Maybe something like

    Code:
    If Me.frmSubBookingsControl.Form!StatusID = 5 And Not IsNull(Me.frmSubBookingsControl.Form!CancellationDate) And Not IsNull(Me.frmSubBookingsControl.Form!CancellationReasonID) And Not IsNull(Me.frmSubBookingsControl.Form!CancellationContact) Then
    DoCmd.Close
    Exit Sub
    Else
    MsgBox "Please fill all the fields for the cancellation details"
    End If

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why is it wrong? What happens?

    The message doesn't specify which field(s) need data so all the If Then conditions are superfluous. Maybe:

    If Me.frmSubBookingsControl.Form!StatusID = 5 And Not IsNull(Me.frmSubBookingsControl.Form!CancellationD ate) And Not IsNull(Me.frmSubBookingsControl.Form!CancellationR easonID) And Not IsNull(Me.frmSubBookingsControl.Form!CancellationC ontact) Then
    DoCmd.Close
    Else
    MsgBox "Please fill all the fields for the cancellation details"
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Well I get a can't find error when the form is closed because I guess the form closes before the command can follow through to the next therefore have it missing.

    Remember I need the if <> 5 to work too...

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Even though form closes, the code continues to execute unless use an Exit Sub.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please describe WHAT you are trying to do with some sample data. You are showing us HOW you did something, and we are left guessing your intentions.
    It's easy to use the wrong/inappropriate function or make a syntax error, but difficult to divine the underlying concept from code that you know has errors.

    Good luck with your project

  7. #7
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by orange View Post
    Please describe WHAT you are trying to do with some sample data. You are showing us HOW you did something, and we are left guessing your intentions.
    It's easy to use the wrong/inappropriate function or make a syntax error, but difficult to divine the underlying concept from code that you know has errors.

    Good luck with your project
    It's a booking form.

    I have a close form button that disappears based on the current status. If the status (StatusID) combo box is changed to 5 it becomes "cancelled" however, all the associated fields have to be filled.

    If the staff place the status to 5 but don't fill in the fields, the close form button disappears until all the fields are completed.

    Should the staff change the status while there is a date in the cancellation date field - then they also can't close the form until they have cleared the filled date field.

    There are a few conditions basically that the user has to adhere to before they can close the form.

    I made it so that the closed button appears or disappears from visibility based on my set criteria.

  8. #8
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Even though form closes, the code continues to execute unless use an Exit Sub.
    So just add Exit Sub to the code? I think that may solve the issue..

  9. #9
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Seems to have fixed it, I won't know until all the situations have been run through but looks to work thanks!

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

Similar Threads

  1. VBA gone wrong with an if
    By Ruegen in forum Programming
    Replies: 2
    Last Post: 10-21-2013, 05:38 PM
  2. Compile Error: Can't find out what's wrong with the code.
    By vickan240sx in forum Programming
    Replies: 4
    Last Post: 05-24-2012, 06:25 PM
  3. What's wrong!
    By khalid in forum Programming
    Replies: 15
    Last Post: 06-27-2011, 06:38 AM
  4. What is wrong with this IFF?
    By bburton in forum Reports
    Replies: 2
    Last Post: 03-16-2011, 10:42 AM
  5. What am I doing wrong?
    By brandon in forum Access
    Replies: 2
    Last Post: 08-03-2008, 10:26 AM

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