Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditionally Preventing Record Creation in a Form (2003)

    Private Sub Form_Load() warns user about the presence of gaps(missing days) in the GIORNO field of the Record Source for the Startup form of the attached mdb. I'd like though to prevent the end user from creating a new record if they select a day on the calendar which is not the day immediately following the last day in the Startup form's Record Source, so I've prepared the following procedure:

    <font color=red>Private Sub OREDE_BeforeUpdate(Cancel As Integer)
    Dim i As Date
    Set cnn = CurrentProject.Connection
    rst.Open "SELECT T_COMUNI.Giorno " & _
    "FROM T_COMUNI " & _
    "WHERE Year([giorno])= Year(#" & Format([Forms]![VilladiSerio]![CGior], "mm/dd/yyyy") & "#)", _
    cnn, adOpenKeyset, adLockOptimistic
    rst.MoveFirst
    i = rst!Giorno
    Do Until rst.EOF
    If Not rst!Giorno = i Then
    MsgBox "You haven't filled out " & i, , "Warning!"
    Cancel = True
    Me.CGior = i
    CGior_AfterUpdate
    Exit Sub
    End If
    i = i + 1
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub</font color=red>

    but the problem is the rst recordset opened in the BeforeUpdate procedure above still doesn't contain the possible "offending" day and so, if the user skips a day(e.g. by selecting April 17, 2005 on the calendar), the procedure ends without cancelling the insertion of the new record. How can I do it?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditionally Preventing Record Creation in a Form (2003)

    You must treat new records separately:

    Private Sub OREDE_BeforeUpdate(Cancel As Integer)
    Dim i As Date
    If Me.NewRecord Then
    i = DMax("Giorno", "T_Comuni") + 1
    If Me.CGior > i Then
    MsgBox "You haven't filled out " & i, , "Warning!"
    Cancel = True
    End If
    Else
    Set cnn = CurrentProject.Connection
    rst.Open "SELECT T_COMUNI.Giorno FROM T_COMUNI " & _
    "WHERE Year([giorno])= Year(#" & _
    Format([Forms]![VilladiSerio]![CGior], "mm/dd/yyyy") & "#)", _
    cnn, adOpenKeyset, adLockOptimistic
    rst.MoveFirst
    i = rst!Giorno
    Do Until rst.EOF
    If Not rst!Giorno = i Then
    MsgBox "You haven't filled out " & i, , "Warning!"
    Cancel = True
    Me.CGior = i
    CGior_AfterUpdate
    Exit Sub
    End If
    i = i + 1
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    End If

    Note 1: this only checks if the user enters something in OREDE. What if the user starts entering in another control?
    Note 2: the code only undoes the entry in ORE_DE, not the entire record.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditionally Preventing Record Creation in a Form (2003)

    Thank you Hans,
    Would you solve the problems outlined in the two notes at the bottom of your reply by putting code in the dirty event procedure for the startup form?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditionally Preventing Record Creation in a Form (2003)

    I would use the Before Insert event of the form and set its Cancel argument to True if CGior isn't correct. Or use a separate form to enter a new record.

Posting Permissions

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