Can you tell me why this code is not working???
Private Sub cmdDoCost_Click()
Dim db As DAO.Database
Dim rstPO As DAO.Recordset, rstPORoducts As DAO.Recordset
Dim rstSToOrder As DAO.Recordset, qd As DAO.QueryDef
Dim varRet As Variant, lngPOCount As Long
Dim lngPONum As Long, lngI As Long, intLine As Integer
Dim lngFirstPONum As Long, lngThisVend As Long
Set db = DBEngine(0)(0)
Set rstPO = db.OpenRecordset("tblPurchases", dbOpenDynaset, dbAppendOnly)
Set rstPOServices = db.OpenRecordset("tblPurchaseDetails", dbOpenDynaset,
dbAppendOnly)
Set rstSToOrder = db.OpenRecordset("zqryServicesToOrder")
varRet = SysCmd(acSysCmdInitMeter, "Creating Purchase Orders....", lngSOCount)
rstSToOrder.MoveFirst
Do Until rstSToOrder.EOF
If lngThisVend <> rstSToOrder![ThisVend] Then
If lngThisVend <> 0 Then
CommitTrans
intTrans = False
BeginTrans
intTrans = True
rstPO.AddNew
rstPO!Order_Date = [DateIn]
rstPO!SupplierID = rstSToOrder![ThisVend]
rstPO!File = [FileID]
rstPO!EmployeeID = [EmployeeID]
rstPO.Update
lngThisVend = rstSToOrder![ThisVend]
rstPOServices.AddNew
rstPOServices!ServiceID = rstSToOrder![ServiceID]
rstPOServices.Update
End If
Loop
Exit Do
CommitTrans
DBEngine.Idle dbFreeLocks
End If
End Sub
Thanks for your help
John
mray29 - 07 Mar 2008 20:57 GMT
Are you seeing an error message?
> Can you tell me why this code is not working???
>
[quoted text clipped - 45 lines]
>
> John
Robert Morley - 07 Mar 2008 21:29 GMT
You have two If's, but only one End If, and you've got an Exit Do outside
the loop. It's a good idea to get used to indenting your code inside any
If, Do, While, or With block. Doing that makes it easier to read, and
easier to spot errors like this.
Rob
> Can you tell me why this code is not working???
>
[quoted text clipped - 45 lines]
>
> John
Steve Sanford - 10 Mar 2008 05:05 GMT
> Can you tell me why this code is not working???
Lots of reasons..
First off, EVERY module should begin with these two lines
Option Compare Database
Option Explicit
In the IDE, click on TOOLS/ OPTIONS . On the Editor tab, everything should
be checked except the first check box "Auto Syntax Check".
--------
** You didin't declare these variables:
rstPOServices
lngSOCount
intTrans
BTW, the variable "intTrans" should be declared as Boolean (bolTrans), not
Integer because you are setting it True/False.
** The nesting of the IF()'s and the DO loop is wrong. And the "EXIT DO" is
outside of the loop.
** "lngThisVend" needs to have a value set before you start the loop. On the
first pass , it is NULL. The line before the DO statement should probably set
the value to the field value in the first record of the recordset. The next
line would be
rstSToOrder.MoveNext
** The first "CommitTrans" is in the wrong place and whould be deleted. The
2nd "CommitTrans" should be moved to just after the line
rstPOServices.Update.
**You do not have any way to do a ROLLBACK, or a way to check if a ROLLBACK
is necessary.
** In lines like this
rstPO!Order_Date = [DateIn]
if [DateIn] is a field in the recordset "rstSToOrder", then it has to be
fully qualified:
rstPO!Order_Date = rstSToOrder![DateIn]
You need to decide if you want to commit the changes after each insert
(.AddNew/.Update), or after ALL inserts have been made. That will determine
if the "BeginTrans/CommitTrans" statements are inside or outside of the DO
loop. I would suggest committing after each insert. And you need a way to
know which records were not inserted (more code).
Some error trapping code would be nice....
HTH

Signature
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
> Can you tell me why this code is not working???
>
[quoted text clipped - 45 lines]
>
> John
Steve Sanford - 10 Mar 2008 05:08 GMT
Too fast on the send button.....
These variables were declared but never used (can be deleted from the code):
rstPORoducts
intLine
lngPOCount
lngPONum
lngI
lngFirstPONum
qd
HTH

Signature
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
> > Can you tell me why this code is not working???
>
[quoted text clipped - 103 lines]
> >
> > John