Loops, Whiles and arrays... 1

[djayam]

Hi all,

I am entering new territory and could do with some help!

Main form - frm_NewOrder
Subform1 - frm_OrderPart
Subform2 (also nested in main form) - frm_Deliveries

The Main Form has a button btn_CompleteOrder. In the OnClick event I have the following:

Private Sub btn_CompleteOrder_Click()
    [COLOR=green]'Create variables[/color]
    Dim vOrderPartID As Integer
    Dim vOrderID As Integer
    Dim vDeliveredSoFar As Long
    Dim vTotalToDeliver As Long
    Dim vStillToDeliver As Long
    
    vOrderID = Me.ID
        
    [COLOR=green]'Set current OrderPartID[/color]
    vOrderPartID = 9
    
    [COLOR=green]'Check if any more deliveries are needed[/color]
    vDeliveredSoFar = Nz(DSum("Quantity", "tbl_Deliveries", "OrderPartID = " & vOrderPartID), 0)
    vTotalToDeliver = Nz(DSum("Quantity", "tbl_OrderPart", "ID = " & vOrderPartID), 0)
    
    If vDeliveredSoFar = vTotalToDeliver Then GoTo NoMoreDeliveries Else GoTo AddDelivery

AddDelivery:
    [COLOR=green]'Create new deliveries for all remaining quantities of OrderParts[/color]
    vStillToDeliver = vTotalToDeliver - vDeliveredSoFar
    
    DoCmd.RunSQL "INSERT INTO tbl_Deliveries (OrderPartID, DeliveryDate_Scheduled, Quantity, DeliveryDate_Delivered, OrderID, User) Values (" & vOrderPartID & ", Date(), " & vStillToDeliver & ", Date(), " & vOrderID & ", 'USER')"
    
    DoCmd.RunCommand acCmdSaveRecord
    Me.frm_Deliveries.Requery
        
NoMoreDeliveries:
    
End Sub

This works perfectly when OrderPartID = 9. What I want to do is repeat this code for all OrderParts with an OrderID equal to the main form's ID. There may be 1 order part or 100. I guess I need to use a loop of some description but I've never done it before and can't get my head around how!!

Any help much appreciated.

Jason

 

[MajP]

I assume you want to run this for all records on the subform.
Something like this untested, I am sure there are some errors.

Private Sub btn_CompleteOrder_Click()
dim intOrderPartID as integer
dim rsOrderParts as dao.recordset
dim strOrderPartFld as string
strOrderPartFld = yourNameOfTheFieldWithOrderPartID
set rsOrderParts = me.frm_OrderPart.form.recordsetclone
'this assumes the subform control is name "frm_OrderPart"
do while not rsOrderParts.eof
intOrderPartID = rsOrderParts.fields(strOrderPartFld)
call completeOrder (intOrderPartID)
rsOrderParts.movenext
loop

public sub completeOrder(vOrderPartID as integer)
'your old code here
'Create variables
Dim vOrderID As Integer
Dim vDeliveredSoFar As Long
Dim vTotalToDeliver As Long
Dim vStillToDeliver As Long
...
'delete vOrderPartID = 9
end sub

 

[djayam]

Hiya MajP,

Thanks loads mate - worked liked a charm. Only problem is it only works once! I have a "Delete Deliveries" button which complete removes the delivery records from the table and subform. If I try to run this code after deleting the records nothing happens? If I goto design view for the main form then back into form view again it works OK. Do I need to reset any variables or something?

Thanks again,

Jason

 

[djayam]

I've been doing some testing on this code and it is definitely not calling the public sub after succesfully running once. I have tried all sorts of rs.Close and Set rs = nothing but to no avail.

Is my code closing off the loop correctly and resetting it?

Thanks,

Jason

 

[MajP]

Can you post the code that you ended up writing that loops and calls the sub? By running once do you mean it only reads the first record on the subform or it reads all records on the subform, but does not allow you to run it a subsequent time?

I am having a hard time understanding what the code is not doing, but a couple of things to consider. The recordsetclone property is a snapshot copy of the forms recordset if you delete records from the table the clone this does not effect the clone. If you use the "recordset" property instead you are working with a pointer to the forms recordset. May want to try to see if that makes a difference (I think unlikely). You may need to requery your subform or form. Often when you delete or modify records by code, what you see on the form is not what is in the underlying recordset until the form is requeried. Although closing the recordset and setting the object to nothing is good housekeeping, I do not think that it is really necessary anymore. Once the sub finishes the variables go out of scope.

Sounds like you tried some debugging. Try some debug.prints to to see what is/is not happening

intOrderPartID = rsOrderParts.fields(strOrderPartFld)
debug.print intOrderPartID
call completeOrder (intOrderPartID)
rsOrderParts.movenext

and in the subroutine
debug.print "Subroutine called " & vOrderPartID

 

[djayam]

Hi MajP,

Thanks for your response mate. After doing some checks to see whether or not any deliveries should be added, this code is run:

StartAddingDeliveries:

    [COLOR=green]'Create new invoice for all automatically created deliveries
    'get last sequence number[/color]
        Dim vSeqNumber As Long
        vSeqNumber = Nz(DMax("InvoiceSeqNumber", "tbl_Invoices"), 1000) + 1
    [COLOR=green]'create invoice[/color]
        DoCmd.RunSQL "INSERT INTO tbl_Invoices (InvoiceDate, InvoiceAmount, InvoiceSeqNumber, OrderID, InvoiceLogDate, InvoiceLogUser) VALUES (Date(), 0," & vSeqNumber & ", " & vOrderID & ", " & Date & ", 'USER')"

    [COLOR=green]'requery invoices subform[/color]
    Me.frm_Invoices.Requery
    
    Me.btn_CompleteOrder.SetFocus
            
    Me.Text56 = "About to hit loop"

    
    Dim intOrderPartID As Integer
    Dim rsOrderParts As DAO.Recordset
    Dim strOrderPartFld As String
    strOrderPartFld = "ID"
    Set rsOrderParts = Me.frm_OrderPart_Purchase.Form.RecordsetClone
Me.Text56 = "In START OF loop " & intOrderPartID
    Do While Not rsOrderParts.EOF
       intOrderPartID = rsOrderParts.Fields(strOrderPartFld)
       Debug.Print intOrderPartID
       Me.Text56 = "In loop " & intOrderPartID
       Call CompleteOrder(intOrderPartID)
       rsOrderParts.MoveNext
    Loop

I couldn't see anything with the debug code so added a new textbox and set it to different values at different stages. When you come from the design view of the main form into form view and run the code it works perfectly. The loop is reached, it calls the subroutine and loops the correct number of times. It works REALLY well (and I am very grateful for your help on it!). However, if you then go to another record on the main form which calls completely new recordsets in the subforms and try to run the code again it reaches the loop but skips right past it! Me.Text56 = "In loop " & intOrderPartID is not reached at all, but Me.Text56 = "In START OF loop " & intOrderPartID is reached OK. I can only assume the WHILE Not condition is satisfied, but I don't don't know how!!

Thanks again mate - hope this helps figure out what is going on,

Jason

 

[MajP]

the debug.print code puts the output in the immediate pane of the VBA window. Also for decoding you can put a msgbox
i.e. msgbox "Starting Loop " & intOrderPart ID

If it is reaching the loop, but skipping past it

do while not.eof

One of two things could be happening. If a recordset has no records in it then it will be at the end of file and thus no action. The question is why on the second try are you returning no records? To test if you are returning records you could try
msgbox rsOrderParts.recordcount
before the loop

The other possibility is that you read through a recordset until the end and thus you are at the end of the file. To test that

msgbox rsOrderParts.eof

If either of these are happening, I am missing how that could happen. I am not seeing it.

If there are records and you are at the end of file, you can then put
rsOrderParts.movefirst
to force the recordset to the first record.

That would fix the symptom, but not the cause.

 

[djayam]

Hi Majp,

Thanks for your continued help on this mate. 3 things:

1. rsOrderParts.MoveFirst works! Thank you!

I tested the other things you mentioned, putting the MsgBox code just before the Do

2. rsOrderParts.RecordCount = 3 (correct count)
3. rsOrderParts.EOF = False on the first run, True thereafter.

So it seems I AM returning records, but for some reason it gets to the EOF and stays there...??

Ah well - if your curiosity wants me to test some more let me know, otherwise I am extremely happy with your workaround.

Thanks again MajP, have a big star!

Jason

 

[MajP]

I do not know why this happens, I am missing something. I could see if we were using the forms recordset vice the recordsetclone. When you are working with the clone it is a snap shot and you can mess with it without affecting the forms recordset.

Anyway, it is not neat,but works. However if the recordset has no records and you tell it to move first it will give you an error. Check that it is not empty to avoid the error.

if not rsOrderParts.count = 0 then
rsOrderParts.movefirst
end if