VB Question (1 Viewer)

[spacepro]

Assign Value to table based on recordset

Hi All,

I was wondering if anyone can help me in trying to write some vb code for the following purpose:

I want to loop through records until a quantity of 21 is reached then assign a value to a field then carry on with the loop.

If 21 can't be met 20 or 19 is OK but much be round up to the nearest number.

Example:

Record 1 - No of Lanes = 9.22222
Record 2 - No of Lanes = 14.11112
Record 3 - No of Lanes = 12.333328
Record 4 - No of Lanes = 8.7888954

So Value 0f the field I want to assign will be as follows:

1
2
3
4


So the formula should be something like
If record 1 = 21 then
assign value of 1 . If not then go to next record and add quantities together. If = 21 then assign value = 1 but must = 21 exactly . If quantities over 21 then create duplicate record and adjust quantity to balance (balance minus 21) on the duplicate record.
Then procede to the duplicate and then loop through the records has above until all data in table has been assigned a value in the required field.

I understand this could take time, just don't know how to go about it. No experience in looping records.

Many Thanks in advance on this complicated matter.

Andy

 

[Cosmos75]

I assume you want to set the value of field in the same table (I am colling it tblLanes), if not let me know.

Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim NumOfRec As Long

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

rst.MoveLast
NumOfRec = rst.RecordCount

For i = 1 To NumOfRec
If i = 1 Then
    rst.MoveFirst
Else
    rst.MoveNext
    If i Mod 21 = 0 Then
        With rst
            .AddNew
            .Fields("MyField") = NewValue
            .Update
        End With
    End If
End If

Set rst = Nothing
Set myDB = Nothing

MyField is the name of the field you want to add the value to.
NewValue is the value (or formula) you want to use to set the value for MyField.

I think this wil go through the records in the table in order of the primary key (autonumber?). If you need it to be in a particular order, You can creat a query based on your table and sort it in the query. Just replace tblLanes with the name of the query (e.g. qryLanes)

I am new to using DAO so I hope I haven't lead you off on the right track!

 

[WayneRyan]

Andy,

Not sure what this is all about, but this should get
you started. It will be far easier if you watch it
run with the debugger.

Dim dbs As DAO.Database
Dim sql As String
Dim rst As DAO.Recordset
Dim TotalSoFar As Long
Dim PrintGroup As Long

Set dbs = CurrentDb
sql = "Select * from YourTable"
Set rst = dbs.OpenRecordset(sql)

TotalSoFar = 0
PrintGroup = 1

While Not rst.EOF and Not rst.BOF
   If TotalSoFar + rst!NoOfLanes < 21 Then
      rst.Edit
      rst!PrintGroup = PrintGroup
      rst.Update
      TotalSoFar = TotalSoFar + rst!NoOfLanes
      rst.MoveNext
   ElseIf TotalSoFar + rst!NoOfLanes = 21 Then
      rst.Edit
      rst!PrintGroup = PrintGroup
      rst.Update
      PrintGroup = PrintGroup + 1
      TotalSoFar = 0
      rst.MoveNext
   Else
      rst.AddNew
      rst!PrintGroup = PrintGroup
      rst!NoOfLanes = 21 - TotalSoFar
      rst.Update
      TotalSoFar = 0
      PrintGroup = PrintGroup + 1
   End If
   Wend

Wayne

 

[WayneRyan]

Wow!

Cosmos is fast!

Wayne

 

[Cosmos75]

Re: VB Question......

Example:

Record 1 - No of Lanes = 9.22222
Record 2 - No of Lanes = 14.11112
Record 3 - No of Lanes = 12.333328
Record 4 - No of Lanes = 8.7888954

How do you get 9.22 lanes?

 

[Cosmos75]

Wow!

Cosmos is fast!

Wayne

But am I right?

 

[spacepro]

Thanks Cosmos,

The field to be updated is in the same table. Sorry if I was a bit vague. Anyway thanks for the code, sorry if I am sounding a bit thick but is there any chance you could comment the code as to the functions of the lines, as I am ok with vba but never played with some of the functions in the code.

I just want to understand the code it self, so that if I need to adapt the code for my project or change my way of thinking then I can progress and learn at the same time.

I do appreciate the time taken to help me.

Thank you

Wayneryan,

Cosmos is quick thank you as well if either of you could give me a brief explanation into your code I will appreciate it, I will certainly lookup some of these functions in help and on the web, but if you can give me a starter.

