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
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