Query Help Show Balance Per Record

[Steve5]

Hello all DB Gods and Goddess's,

I have a simple question about getting a balance per record.

In the attached DB under the [table1 Query] is my problem.


Everytime I try to calculate the first [Job] reduces by one, and then it ends there.

example:
each [Job] has a [QTY_OPEN] that I need to reduce from [ONH] per [Job] and provide a running [Balance].

Below is what I need to happen
[Job]-----------[QTY_OPEN]------[ONH]------[Balance]

CTO6535238001------(1)-------------(860)--------(859)
CTO6535298001------(1)-------------(860)--------(858)
CTO6535608001------(1)-------------(860)--------(857)




PLEASE Help this wanna be Access Gru

 

[sonny]

Can you post the sql?

 

[Steve5]

Hello Pat,
I have read many of your past posts, you could say I am kind of a fan.

Below a friend of mine wrote the following code, however, we are having trouble with the code evaluating the part numberS in the sequence they sit in the table.

[PR] and then [PR_Date] must be insequence accending order.
In that order is when I need the [PART] evaluated.
THe jobs that are dated earlier must be first if they have CTO in front of the job number then the 345565 type jobs are last priority.

[PR] = 1 for CTO jobs
[PR] = 2 non-CTO jobs must be evaluated after CTO has consumed its requirements first

[PR_Date] = Date the job was created.



I have attaced the sample DB with test data, if you have the time that would be great.

----------sample ----- example--------- Also in attached DB.

Private Sub cmdCALC_Click()
On Error GoTo cmdCALC_Err

Dim db As Database
Dim rec As Recordset
Dim Counter As Integer
Dim Balance As Double
Dim Part As String

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT PART, DEMAND, ON_HAND, REMAINDER, EVALUATE FROM T_DJL_1C ORDER BY PART")

Counter = 0
Balance = rec("ON_HAND").Value
Part = rec("PART").Value

Do While Not rec.EOF
If Counter = 0 Then
If rec("EVALUATE").Value = "True" Then
rec.Edit
rec("REMAINDER") = (rec("ON_HAND").Value - rec("DEMAND").Value)
rec.Update
Balance = (Balance - rec("DEMAND").Value)
Counter = Counter + 1
rec.MoveNext
Else
rec.MoveNext
End If

Else

If Part = rec("PART").Value Then
If rec("EVALUATE").Value = "True" Then
rec.Edit
rec("REMAINDER").Value = (Balance - rec("DEMAND").Value)
rec.Update
Balance = (Balance - rec("DEMAND").Value)
Counter = Counter + 1
rec.MoveNext
Else
rec.MoveNext
End If

Else
Counter = 0
Balance = rec("ON_HAND").Value
Part = rec("PART").Value
End If

End If

Loop

MsgBox ("Finished")

cmdCALC_Exit:
Exit Sub

cmdCALC_Err:
MsgBox Err.Description
Resume cmdCALC_Exit

End Sub

 

[Steve5]

Pat, I have another question if you have the time.

The above code is poping up this message:
"Increase MaxLocks per file registry entry"

I understand I can increase my registry to support, but the record count could be as high as 100,000 records and I am not sure I should increase my mem support that high?

Any suggestions on how to improve the code so it does not require the mem increase?