Force Insert to Obtain Autonumber 2

[Genimuse]

I've searched quite a bit on this but have had no luck.

I have a bound form with a lot of unbound data on it. The unbound data relies on the bound form's primary key, an autonumber. When working with existing records, it's peachy, but when working with a new record, since the bound form data hasn't inserted yet, all of my code that relies on that autonumber is out of luck.

I know that I can stop using autonumbered fields and generate my key manually (there's a good FAQ here on it), but other than that, is there any way for me to force an insert/save before the user changes records (or whenever it occurs). I've tried docmd.RunCommand acCmdSave, but (as I expected) it doesn't do the trick.

How can I force that insert to fire?

 

[payback]

If you are using Access to Access data (not Access to SQL Server), the autonumber will create itself as soon as something is entered into a bound field.

You can check this by displaying (while testing) the autonumber field on your form.

 

[Eupher]

Hi, Genimuse,

DoCmd.RunCommand acCmdSaveRecord

HTH,

Ken S.

 

[Eupher]

p.s. but I don't think it will work if there is no data in any fields...

 

[Eupher]

p.p.s. However, as payback said, the autonumber is generated as soon as any data is entered in a field. So you could put data in a field with code to force the autonumber to generate, then delete the data and do your RunCommand (if all you want is a blank record with an autonumber).

Ken S.

 

[Eupher]

p.p.p.s. This is what I call "stream of consciousness" posting... ;-) Just tested it. The dummy data can even be vbNullString, so you don't even need to delete it before saving the record and autonumber will be generated.

Ken S.

 

[Genimuse]

Ok, setting a "background" field (CreatedBy, in this case) if the ID is null, and then running the adCmdSaveRecord did the trick, thanks!

One more question, then: is there some way to stop an insert on Before Insert? If I find that it's all empty (the user didn't enter anything, maybe tracked on Dirty), I'd like for it to be not inserted (and I know that After Insert doesn't fire just because the autonumber was generated, strangely enough).

 

[PHV]

Doesn't have the BeforeInsert event procedure a parameter called Cancel you can set to True ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Genimuse]

It does, but (and I apologize for this ignorance), I don't know how to access it.

I mean, it's clear that this doesn't work:

If IsNull(FirstName) And IsNull(LastName) Then
    Cancel = True
End If

The Sub indicates that it's an integer, but I can't seem to find (in the help) what the options might be.

 

[PHV]

Private Sub Form_BeforeInsert(Cancel As Integer)
If MsgBox("Insert new record here?", _
vbOKCancel) = vbCancel Then
Cancel = True
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Genimuse]

Unfortunately, this doesn't work. It appears that BeforeInsert isn't even firing, since I don't get a messagebox.

I'm setting the value and doing the save in Current, mind you... yet since it's saving it, the insert must take place after the Current.

To sum, I have a "new record" button that looks like this (error checking and such removed):

Private Sub cmdNewDonor_Click()
    DoCmd.GoToRecord , , acNewRec
End Sub

and in my Current I have:

Private Sub Form_Current()
    If IsNull(ID) Then
        Me!CreatedBy = Environ("username")
        DoCmd.RunCommand acCmdSaveRecord
    End If
End Sub

and in my BeforeInsert I have:

Private Sub Form_BeforeInsert(Cancel As Integer)
    If MsgBox("Insert new record here?", _
        vbOKCancel) = vbCancel Then
        Cancel = True
    End If
End Sub

I click the button, it's clear that it saved (the ID field displayed on the form changes from "(autonumber)" to a number, or from a previous number to a new one), yet the BeforeInsert is clearly not firing. 5 clicks of the button becomes 5 new blank records in my table.

Why is Cancel an integer instead of a boolean, or am I reading too much into that?

(And thanks, btw!)

 

[PHV]

The BeforeInsert event is raised with keystroke only.
You may try the BeforeUpdate event procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886