null primarykey

[tselie115]

im using the following code to create new records in a subform on the afterupdate even of a combobox in the mainform. the main form has a primary key ProductID.

the problem is that on the afterupdate event, the primary key in the main form is null which is resulting in a null "ProductID" in the subform(highlighted in red in code) therefore no link between form and subform.

Please note that i had no prob when ma database was in access but when i upsized to sql 2000 it happened.

Private Sub type_AfterUpdate()
Dim db As Database, tb As Recordset, rs As Recordset, ns As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("Select * from Materialtable1 where ID =" & Me.Type.Column(1), dbOpenDynaset, dbSeeChanges)
Set rs = db.OpenRecordset("Select * from InventoryTransactions", dbOpenDynaset, dbSeeChanges)

Do Until tb.EOF = True
With tb
rs.AddNew
rs!ProductID = ProductID
rs!material = tb!Material1
rs!Rate = tb!Rate1
rs!Unit = tb!Unit1
rs!MaterialID = tb!MaterialID
rs.Update
End With
tb.MoveNext
Loop

rs.Close
tb.Close
db.Close
Me.Refresh

End Sub

 

[tselie115]

thank you soo much man! it worked like silk! i really appreciate the help of the experts.
addind "docmd.runcommand accmdsaverecord" at the begining of the afterupdate event solved the problem.
take care God Bless