Working code, but some loop help needed!

  • Thread starter Thread starter fdco
  • Start date Start date
F

fdco

Guest
I got this code working (thanks to all who helped) and now I must
add two things to it. 1) a do loop or for each..next statement.
(I don't know which one is better to use, or where to place it)
as well as something that will check a box on the first recordset
after these records are posted. (Maybe a query would be best, but
if I CAN do it in VB I would be more proud of myself)!
smile.gif

Thanks
Mike
here is da code.

Dim wsp As Workspace
Dim db As Database
Dim rst As Recordset, rst2 As Recordset, rst3 As Recordset
Dim strname As String, strmessage As String, strprompt As String
Dim fintrans As Boolean


fintrans = False
strprompt = "Add these completed PO's to Orders"
Set wsp = DBEngine.Workspaces(0)
Set db = CurrentDb
Set rst = db.OpenRecordset("OIP", dbOpenDynaset)
Set rst2 = db.OpenRecordset("Orders", dbOpenDynaset)
Set rst3 = db.OpenRecordset("Purchase Order Details", dbOpenDynaset)

'start trans
wsp.BeginTrans
fintrans = True

rst.FindFirst ("ProductID = " & rst3!ProductID & "AND CustomerID = " & rst2!CustomerID & rst3!IP = "0" & rst2!OrderDate <= Me.CutoffDate)

If rst.NoMatch Then 'if it doesn't exist create it
With rst
.AddNew
!ProductID = rst3!ProductID
!CustomerID = rst2!CustomerID
!StyleCode = rst3!StyleCode
!Fabric = rst3!Fabric
!Colour = rst3!Colour
!XS = rst3!XS
!Small = rst3!Small
!Medium = rst3!Medium
!Large = rst3!Large
!XL = rst3!XL
!XXL = rst3!XXL
!OS = rst3!OS
!Unit = rst3!Unit
!UnitPrice = rst3!UnitPrice
!Notes = rst3!Notes
.Update
End With
Else
rst.Edit
rst!XS = rst!XS + rst3!XS
rst!Small = rst!Small + rst3!Small
rst!Medium = rst!Medium + rst3!Medium
rst!Large = rst!Large + rst3!Large
rst!XL = rst!XL + rst3!XL
rst!XXL = rst!XXL + rst3!XXL
rst!OS = rst!OS + rst3!OS
rst!Unit = rst!Unit + rst3!Unit
rst.Update
End If

If MsgBox("Save all changes", vbQuestion + vbYesNo, " Save Changes") = vbYes Then
wsp.CommitTrans 'commit changes
Else
wsp.Rollback 'rollback changes
End If
POupdExit:
rst.Close
rst2.Close
rst3.Close
Set db = Nothing
Set wsp = Nothing
Exit Sub
poupderr:
MsgBox "Problemo! Error number:" & Err.Number & vbCrLf & "Error desc.:" & Err.Description
If fintrans Then
wsp.Rollback 'undo
End If
Resume POupdExit
 

Users who are viewing this thread

Back
Top Bottom