VB Question

Andy,

I made a couple of changes. Don't move to next record if
> 21 (goes on next truck). The "filler" record total was calculated
wrong. And I requeried the form after.

Will check on more later. Have to go now.

Wayne
 

Attachments

Sucess!!

Hi Guys,

Played with the code abit and managed to do what I needed for > 21 by using the following code:

Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim NumOfRec As Long
Dim NewValue, CurrentValue, StoredValue, TotalValue As Double

Set myDB = CurrentDb()
Set rst = myDB.OpenRecordset("tblLDCNewark", dbOpenDynaset)

rst.MoveLast
NumOfRec = rst.RecordCount
NewValue = 1

StoredValue = 0

rst.MoveFirst

'This will only look through the existing records
'and not any records added via this loop
For i = 1 To NumOfRec

CurrentValue = rst.Fields("Rows")

TotalValue = CurrentValue + StoredValue

Select Case TotalValue

Case Is = 21
With rst
.Edit
.Fields("LoadNumber") = NewValue
.Update
End With
NewValue = NewValue + 1
StoredValue = 0
rst.MoveNext

Case Is < 21
With rst
.Edit
.Fields("LoadNumber") = NewValue
.Update
End With
StoredValue = StoredValue + CurrentValue
rst.MoveNext
Case Is > 21

balancevalue = TotalValue - 21
CurrentLane = rst.Fields("Rows")
changevalue = CurrentValue - balancevalue
copyproduct = rst.Fields("Product")
copydestination = rst.Fields("LDC")

With rst

.Edit
.Fields("Rows") = changevalue
.Fields("Loadnumber") = NewValue

.Update

.AddNew
.Fields("Product") = copyproduct
.Fields("LDC") = copydestination
.Fields("Rows") = balancevalue
.Fields("Loadnumber") = NewValue + 1

.Update
End With
'If using the same NewValue as previous record,
'it should increment after adding the new record
'if not, then it should be incremented before adding the new record?
NewValue = NewValue + 1
StoredValue = 0

rst.MoveNext

End Select

Next i

Set rst = Nothing
Set myDB = Nothing

Me.Requery


Thanks Guys for all of your help, and am know working on the >21 part of the code so that the qty is changed and the balance is inserted into the new record.

Cheers

Andy
 
Re: Sucess!!

spacepro said:
Hi Guys,

Played with the code abit and managed to do what I needed for > 21 by using the following code:
Click to expand...
I recognize that code! ([sing]We are the champions[/sing] - We includes WayneRyan)
:p ;)

After reading your other post, what you wanted made more sense (but made my head spin even more!).

I will try to take a look at your file when I have some free time which probably won't be till next year. Sounds long but it's only two weeks away!
:)

I just love to tell people "See you next year!" on Dec 31st, don't you?
:D
 
Andy, Cosmos,

It was fun. Have a good Xmas and see you next year.

Maybe next year we'll max out those trucks.

Wayne
 
Wayne, Cosmos

Thanks guys once again I have now achieved what I wanted with 99% coding from yourselves. Already looking at maxing the truck out . Maybe next year.

Anyway Thanks very much and have a wonderful holiday.

If I can repay you guys any time, if you need anything a far as website design or anything like that then just ask, I already set up a forum running for Hayley.

Happy Holidays

Andy
 
Merry Christmas to you both!

And I appreciate the offer, Spacepro!
 
Hi All,

I forgot about looping through the added records when assigning the loadnumber field.

Is it possible to add code after the with statement adding the record to start the loop again or not. Is this bad design or is there a solution that I haven 't thought about.

I tried this but didn't work:

If Totalvalue = > 21 Then
rst!Loadnumber = Newvalue + 1
rst.movenext
End If

Am I on the right track?

Thanks in advance

Andy

;)
 
Hi Andy,

I'm on a different computer, so don't have a copy of the
latest.

In the following when you are editing a recordset, you need:

Code: 
If Totalvalue = > 21 Then
   rst.Edit ' New Line
   rst!Loadnumber = Newvalue + 1
   rst.Update ' New Line
   rst.movenext
   NewValue = NewValue + 1 ' Need to point to the next truck
End If

hth,
Wayne
 
Wayne / Cosmos,

Sorry to be a pain but I am really struggling with the looping through the added records.

I have attached an updated db of my code and a bit of text explaining it. If you guys can have a look and tell me what I am doing wrong.

I would appreciate it.

Thanks

Andy
 

Attachments

spacepro said:
Sorry to be a pain but I am really struggling with the looping through the added records.
Click to expand...
Why do you need to loop through the new records?

If you need to, replace

Code: 
NumOfRec = rst.RecordCount

...

