Problem with code (Loop without DO)

[Johnkl via AccessMonster.com]

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]

Are you seeing an error message?

 

[Robert Morley]

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

 

[Steve Sanford]

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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

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

 

[Steve Sanford]

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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

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
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

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