null primarykey

tselie115

Registered User.
Local time
Yesterday, 17:17
Joined
Aug 10, 2008
Messages
44
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 with a Jet backend, the autonumber is assigned as soon as the first character is typed into the form. With a server backend, the identity column is not assigned until the record is saved. I can infer from your post that you are not enforcing referential integrity which you should be. You should NOT be able to save subform records prior to saving the main form record.

To solve your problem you must save the mainform record prior to inserting rows in the subform. You can add something like the following at the beginning of your posted code.

If me.dirty then
docmd.runcommand accmdsaverecord
end if

The best solution is to not insert the subform records until after the mainform record has been saved correctly. In that case, you would move all your code (you won't need the dirty test) to the Form's AfterUpdate event. If you have a condition that the insert depends on, use a form level variable that you set in the type control's afterupdate event and check and reset in the form's after update event.
 
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
 

Users who are viewing this thread

Back
Top Bottom