SQL in module code

[peneirol]

Hi.

I have a table that keeps track of the last id number of other tables.
When in a another table form I do an insert I would like this table
(called IDTable) to be updated with the new id number.

How can I do this in a module so I can call the code from any of the
table forms in my database.

Best regards.
Peneirol

 

[cTaHk0]

Hi.

I have a table that keeps track of the last id number of other tables.
When in a another table form I do an insert I would like this table
(called IDTable) to be updated with the new id number.

How can I do this in a module so I can call the code from any of the
table forms in my database.

Best regards.
Peneirol

Okay, do this:
In one module copy this code

Public Function updt(tableName As String, idName As String, idValue As Long)
Dim strSql As String
strSql = "INSERT INTO IDTable (tblName, IDvalue) VALUES ('" & tableName
& "', " & idValue & ")"
'tblName and IDvalue rename it like in your IDtable
DoCmd.RunSQL strSql
End Function
And this one in every form you need (in After Insert event)

Private Sub Form_AfterInsert()

Call updt(Me.RecordSource, id.Name, id) 'id your current id
on each form

End Sub

Make one or more little correction if you wish and go play J


Regards,

cTaHk0