?
Solved

variables not changing during loop

Posted on 2010-11-22
9
Medium Priority
?
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
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 2000 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

801 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