Many Many Thanks


Andy

 

[spacepro]

Cosmos,

the value of 9.2222 is based on a value resulted from a query which updates the table prior to me running the requested code.

Basically I have products that each product will say product a x 4 in a lane, product b x 8 in a lnae.

I divide the order qty by the Product Cube as above.
So if there is an incomplete lane of product is will throw a value of 9.something.

Possibly I could round this figure to the next round number to simplify things. Don't know how to do this, I assume this is something needs to be done in a query.

This wouldn't maximise vehicle space but is close enough.

Hope this explains things abit better.
ps. the 9.something is a random number just an example

Andy

 

[Cosmos75]

Found some mistakes! And added some code.

Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim NumOfRec As Long
[b][color=blue]Dim NewValue As Long[/color][/b]

[color=green]'Set database to be used as the currently opened database[/color]
Set myDB = CurrentDb()
[color=green]'Set recordset to be used as tblLanes[/color]
Set rst = myDB.OpenRecordset("tblLanes", dbOpenDynaset)

[color=green]'Move to the last record (must be done to get correct record count)[/color]
rst.MoveLast
[color=green]'Count # of records[/color]
NumOfRec = rst.RecordCount
[color=green]'Set first value of MyField to one[/color]
[b][color=blue]NewValue = 1[/color][/b]

For i = 1 To NumOfRec

If i = 1 Then
[color=green]    'Move to first records (don't need to do anything since [/color]
[color=green]    'you only want to do something every 21 records[/color]
    rst.MoveFirst
Else
[color=green]    'Move to next record[/color]
    rst.MoveNext
[color=green]    'This will perfom code every 21 records[/color]
    If i Mod 21 = 0 Then
        With rst
[color=green]             'Edit the curent record[/color]
            .[b][color=red]Edit[/color][/b]
[color=green]            'Set the value of MyField to NewValue[/color]
            .Fields("MyField") = NewValue
[color=green]            'Save the changes made[/color]
            .Update
        End With
[color=green]        'Add 1 to NewValue to be used for the next 21st record[/color]
        [b][color=blue]NewValue = NewValue + 1[/color][/b]
    End If
End If

[color=green]'Empty the reserved memory of rst & myDb
'If you don't do this, it will cause memory bloat[/color]
Set rst = Nothing
Set myDB = Nothing

Edit: Added comments as requested.

 

[WayneRyan]

Andy,

OK, I'll comment the code a little bit...

' Declare objects to interact with the db
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
' Total Lanes so far
Dim TotalSoFar As Long
' Just a number to "group" the records that
' total to 21
Dim PrintGroup As Long

' Open your table
Set dbs = CurrentDb
sql = "Select * from YourTable"
Set rst = dbs.OpenRecordset(sql)

' We're on group 1 total = 0
TotalSoFar = 0
PrintGroup = 1

While Not rst.EOF and Not rst.BOF
   ' We are always point at a "current record"
   ' If this record doesn't make the total >= 21 
   '    add the record to the current group
   '    and update the total
   '    move to the next record
   ' If this record makes the total = 21 
   '    add the record to the current group
   '    Reset the total to 0
   '    move to the next record
   ' If this makes the total > 21 
   '    we've overflowed
   '    add new "dummy" record to the current group
   '        with NoOfLanes = 21 - Total
   '    Set the Total = 0
   '    Don't move to new record, cause we have not
   '        processed this one yet
   If TotalSoFar + rst!NoOfLanes < 21 Then
      rst.Edit
      rst!PrintGroup = PrintGroup
      rst.Update
      TotalSoFar = TotalSoFar + rst!NoOfLanes
      rst.MoveNext
   ElseIf TotalSoFar + rst!NoOfLanes = 21 Then
      rst.Edit
      rst!PrintGroup = PrintGroup
      rst.Update
      PrintGroup = PrintGroup + 1
      TotalSoFar = 0
      rst.MoveNext
   Else
      rst.AddNew
      rst!PrintGroup = PrintGroup
      rst!NoOfLanes = 21 - TotalSoFar
      rst.Update
      TotalSoFar = 0
      PrintGroup = PrintGroup + 1
   End If
   Wend

