Solved

variables not changing during loop

Posted on 2010-11-22
9
284 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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