vba error use dbseechanges

tselie115

Registered User.
Local time
Today, 09:48
Joined
Aug 10, 2008
Messages
44
runtime error

hey guys im having this error on executing this code;



must use dbSeeChanges option with Recordset



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), dbSeeChanges)
Set rs = db.OpenRecordset("Select * from InventoryTransactions", dbSeeChangess)
Set ns = db.OpenRecordset("Select * from InventoryTransactions Where ProductID=" & Me.ProductID, dbSeeChanges)

Do Until ns.EOF = True
With ns
ns.Delete
ns.MoveNext
End With
Loop
ns.Close

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
Me.Requery

End Sub

wats the solution
 
Last edited:
tselie,

It might help to know what line threw the error, but ...

Set rs = db.OpenRecordset("Select * from InventoryTransactions", dbSeeChangess)

Wayne
 
thx guys i solved this problem by changing the code as follows:

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)
Set ns = db.OpenRecordset("Select * from InventoryTransactions where ProductID =" & Me.ProductID, dbOpenDynaset, dbSeeChanges)

Do Until ns.EOF = True
With ns
ns.Delete
ns.MoveNext
End With
Loop
ns.Close

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

but a new problem showed.
a runtime error '3075' syntax error(missing operator) in query 'productid=' (highlighted in red)
this error only occurs if there are no records in the table "invetory transactions" with the same product ID meaning when there is no record for ProductID =" & Me.ProductID.

thanks for your help.
 

Users who are viewing this thread

Back
Top Bottom