If you enter the code (After swapping in the real names
of your tables and fields, then you can click on the
left-margin and a circle will appear. This is a break-
point. When the code runs it will stop here. If you
hover over a variable, the debugger will show you its
value. F8 will single-step you through the code, F5 will
let it run to completion.

You can also view the immediate window and type:

?TotalSoFar
15.2

and it will show you the value of your variables.

Good luck,
Wayne

 

[spacepro]

Wayne,

Thank you Thank you

the pair of you are so kind, and have already helped me so much, and at the same time I am learning a bit more.

Hopefully one day I can repay the favour!!

I will try the code and see how I go, if I have any problems I hope you don't mind me give either of you a shout.

Thanks Again

Andy

 

[Cosmos75]

But am I right?

I wasn't...


spacepro,

I just reread the question and I TOTALLY missed out on providing the right answer!

 

[WayneRyan]

Andy,

Not a problem. Just post to this thread.

Wayne

 

[spacepro]

Cosmos,

No worries, the main thing is that you took the time to try and help me in my project and I thank you for your time.

And some of your code helps me with some functions in vba that I am not an expert in, so you have helped me anyway.

Cheers

Andy

 

[WayneRyan]

Cosmos,

Not to worry, I didn't have much of a clue as to what the real
objective of this exercise was. I guess we were just seeing how
many "things" we could stuff in a truck.

See ya,
Wayne

 

[Cosmos75]

Bugged me that I didn't get it right the first time!

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("tblLanes", dbOpenDynaset)

rst.MoveLast
NumOfRec = rst.RecordCount
NewValue = 1

StoredValue = 0

rst.MoveFirst

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

CurrentValue = rst.Fields("NumOfLanes")

TotalValue = CurrentValue + StoredValue

Select Case TotalValue

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

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

Case Is > 21
    BalanceValue = TotalValue - 21
    [color=green]'Repeat the next line's logic for 
    'any other fields that need to be duplicated[/color]
    CurrentLane = rst.Fields("Lane") 
    With rst
        .AddNew
        .Fields("Lane") = CurrentLane
        .Fields("NumOfLanes") = BalanceValue
        'Add the current NewValue to the added record???
        .Fields("MyField") = NewValue
        .Update
    End With
[color=green]    'If using the same NewValue as previous record, 
    'it should increment [b]after[/b] adding the new record
    'if not, then it should be incremented [b]before[/b] adding the new record?[/color]
    NewValue = NewValue + 1
    StoredValue = 0
    rst.MoveNext

End Select

Next i

Set rst = Nothing
Set myDB = Nothing

The only thing I am unsure about is what to do when the total is greater than 21.

If quantities over 21 then create duplicate record and adjust quantity to balance (balance minus 21) on the duplicate record.

I've made it so that the total - 21 is inserted into a new record with the other fields being duplicated from the record that when added causes the total to be > 21. But it doesn't add NewValue to MyField for the record that causes the total to go over 21, should it be doing that?


The code will only run for all the previously existing records and not the newly added records (via the total-21 part of the loop).

Is that what was trying to be accomplished?


Now I won't be able to sleep well till I figure this out!

 

[WayneRyan]

Cosmos,

I think you've got it!

But when the total is > 21 (meaning it won't fit on the truck)
then add a dummy record to that set to make it = 21.

Do not go to the next record. This record goes on the next
truck.

At least I think that's how it works.

Wayne

 

[Cosmos75]

Lane, NumOfLanes, MyField
1, 10, 1
2, 10, 1
3, 1, 1
4, 5, 2
5, 6, 2
6, 0, 2
7, 9, 2
8, 4,
9, 10, 3
10, 6, 3
11, 2, 3
12, 10,
13, 10, 4
15, 1, 4
8, 3, 2
12, 7, 3

Here are the results of my code.
Existing Records are in Blue.
Added Records are in Red.

 

[spacepro]

Hi Guys,

Yes that's right it starts on a new truck. I think when I understanding the logic of the code.

Information:

Wayne, yes I am trying to allocate stock to vehicles automatically
see the following thread give you both a bit of info on my query.

Do apologise for being a bit vague, but my query is quite complicated to say the least.

I might be going about this the wrong way, but this is a unique and complex situation that I am trying to achieve. I hope that the question I posed and the solution you have posted is the answer to my situation.

Previous Thread relating to my query

Thanks Guys,

I will try to let you know how I get on, just to put your mind to rest (Cosmos).

Andy

 

[spacepro]

Hi Guys,

Sorry to be a pain but I have ran the code but still struggling a little bit. I have attached a example of the db. Access 2000 format and a form called Explanation will open up. Please take a look and any advise will be helpful.

Thanks in advance

Andy