'This will only look through the existing records 
'and not any records added via this loop
For i = 1 To NumOfRec
...
Next i
with
Code: 
'Will loop through all the records.
While Not rst.EOF and Not rst.BOF
...
Wend
I'm not so sure that it is necessary to loop through the added records. The "new" records are ones where the totals exceed the 21 and the balance is transfered to a new truck, correct? However, wouldn't you also need to change the record that causes it to go over 21 to a number that makes the total exactly 21?
 
Re: Sucess!!

spacepro said:
Thanks Guys for all of your help, and am know working on the 21 part of the code so that the qty is changed and the balance is inserted into the new record.
Click to expand...
This goes towards my last post.
Try this..
Code: 
Case Is > 21
    BalanceValue = TotalValue - 21
[color=green]    'Store current records value[/color]
    CurrentValue = rst.Fields("NumOfLanes")
[color=green]    'Calculated what the value of current records should be to add up to 21[/color]
    UpdatedValue = CurrentValue - BalanceValue
[color=green]    'Repeat the next line's logic for any other fields
    'that need to be duplicated[/color]
    CurrentLane = rst.Fields("MyField")
    With rst
[color=green]        'Change current record's value to the calculated value[/color]
        .Edit
        .Fields("NumOfLanes") = UpdatedValue
        .Fields("MyField") = CurrentLane
        .Update
[color=green]        'Add new record[/color]
        .AddNew
[color=green]        'Enter values for new record[/color]
[color=green]        'Add one to current lane since the balance should go to a new lane[/color]
        .Fields("MyField") = CurrentLane + 1
        .Fields("NumOfLanes") = BalanceValue
        .Update
    End With
[color=green]    'If using the same NewValue as previous record,
    'it should increment after adding the new record
    'if not, then it should be incremented before adding the new record?[/color]
    NewValue = NewValue + 1
    StoredValue = 0
    rst.MoveNext
Still not perfect. I think that it might be better to the code to first add the appropriate records such that the totals will add up to 21 and then assign the truck/lanes.
 
Last edited:
Code: 
Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim NumOfRec As Long
Dim CurrentLanes, StoredLanes, TotalLanes As Double

Set myDB = CurrentDb()
Set rst = myDB.OpenRecordset("tblLanes", dbOpenDynaset)

StartOver:
[color=green]'Set StatusBar message[/color]
StsBar = SysCmd(acSysCmdSetStatus, "Calculating Lanes Usage...")

rst.MoveLast
NumOfRec = rst.RecordCount

StoredLanes = 0

rst.MoveFirst

For i = 1 To NumOfRec

CurrentLanes = rst.Fields("Lanes")

TotalLanes = CurrentLanes + StoredLanes

Select Case TotalLanes

Case Is = 21
    StoredLanes = 0
[color=green]    'If at end of records and =21 then assigning of lanes is complete[/color]
    If i = NumOfRec Then
        GoTo AssignTruck:
    End If
    rst.MoveNext

Case Is < 21
[color=green]    'If at end of records and <21 then assigning of lanes is complete[/color]
    StoredLanes = StoredLanes + CurrentLanes
    If i = NumOfRec Then
        GoTo AssignTruck:
    End If
    rst.MoveNext

Case Is > 21
    BalanceLanes = TotalLanes - 21
[color=green]    'Store current records value[/color]
    CurrentLanes = rst.Fields("Lanes")
[color=green]    'Calculated what the value of current records should be to add up to 21[/color]
    UpdatedLanes = CurrentLanes - BalanceLanes
    
    With rst
[color=green]        'Change current record's value to the calculated value[/color]
        .Edit
        .Fields("Lanes") = UpdatedLanes
        .Update
[color=green]        'Add new record[/color]
        .AddNew
[color=green]        'Enter values for new record
        'Add one to current lane since the balance should go to a new lane[/color]
        .Fields("Lanes") = BalanceLanes
        .Update
    End With
    StoredLanes = 0
    rst.MoveNext

End Select

Next i

GoTo StartOver:

AssignTruck:
StsBar = SysCmd(acSysCmdSetStatus, "Assigning Trucks...")
rst.MoveLast
NumOfRec = rst.RecordCount

TruckNum = 1
StoredLanes = 0

rst.MoveFirst

For n = 1 To NumOfRec

CurrentLanes = rst.Fields("Lanes")

TotalLanes = CurrentLanes + StoredLanes

Select Case TotalLanes

Case Is = 21
    With rst
        .Edit
        .Fields("Truck") = TruckNum
        .Update
    End With
    TruckNum = TruckNum + 1
    StoredLanes = 0

Case Is < 21
    With rst
        .Edit
        .Fields("Truck") = TruckNum
        .Update
    End With
    StoredLanes = StoredLanes + CurrentLanes

Case Is > 21
[color=green]    'This case should [b]never[/b] return true [/color]
    MsgBox "Somthing is wrong!", vbCritical, "Problem occured..."
    
End Select

rst.MoveNext

Next n

