Bound form behavior. Form_BeforeInsert()

[vitamin]

Hey guys! I'm new here, and just started a new job about 3 weeks ago. Turns out that they want me to do some Access database design and creation. I've used it before to a certain extent, but am having some troubles with a form I have bound to a table. The form is below.



Driver, Start Miles, End Miles, and Load Count are all required fields.


I have a switchboard on load and they can pull up this same form in either "Entry Mode" or "Edit Mode". In entry mode I'm having a problem. When the form loads all fields are blank besides the Shift Date field (which auto fills to the date today). Everything works fine, but I'm trying to catch the event if say the user selects a driver from the drop down then makes the box empty again and tries to exit/save/new record with no fields filled in. Access tries to save the record, but is unable to because the required fields are all still null. I'm trying to figure out where to validate the data. (I'm hoping this is making sense).


The following is triggered right when there is a change anywhere on the form and is never triggered again. So I'm assuming that access has already began to insert a record right when a change is detected? This is where my problem is because if the user decides they no longer want to insert a record and tries to exit after editing a field and deleting, it won't let them.

Private Sub Form_BeforeInsert(Cancel As Integer)


End Sub

I also tried this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If (Me.Dirty = True) Then
        If (IsNull(Me!DriverID.Value) And IsNull(Me!TruckID.Value) And IsNull(Me!StartMiles.Value) And IsNull(Me!EndMiles.Value) And IsNull(Me!TrailerID.Value) And _
            IsNull(Me!StartHubMeter.Value) And IsNull(Me!EndHubMeter.Value) And IsNull(Me!loadcount.Value) And IsNull(Me!Notes.Value)) Then
            'Ended up not doing this because it gave some an error saying something like insert has began already and can't change the state.  Just wanted to show you that I tried this
            'Me.Dirty = False
        End If
    End If
    If (IsNull(Me!DriverID.Value) Or IsNull(Me!StartMiles.Value) Or IsNull(Me!EndMiles.Value)) Then
        MsgBox ("Driver, Start Miles, and End Miles are all required fields")
        Cancel = True
    End If
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2169 Then
        Response = True
    End If
End Sub

This almost works, but ends up using an AutoNumber. If I go through the steps that I explained earlier (Edit driver, delete, and then try and exit) it will exit without an error, but if I reopen the form and add a real record, the next record added will be +1 AutoNumber in the table. Is this behavior intended to work like this? Also, if I do this and say I have the driver selected, but no start or end miles entered and I hit the close button, I would like the close action to stop and ask the user if they would like to continue to fill out the form. Right now it just exits. How would I go about doing that.

I guess the main question I have, when a change in the form is detected on a bound form is a record already created? Is there a way to not have the insert run until either save/new/exit is hit? Or is that just how bound forms work and if I want to not skip an AutoNumber I must create an unbound form?

Sorry for long post. Hopefully I can get a few of my questions answered. Thanks!

[John_G]

That is how autonumbers work - they are assigned as soon as record is inserted (when any character is entered, essentially), and they cannot be reused.

I not having gaps in the number sequence is important to your application, then you should not use autonumber and instead generate the number yourself. It's not difficult - just use the current maximum plus 1.

This gets a bit more complicated if multiple users are adding to the same table (two users could get the same number), but there are ways around it with a little VBA.

[vitamin]

I guess I was always under the impression that skipping AutoNumbers was bad practice. I guess I never gave it much thought, but in all honesty it is not a big deal if I have gaps in the number sequence. So i guess the behavior I'm seeing is expected.

Thanks for the information. Greatly appreciate it!