Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a frmReservationEntry whose data source is qryCustomerSorted. Among the fields on the form are CustomerStatus, CustomerID, ReservationNumber (alphanumeric field), ResBegDate, ResEndDate. There are 7 CustomerStatus codes.
    When entering data, I want to perform some validation tests.
    1. If CustomerStatus is any of the 1st five fields, I want to prevent any entry into the ReservationNumber, ResBegDate, and ResEndDate fields
    2. If CustomerStatus is 6, I want to insure that the ReservationNumber is 11 characters long.
    3. If CustomerStatus is 7, I want to make ReservationNumber equal to the CustomerID number
    4. If ReservationNumber is populated, then dates must be entered into ResBegDate and ResEndDate fields

    Any suggestions as to the best place to perform these tests and the syntax would be greatly appreciated.

    Tom

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='TomGoodwin' post='783360' date='07-Jul-2009 15:25']I have a frmReservationEntry whose data source is qryCustomerSorted. Among the fields on the form are CustomerStatus, CustomerID, ReservationNumber (alphanumeric field), ResBegDate, ResEndDate. There are 7 CustomerStatus codes.
    When entering data, I want to perform some validation tests.
    1. If CustomerStatus is any of the 1st five fields, I want to prevent any entry into the ReservationNumber, ResBegDate, and ResEndDate fields
    2. If CustomerStatus is 6, I want to insure that the ReservationNumber is 11 characters long.
    3. If CustomerStatus is 7, I want to make ReservationNumber equal to the CustomerID number
    4. If ReservationNumber is populated, then dates must be entered into ResBegDate and ResEndDate fields

    Any suggestions as to the best place to perform these tests and the syntax would be greatly appreciated.

    Tom[/quote]

    Tom,

    1. After Update event of CustomerStatus control. Syntax should be very simple, to disable the other fields. You may also need an On Current event.
    2. Before Update event of ReservationNumber control. If cmbCustomerStatus = 6 then.......If nz(LEN(txtReservationNumber))<>11 then...
    3. As 1.
    4. Form's Before Update event is probably the best place.

    Regards,

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Part of it can be done in the After Update event of CustomerStatus:

    Code:
    Private Sub CustomerStatus_AfterUpdate()
      Select Case Me.CustomerStatus
    	Case 1 To 5
    	  Me.ReservationNumber = Null
    	  Me.ResBegDate = Null
    	  Me.ResEndDate = Null
    	  Me.ReservationNumber.Enabled = False
    	  Me.ResBegDate.Enabled = False
    	  Me.ResEndDate.Enabled = False
    	Case 7
    	  Me.ReservationNumber.Enabled = True
    	  Me.ResBegDate.Enabled = True
    	  Me.ResEndDate.Enabled = True
    	  Me.ReservationNumber = Me.CustomerID
    	Case Else
    	  Me.ReservationNumber.Enabled = True
    	  Me.ResBegDate.Enabled = True
    	  Me.ResEndDate.Enabled = True
      End Select
    End Sub
    You also need code in the On Current event of the form:

    Code:
    Private Sub Form_Current()
      Select Case Me.CustomerStatus
    	Case 1 To 5
    	  Me.ReservationNumber.Enabled = False
    	  Me.ResBegDate.Enabled = False
    	  Me.ResEndDate.Enabled = False
    	Case Else
    	  Me.ReservationNumber.Enabled = True
    	  Me.ResBegDate.Enabled = True
    	  Me.ResEndDate.Enabled = True
      End Select
    End Sub
    For the rest of the checks, you can use the Before Update event of the form:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      If Me.CustomerStatus = 6 And Not Len(Me.ReservationNumber) = 11 Then
    	Me.ReservationNumber.SetFocus
    	MsgBox "Reservation number should be 11 characters long", vbExclamation
    	Cancel = True
    	Exit Sub
      End If
      If Not IsNull(Me.ReservationNumber) Then
    	If IsNull(Me.ResBegDate) Then
    	  Me.ResBegDate.SetFocus
    	  MsgBox "Please enter a beginning date", vbExclamation
    	  Cancel = True
    	  Exit Sub
    	End If
    	If IsNull(Me.ResEndDate) Then
    	  Me.ResEndDate.SetFocus
    	  MsgBox "Please enter an end date", vbExclamation
    	  Cancel = True
    	  Exit Sub
    	End If
      End If
    End Sub

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks. The code works perfectly.

    I have added 2 more fields. Can you make some fields mandatory once the are enabled. In the case of Case 6, the original 3 fields plus the 2 new fields would be mandatory. In the case of Case 7, only the original 3 fields would be mandatory.

    Tom

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd add code to the Before Update event of the form:

    Code:
    If Me.CustomerStatus = 6 Then
      If IsNull(Me.NewField1) Then
    	Me.NewField1.SetFocus
    	MsgBox "Please enter a value for NewField1", vbExclamation
    	Cancel = True
    	Exit Sub
      End If
      If IsNull(Me.NewField2) Then
    	Me.NewField2.SetFocus
    	MsgBox "Please enter a value for NewField2", vbExclamation
    	Cancel = True
    	Exit Sub
      End If
    End If

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. I see now that the mandatory tests were in the Before Update event in your original code for some fields. Thanks for the additional lesson.

    Tom

    [quote name='HansV' post='783589' date='08-Jul-2009 13:33']I'd add code to the Before Update event of the form:

    Code:
    If Me.CustomerStatus = 6 Then
      If IsNull(Me.NewField1) Then
    	Me.NewField1.SetFocus
    	MsgBox "Please enter a value for NewField1", vbExclamation
    	Cancel = True
    	Exit Sub
      End If
      If IsNull(Me.NewField2) Then
    	Me.NewField2.SetFocus
    	MsgBox "Please enter a value for NewField2", vbExclamation
    	Cancel = True
    	Exit Sub
      End If
    End If
    [/quote]

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I try to close the form without entering data in either of the two new fields, I get the error message to enter the correct data. When I click OK, I get another error message: You can’t save this record at this time… if you close now data will be lost. Do you want to close anyway? If you answer “No”, the error message disappears. The focus is on the field with the missing data. If correct data is entered, the form closes correctly.



    Private Sub Form_BeforeUpdate(Cancel As Integer)
    'The BeforeUpdate event allows you to test to see
    'if a record exists before inserting it a second time.
    If Me.NewRecord = True Then
    Dim strWhere As String, strMessage As String
    strWhere = "LastName = " & Chr(34) & Me!LastName & Chr(34)
    Me.RecordsetClone.MoveFirst
    Me.RecordsetClone.FindFirst strWhere
    Do Until Me.RecordsetClone.NoMatch
    If Me.RecordsetClone!FirstName = Me!FirstName Then
    strMessage = "This guest is already in the database. "
    strMessage = strMessage & " Please verify the information being entered."
    strMessage = strMessage & " Click OK and the original record will be displayed."
    MsgBox strMessage, vbInformation, "Matching Record"
    Cancel = True
    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Exit Sub
    End If
    Me.RecordsetClone.FindNext strWhere
    Loop
    End If

    If Me.CustomerStatusID = 6 And Not Len(Me.ReservationNumber) = 11 Then
    Me.ReservationNumber.SetFocus
    MsgBox "Reservation number must be 11 characters long", vbExclamation
    Cancel = True
    Exit Sub
    End If
    If Not IsNull(Me.ReservationNumber) Then
    If IsNull(Me.ResBegDate) Then
    Me.ResBegDate.SetFocus
    MsgBox "Please enter a begining date", vbExclamation
    Cancel = True
    Exit Sub
    End If
    If IsNull(Me.ResEndDate) Then
    Me.ResEndDate.SetFocus
    MsgBox "Please enter an end date", vbExclamation
    Cancel = True
    Exit Sub
    End If
    End If
    If Me.CustomerStatusID = 6 Then
    If IsNull(Me.IATANumber) Then
    Me.IATANumber.SetFocus
    MsgBox "Please enter a valid IATA Number", vbExclamation
    Cancel = True
    Exit Sub
    End If
    If IsNull(Me.ResID) Then
    Me.ResID.SetFocus
    MsgBox "Please select a Reservation Source from the pull down menu", vbExclamation
    Cancel = True
    Exit Sub
    End If
    End If


    End Sub




    [quote name='TomGoodwin' post='783595' date='08-Jul-2009 13:46']Thanks. I see now that the mandatory tests were in the Before Update event in your original code for some fields. Thanks for the additional lesson.

    Tom[/quote]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should disable closing the form through the x in the upper right corner, and only allow the user to close the form if the current record has been saved.

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have added a Close form button to the form. I also changed the form property "controlbox" to No. Is the most effective way to disabled closing the form through the x in the upper right hand corner?

    Tom





    [quote name='HansV' post='783649' date='08-Jul-2009 16:57']You should disable closing the form through the x in the upper right corner, and only allow the user to close the form if the current record has been saved.[/quote]

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Setting the Control Box property to No is a good idea.

    For extra security you can add a variable at the top of the form module:

    Private fClose As Boolean

    In the On Click code of the command button:

    Code:
    Private Sub cmdClose_Click()
      fClose = True
      DoCmd.Close acForm, Me.Name
    End Sub
    and in the On Unload event of the form:

    Code:
    Private Sub Form_Unload(Cancel As Integer)
      Cancel = Not fClose
    End Sub
    fClose will initially be False, so even if the user presses Ctrl+F4, the form will not close. When the user clicks the command button, the form will close.

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have not tried using the variable yet but have the following code on the close button

    Private Sub Close_Form_Click()
    On Error GoTo Err_Close_Form_Click


    DoCmd.Close acForm, "frmReservationEntry", acSaveYes

    Exit_Close_Form_Click:
    Exit Sub

    Err_Close_Form_Click:
    MsgBox Err.Description
    Resume Exit_Close_Form_Click

    End Sub


    I am still having the problem that I get the correct error message but when I click OK, the form closes without giving me the opportunity to correct the data entry.

    Tom



    [quote name='HansV' post='783782' date='09-Jul-2009 10:50']Setting the Control Box property to No is a good idea.

    For extra security you can add a variable at the top of the form module:

    Private fClose As Boolean

    In the On Click code of the command button:

    Code:
    Private Sub cmdClose_Click()
      fClose = True
      DoCmd.Close acForm, Me.Name
    End Sub
    and in the On Unload event of the form:

    Code:
    Private Sub Form_Unload(Cancel As Integer)
      Cancel = Not fClose
    End Sub
    fClose will initially be False, so even if the user presses Ctrl+F4, the form will not close. When the user clicks the command button, the form will close.[/quote]

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Code:
    Private Sub Close_Form_Click()
      If Me.Dirty Then
    	MsgBox "Please save the record before closing the form.", vbExclamation
    	Exit Sub
      Else
    	DoCmd.Close acForm, "frmReservationEntry"
      End If
    End Sub

  13. #13
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think the If me.Dirty check to see if the information has been changed but not saved. The code lets me correct the data but then stops. Do I need to have a separate button to save the data? I take it that it can't be done with just the close button.

    Tom


    [quote name='HansV' post='784139' date='11-Jul-2009 11:30']Try this:

    Code:
    Private Sub Close_Form_Click()
      If Me.Dirty Then
    	MsgBox "Please save the record before closing the form.", vbExclamation
    	Exit Sub
      Else
    	DoCmd.Close acForm, "frmReservationEntry"
      End If
    End Sub
    [/quote]

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, Me.Dirty is True if the current record has unsaved changes.

    Instead of checking for Me.Dirty, you could repeat the tests from the Form_BeforeUpdate event in the Close_Form_Click event procedure. If a test fails, use Exit Sub instead of Cancel = True (you can't use Cancel here, because Close_Form_Click doesn't have a Cancel argument.)
    If the record passes all tests, it will be saved automatically when the form is closed.

  15. #15
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't see any change in the second On Click code.

    Tom

    [quote name='TomGoodwin' post='784142' date='11-Jul-2009 12:01']I think the If me.Dirty check to see if the information has been changed but not saved. The code lets me correct the data but then stops. Do I need to have a separate button to save the data? I take it that it can't be done with just the close button.

    Tom[/quote]

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •