Solved

variables not changing during loop

Posted on 2010-11-22
9
281 Views
Last Modified: 2012-06-27
I 've got a production planning screen where i want to enter start times and end times based on a query that gives me an estimated duration of a production order.
I i calculate this for one single record it gives me the right result.
If the production start time is filled in in the field [Prod Start], my code will look for the duration, add this to the start time and fill in the [Prod End] field with the right result.

so this is working:
Dim i As Long
Dim d As Double
Dim s As Double


i = Me.ID
d = CDbl(Me.[Prod Start])
s = CDbl(DLookup("Minutes", "qryMRorderDetail", "ID = " & i) / 1440)
d = (d + s)
Me.Prod_End = d

fine, but now I would want to automatically populate the start time and end time of all the following records.

I sort the records first. by machine and a number which I call a timeslot, which serves to tell me in which order the ite;s need to be produced, no problems there
there are records for 2 production machines, so I want to stop updating the records when I find  am at the end of one machine's records.

The end of one production should be the Prod Start time of the following one

So I tried this loop:


m = Me.MachineNP

Do Until Me.MachineNP <> m

t = Me.MRProductionOrders_Slot
i = Me.ID
d = CDbl(Me.[Prod Start])
s = CDbl(DLookup("Minutes", "qryMRorderDetail", "ID = " & i) / 1440)
d = (d + s)
Me.Prod_End = d
'now look for the next productionorder  and start it where this one ended
'if no more orders later than this one for this machine we can stop
If t = DMax("NPTimeslots.Slot", "qryPlanMotherRolls2", "MachineNP = " & Chr(34) & m & Chr(34)) Then
MsgBox "End of the list for " & m
Exit Sub
End If

DoCmd.GoToRecord , , acNext
DoCmd.GoToControl "[Prod Start]"

Me.[Prod Start] = d
Loop


Now I must be doing something wrong here , as I can see the program is filling out the first end time correctly, but then writes this same time in all the following records. I found in the debugging screen that t and i did not change after moving to a next record, so the calculation of d kept giving the same result
0
Comment
Question by:gstallaert
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 34187288
Any chance of a sampe database to play with?
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34187322
Is your form a continuous form?  If so, have you tried looping through the form's Recordset instead?
0
 
LVL 1

Author Comment

by:gstallaert
ID: 34187834
Hi,JezWalters, the database is made on a chinese computer and won't run on an english one unless you set your computers support for non unicode programs to chinese.

I think you are right that looping through the recordset would be the answer, but i'm not so experienced in working with recordsets.
so in pseudocode what i would want to do is:


User double clicks on [Prod End] date of a random record in the list ( it's a split form)
the vba code should read the record id of that record and store it in variable i and the name of the machine ( there are currently 2 machines in the factory, the machines wor at different speeds)The code will then " do something" ( using  i find the duration of the production based on quantity in another query, then calculate the time when production is finished) and then update the [Prod End] field.(the doing something works fine if I do it for just one record, but not if I try to do it in a loop for each record one after the other.)

it should then go to the next record in the list. THe [Prod Start] date should be the [Prod End] date of the previous record.
the code should then find the record ID of this next record, look again in the other query to find production time neede to produce this next item, calculate the new [Prod End] for that record and update that record's [Prod End] field, then loop further untill all production orders for that machine have been updated with new start and end dates.
 how do i get the variable i to give me each time the ID of the next record in stead of staying the same?

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34187853
What do you mean when you say that you have a "split form"?

Do you mean that you have a single-record form with a continuous-record subform?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:gstallaert
ID: 34188068
I mean it's an access 2007 "Split Form", one of the form types. There's no subform.
0
 
LVL 1

Author Comment

by:gstallaert
ID: 34188099
I' going to sleep now. see you tomorrow.

0
 
LVL 17

Accepted Solution

by:
JezWalters earned 500 total points
ID: 34189155
I'm not sure if I've completely understood what you're after, but this should get you started:
Option Explicit

Option Compare Database



Private Sub Prod_End_DblClick(pintCancel As Integer)



    Dim datProdEnd As Date

    Dim lngId As Long

    Dim strMachineNP As String



    With RecordsetClone

        .Bookmark = Recordset.Bookmark

        lngId = !ID

        strMachineNP = !MachineNP

        datProdEnd = ![Prod Start]

        Do Until !MachineNP <> strMachineNP

            .Edit

            ![Prod Start] = datProdEnd

            ![Prod End] = ![Prod Start] + DLookup("Minutes", "qryMRorderDetail", "ID = " & !ID) / 1440

            datProdEnd = ![Prod End]

            .Update

            .MoveNext

            If .EOF Then

                Exit Do

            End If

        Loop

    End With

    DoCmd.GoToRecord , , acNext

    DoCmd.GoToControl "[Prod Start]"

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:gstallaert
ID: 34193277
Waw!!! this is nearly perfect, i adapted it a bit because i had so;e other names for my variables. the code that wors now s this:
With RecordsetClone
        .Bookmark = Recordset.Bookmark
        m = !MachineNP
        d = CDbl(![Prod Start])
        Do Until !MachineNP <> m
            .Edit
            ![Prod Start] = d
            i = !ID
            d = CDbl(![Prod Start])
            s = CDbl(DLookup("Minutes", "qryMRorderDetail", "ID = " & i) / 1440)
            d = (d + s)
            ![Prod End] = d
            .Update
            .MoveNext
            If .EOF Then
                Exit Do
            End If
        Loop
    End With


THanks a lot for this!
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 34194892
I can't be certain from your code snipet above, but make sure all your modules start with "Option Explicit" and make sure you ALWAYS explicitly declare all your variables.  I don't think you need the CDbl() calls either, as long as your Prod Start/End table columns are defined to be of type "Date/Time".

Finally, you'd do well to use more meaningful variables too - my type-based prefixes are the most common convention.  It's a matter of opinion, but it's not really worth creating variables just to perform calcuations (d & s)), or if they are only used once (i).

Glad I could be of assistance!  :-)
Option Explicit

Option Compare Database



Private Sub Prod_End_DblClick(pintCancel As Integer)



    Dim datProdEnd As Date

    Dim strMachineNP As String



    With RecordsetClone

        .Bookmark = Recordset.Bookmark

        strMachineNP = !MachineNP

        datProdEnd = ![Prod Start]

        Do Until !MachineNP <> strMachineNP

            .Edit

            ![Prod Start] = datProdEnd

            ![Prod End] = ![Prod Start] + DLookup("Minutes", "qryMRorderDetail", "ID = " & !ID) / 1440

            .Update



            datProdEnd = ![Prod End]

            .MoveNext

            If .EOF Then

                Exit Do

            End If

        Loop

    End With

    DoCmd.GoToRecord , , acNext

    DoCmd.GoToControl "Prod Start"

End Sub

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now