'After Insert' action doesn't work in datasheet view

[dots]

I have a form for entering new records in a main table. If certain conditions are met, it creates a new record in another table (child). The second form is a simple one - it is just an auto-number field and another number field [Rail Entry] that references the record number of the first table. All worked fine until my boss decided he wanted the form to be in datasheet view. Now, it doesn't work at all. The code runs 'After Insert' and I believe this may be the problem, although I don't know how to correct it. Can anyone help?

Dim MyNum As Integer
MyNum = [Record]

If [Type] = "Chips" Then
If [Customer] = 2 Then

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.openrecordset("Domtar Tickets&quot

rst.AddNew
rst![Rail Entry] = MyNum
rst.Update
rst.Close

Set rst = Nothing
Set dbs = Nothing

End If
End If

 

[JPeters]

Is this code ran in the subForm or is it updating the sub from the main form by code? The reason I ask is that you didn't Dim your form.

Something in the main form like:
Dim Frm As Form
Set frm = Forms(frmName) ' change to your form's name

It looks like your code doesn't actual do anything within the form. It appears to be updating the subform's table with code through recordsets only, not through the form.

What you might want to do also (instead of what i've said above) is to check your table as you make changes to the form. See if the updates appear in your table, but not in your form. If for some reason they appear in one and not the other, go to your form (subform) and add this to your On Current event (or wherever you see fit to place it)

Me.Refresh ... or Me.Requery. I can never remember which one does what - so read up on them. This might help to refresh changed records. Goodluck... please reply if I'm a big bafoon and I don't offer any help.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[dots]

Hi Josh,

This code is run on the Main Form. It is supposed to execute on the After Insert Event in the form properties. You are correct - there is not a subform. It is entering a new record in the second table if the criteria is met.
It executed perfectly before we changed to datasheet view.

 

[JPeters]

Try after update, instead of after Insert.. that might work.. also maybe try:

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.openrecordset("Domtar Tickets", dbOpenDynaset)

This seems like one of those problems that just drives you crazy. If you aren't in form view, do Class Modules even run? I just re-read your code and it looks like it SHOULD WORK ... Let me do some thinking about it. Srange..

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------