Solved

variables not changing during loop

Posted on 2010-11-22
9
278 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

758 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

21 Experts available now in Live!

Get 1:1 Help Now