Problem with Form_AfterInsert()

[jdegeorge]

Hi:

I have a data entry form with several fields on it. Some fields have default values and can be edited if necessary, while others start out empty and require input.

A user can tab through the fields and enter information as necessary. When done with entering a record, the user has the option of tabbing through the last field to get a new record or clicking the CLOSE button to close the form.

If the user tabs through to add other records, the Form_AfterInsert() code ALWAYS runs. But, if the user doesn't want to enter more records, the CLOSE button is clicked and the Form_AfterInsert() code doesn't run ON ONLY THE LAST RECORD.

The code behind the CLOSE button is:

DoCmd.Close acForm, Me.NAME, acSaveYes
[Forms]![frmGapMenu].Visible = True

Shouldn't this be enough to INSERT the last inputted record into the table and for the Form_AfterInsert() code to run?

I'm very puzzled and frustrated by this one. Please help!

Jim DeGeorge

 

[LambertH]

I just tried your code on a simple form and it DOES run the AfterInsert event. ????

It may be time for you to backup the database first (very important) and then decompile the database.

"D:\PathTo MsAcces\MsAccess.exe /decompile "D:\Path To Db\YourDb.mdb"

 

[jdegeorge]

LambertH

The code I described was behind the CLOSE button...it wasn't the AfterInsert code. Did you mock up some AfterInsert code and a CLOSE button?

Anywho, I've never decompiled before, so do I do this from the DOS prompt or the START/RUN window?

Jim DeGeorge

 

[jdegeorge]

Lamberth

I made a backup copy and decompiled the database. What is decompile supposed to do?

Jim DeGeorge

 

[jdegeorge]

LambertH

Decompiling didn't help any.

Upon looking at this further, it turns out that SOME of the AfterInsert code runs when the CLOSE button is clicked but it ALL runs when the user tabs through to a new record.

Here's the AfterInsert code:

[tt] ************ THE PART THAT ONLY RUNS WHEN TABBING THROUGH! ***********
'Assignment of the GAP#

Dim Db As DAO.Database, rs As DAO.Recordset, vPrefix As Variant, vNextNum As Variant

vPrefix = DLookup("[Prefix]", "[tblConversion]", "[Conversion] = [Forms]![frmSignOn]![ctlConversion]&quot
vNextNum = DLookup("[NextNumber]", "[tblConversion]", "[Conversion] = [Forms]![frmSignOn]![ctlConversion]&quot

Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblConversion", dbOpenDynaset)

Me![ctlTempID#].SetFocus
Me![ctlTempID#] = vPrefix & "-" & Format(vNextNum, "000&quot

If IsNull(Me![ctlGap#]) Then
Me![ctlGap#].SetFocus
Me![ctlGap#] = Me![ctlTempID#]
Me![ctlGap_Status].SetFocus
SendKeys "{TAB}", False
End If


************ THE PART THAT ALWAYS RUNS! ************
'Commit Gap# to record and update that Conversion's NEXT AVAILABLE GAP NUMBER

Dim db1 As DAO.Database, rs1 As DAO.Recordset, strCriteria As Variant, zPrefix As Variant

Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("tblConversion", dbOpenDynaset)

zPrefix = Left(Me![ctlGap#], (Len(Me![ctlGap#]) - 4))
strCriteria = "[Prefix] = '" & zPrefix & "'"

If Me![ctlTempID#] = Me![ctlGap#] Then
With rs1
.FindFirst strCriteria
.Edit
.Fields("NextNumber&quot = .Fields("NextNumber&quot + 1
.Update
End With
End If [/tt][/tt]

Why doesn't it always work?

Jim DeGeorge

 

[jdegeorge]

I included some error trapping by displaying the value of ctlTempID and ctlGap# from both sections of this code.

Whether I'm tabbing through to the next record or if I closed the form with the CLOSE button, the values are always the same. So, why doesn't this work.

What I've determined is that this is the only part of the code that doesn't seem to work when the CLOSE button is clicked:

[tt] If IsNull(Me![ctlGap#]) Then
Me![ctlGap#].SetFocus
Me![ctlGap#] = Me![ctlTempID#]
Me![ctlGap_Status].SetFocus
SendKeys "{TAB}", False
End If
[/tt]
Me![ctlGap#] is definitely NULL at this point, so why doesn't it work?

Here's the code behind the CLOSE button:

[tt]DoCmd.Close acForm, Me.NAME, acSaveYes
[Forms]![frmGapMenu].Visible = True[/tt]

Is there something about this code that prevents this IF...THEN from running? I can't understand this one for the life of me.

Jim DeGeorge

 

[jdegeorge]

Hi:

I just modifed this part of the code:

[tt]If IsNull(Me![ctlGap#]) Then
Me![ctlGap#].SetFocus
Me![ctlGap#] = Me![ctlTempID#]
Me![ctlGap_Status].SetFocus
SendKeys "{TAB}", False
End If[/tt]

to

[tt]If IsNull(Me![ctlGap#]) Then
Dim rst As DAO.Recordset
Dim strCrit As String
strCrit = "[Description] = '" & Me![ctlGapDescription] & "' AND [Conversion] = '" & Me![ctlConversion] & "'"
Set rst = Me.RecordsetClone
With rst
.FindFirst strCrit
.Edit
.Fields("Gap#&quot = Me![ctlTempID#]
.Update
End With
Set rst = Nothing
Me![ctlGap_Status].SetFocus
SendKeys "{TAB}", False
End If[/tt]

and it's working! Thanks everyone for taking a look at this.

It's times like this when I wish I could give myself a star! It's always rewarding to solve your own problems.


Jim DeGeorge

 

[LambertH]

Jim:

Great that you got it all working.

Decompiling:

In an Access mdb you have the VBA code in text form, and it is compiled into a machine form. As you make changes to your code and recompile, sometimes old bits of compiled code are left "hanging around". This sometimes causes perfectly legal VBA code to behave oddly, and the process of decompiling removes all of the compiled, machoine readabel code, so that the next time you compile you get a fresh set of machine instuctions.

http://www.mvps.org/access/

(The Access Web) is a good place to read about this. For example

http://www.mvps.org/access/bugs/bugs0008.htm

Your Code:

???? I don't really have an answer to why your original code was only running partly when the Close button was hit. I suspect it might have somthing to do with the form having been deactivated, and so the contrl references would not be returning any value, but that's just a guess.

 

[jdegeorge]

Yes...form deactiviation, etc. was the idea path I finally travelled down and that's why I update the table directly rather than the form's control fields. It's working beautifully.

Thanks for all the time you took to help me with this.

Jim DeGeorge