StsBar = SysCmd(acSysCmdSetStatus, " ")
Set rst = Nothing
Set myDB = Nothing
This should assign the Lanes and Truck. Lemme know if this is what you are looking for, if not post back the problem.
 

Attachments

Cosmos,

Thank you very much for your time into my problem.

I am still confused on the code but I will try the code and let you know how I go on.

many many Thanks

Andy
 
I've found a problem, in that in trying to assign lanes there needs to be a way to figure out when it is done. As it stands right now, it won't work correctly.
 
Try this and see. Where I've used "...", use the same code as before.
Code: 
Dim myDB As DAO.Database
...

Set myDB = CurrentDb()
Set rst = myDB.OpenRecordset("tblLanes", dbOpenDynaset)

[color=red][b]StartOver[/b][/color]:
rst.MoveLast
NumOfRec = rst.RecordCount

StoredLanes = 0

rst.MoveFirst

For i[b]Assign[/b] = 1 To NumOfRec

CurrentLanes = rst.Fields("Lanes")
TotalLanes = CurrentLanes + StoredLanes

Select Case TotalLanes

Case Is = 21
...

Case Is < 21
...

Case Is > 21
...

End Select

Next i[b]Assign[/b]

rst.MoveLast
NumOfRec = rst.RecordCount
StoredLanes = 0
rst.MoveFirst

[color=green]'Loop to check if all the lane assignments are OK,
'If not, go back to assigning loop[/color]
For i[b]Check[/b] = 1 To NumOfRec

CurrentLanes = rst.Fields("Lanes")
TotalLanes = CurrentLanes + StoredLanes

Select Case TotalLanes

Case Is = 21
    StoredLanes = 0
    rst.MoveNext

Case Is < 21
    StoredLanes = StoredLanes + CurrentLanes
    rst.MoveNext

Case Is > 21
[color=green]    'If there is still a sequence >21 
     'go back and rerun assigning of lanes[/color]
    GoTo [color=red][b]StartOver[/b][/color]:

End Select

Next i[b]Check[/b]

[color=blue][b]AssignTruck[/b][/color]:
...
Set myDB = Nothing
 

Attachments

Cosmos,

You are a star. Thanks

I ran the code and it was ok but didn't like when the lane value was 9.112233 etc.
So I have formatted the calculation so that it round off to the next whole number using the "int" function in my query that calculates the lanes.

I have tested it using multiple scenarios with different quantities and is seems to be OK.
I will keep up with the testing, but it looks like you did the trick.

I cannot thank you enough for the effort you have put in to help me through this.

It is much appreciated.

Don't forget if I can ever repay you with web design or hosting requirements in the future, give me a shout.

I will keep you posted on the db or if I come across any problems.

Hope you have a wonderful and jolly Christmas.

Cheers

Andy
;) :) :) :) :)
 
Cosmos,

Hey, you did all right. I don't know if those
trucks will ever bring us anything, but your
code worked fine!

I hope you and Andy have a Merry Christmas and
a Happy New Year.

Wayne
 
Hey Wayne,

You never know, Santa might be the driver and bring you something.

Merry Xmas

Andy
;)
 
Andy,

He won't bring me what I need. Oh well ...

I hope that we got your shipping problems at least a little
clearer.

A big thanks to Cosmos, not only is he QUICK, but once you get
his attention ... problem solved!

Thanks and Merry Xmas to you both,
Wayne
 
spacepro said:
I ran the code and it was ok but didn't like when the lane value was 9.112233 etc.
So I have formatted the calculation so that it round off to the next whole number using the "int" function in my query that calculates the lanes.
Click to expand...
Be carefull when using int, see these knowlwedge base article for why.
http://support.microsoft.com/default.aspx?scid=kb;en-us;210423
http://support.microsoft.com/default.aspx?scid=kb;en-us;111781

Another article on Access Math that might be of interest is
When Access Math Doesn't Add Up by Luke Chung
http://www.fmsinc.com/tpapers/math/index.html

See this thread on how to round up.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=55155&highlight=RoundUp
or these knowledge base article
http://support.microsoft.com/default.aspx?scid=kb;en-us;210564&Product=acc2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;209996&Product=acc2000
WayneRyan said:
A big thanks to Cosmos, not only is he QUICK, but once you get
his attention ... problem solved!
Click to expand...
Aw... shucks..
:)
 
Last edited:
You must log in or register to reply here.

Similar threads

A
Filter Records
Replies
1
Views
522
June7
June7
M
Solved Update records through a recordset
Replies
11
Views
1,001
Pat Hartman
P
D
Solved Unkown Cls/Collection Holding An Event
2 3 4
Replies
70
Views
3,248
dalski
D
W
The problem has been resolved and the program can now be executed. Thank you very much.
Replies
7
Views
661
Ken Sheridan
Ken Sheridan
A
FILTER PRINT REPORT
Replies
2
Views
403
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom