Results 1 to 5 of 5
  1. #1
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142

    If loop statement

    Hello Everyone
    I am trying to do a if statement in a loop.

    Below is the code.


    Code:
    Private Sub Monday_Click()
    Dim LResponse As Integer
    Dim a As Integer
    For a = 1 To 5
    If Text284 = DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "#") Then
    LResponse = MsgBox("Please note this day is a holiday. Do you still want to charge on this day? ", vbYesNo, "CONTINUE")
    If LResponse = vbYes Then
    Me.Monday.SetFocus
    Else
    Me.Monday.Locked = True
    End If
       End If
       Next a
    End Sub
    I am trying to restrict "Monday" text box depending on the value of the "Text284" textbox.
    If the user clicks yes, "Monday" textbox is focused and it is editable.
    If no, its locked.


    and this goes on a loop. If the user clicks the "Monday" text box again.
    I am not sure how to change the code to make it work.
    Can anyone help me with this.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the purpose of the loop? You're going to keep asking the question 5 times. You'd want to unlock it when setting focus to it, as it could be locked from a previous record/attempt.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This kind of looks like the same problem here
    https://www.accessforums.net/showthread.php?t=73679

  4. #4
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142
    I tried the below code: And its working
    I was trying to do this.
    Whenever the user changes the value greater than 0 in the text box . A warning message should prompt.
    And also the text box had another criteria to lookup value from another table.
    I figured out a loop is not required when I read your message. hence I used the code in the after update event.
    Thank you so much for taking your time to look into this.


    Code:
    Private Sub Monday_AfterUpdate()
    Dim LResponse As Integer
     
    If Monday.Value > 0 And Text284 = DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "#") Then
    LResponse = MsgBox("Please note this day is a holiday. Do you still want to charge on this day? ", vbYesNo, "CONTINUE")
    If LResponse = vbYes Then
    Me.Monday.SetFocus
    Else
    Me.Monday.Value = 0
    End If
       End If
    End Sub


    Quote Originally Posted by pbaldy View Post
    What is the purpose of the loop? You're going to keep asking the question 5 times. You'd want to unlock it when setting focus to it, as it could be locked from a previous record/attempt.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  2. Replies: 2
    Last Post: 10-10-2012, 02:51 PM
  3. Replies: 4
    Last Post: 10-12-2010, 02:11 PM
  4. For Each LOOP statement
    By zambam737 in forum Programming
    Replies: 3
    Last Post: 10-26-2009, 09:59 PM
  5. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 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