Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / March 2008

Tip: Looking for answers? Try searching our database.

Problem with code (Loop without DO)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Johnkl - 07 Mar 2008 20:35 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.