Calculate and Generate Bundles and Books (1 Viewer)

jraccess · Mar 29, 2019

Hi,
I want to pack the books in pockets and pockets into a bundle.
For Example for 100 books, I would like to pack the pockets with 6 books with each and 10 pockets in 1 Bundle. Likewise I would like to generate data for bundles , pockets and books details as the following header, in a table

Bundle Number,Pocket Number,Books in Pocket,Book Start number (always as 1), Book End Number, Category, Author, Place.

In that above example total 100 books.
Per Pockets : 6 Books
Per Bundle : 10 Pockets
Bundle Start Number : 001
Category : Comics
Author : Misc
Place: Class Room

with this details ,Now I want the data as,

with 100 books we can get 16 full pockets and 1 pocket with 4 books and we can get 2 bundles (10 pockets in one bundle and 7 pockets in another bundle)

So Now Bundle number will be generate as 001,002

In Bundle No 001 we should get pockets numbers as 01,02,03,04,...10 (Because we assigned 10 pockets per Bundle)

In each pockets the book start number 01 and the last number 06 for full pockets and the last number will be 04 for the 17th bundle.

Category,Author, and Place same for all...

In my access form I will enter the following details,

Total No of Books:
No of Books per Pocket:
No of Bundles per Bundle:
Bundle Start Number:
Category:
Author:
Place:

So My Sample data should generate like this in Report table which has the headers as
Bundle Number,Pocket Number,No of Books in Pocket,Book Start number (always as 1), Book End Number, Category, Author, Place.

001 01 06 01 06 Comics Misc Class Room
001 02 06 01 06 Comics Misc Class Room
001 03 06 01 06 Comics Misc Class Room
001 04 06 01 06 Comics Misc Class Room
001 05 06 01 06 Comics Misc Class Room
001 06 06 01 06 Comics Misc Class Room
001 07 06 01 06 Comics Misc Class Room
001 08 06 01 06 Comics Misc Class Room
001 09 06 01 06 Comics Misc Class Room
001 10 06 01 06 Comics Misc Class Room
002 01 06 01 06 Comics Misc Class Room
002 02 06 01 06 Comics Misc Class Room
002 03 06 01 06 Comics Misc Class Room
002 04 06 01 06 Comics Misc Class Room
002 05 06 01 06 Comics Misc Class Room
002 06 06 01 06 Comics Misc Class Room
002 07 04 01 04 Comics Misc Class Room

[with these I will design a report and will get labels to paste on each pocket and bundle with their details.

For Pockets the label it contains (Bundle Number, Pocket Number , Total Books , Book number from ,to...)
]
I designed the form and report., But I want that auto generate table by vba macro when the details entered in user form.

Ranman256 · Mar 29, 2019

on the form are the text boxes shown in the picture.
the control source for txtTotPock, (TOTAL POCKETS) : =calcPock([txtBooks])
the control source for txtTotBnd , (TOTAL BUNDLES) : =calcBnd([txtTotPock])

this code is put into a module to use by the text boxs formulas:

Code:

Public Function calcTot(ByVal plNum As Single, plDiv As Integer)
Dim nVal As Single
nVal = plNum / plDiv
calcTot = Int(nVal) + IIf((nVal - Int(nVal)) > 0, 1, 0)
End Function

  'calculate POCKETS
Public Function calcPock(ByVal piBooks As Integer)
calcPock = calcTot(piBooks, 6)
End Function

  'calculate BUNDLES
Public Function calcBnd(ByVal piPoks As Integer)
calcBnd = calcTot(piPoks, 10)
End Function

then when all boxes are filled out ,click the RUN button to run this code:

Code:

Private Sub btnRun_Click()
Dim iBnd As Integer, iPok As Integer, iBooksInPok As Integer
Dim iRemPok As Integer, iRemBk As Integer, iPoksInBnd As Integer, iPokVal As Integer
Dim iBndls As Integer

DoCmd.SetWarnings False
iBooksInPok = 6
iPoksInBnd = 10

iRemBk = txtBooks
iRemPok = txtTotPock

iBookVal = iBooksInPok
iPokVal = iPoksInBnd
If iRemBk < iBooksInPok Then iBookVal = iRemBk

For iBnd = 1 To txtTotBnd
   If iRemPok < iPokVal Then iPokVal = iRemPok
   
   For iPok = 1 To iPokVal
   
      'Debug.Print iBnd, iPok, iBookVal, 1, iBookVal, txtCat, txtAuthor, txtPlace
      sSql = "Insert into table (bundle, Pocket,BookVal,BookStart,BookEnd,Cata,Author,Place) values (" & iBnd & "," & iPok & "," & iBookVal & ",1," & iBookVal & ",'" & txtCat & "','" & txtAuthor & "','" & txtPlace & "')"
      DoCmd.RunSQL sSql
      
      iRemBk = iRemBk - iBooksInPok
      If iRemBk < iBooksInPok Then iBookVal = iRemBk
   Next
   
      iRemPok = iRemPok - iBnd * 10
      If iRemPok < iPoksInBnd Then iPokVal = iRemPok
Next

DoCmd.SetWarnings True
MsgBox "Done"
End Sub

Calculate and Generate Bundles and Books (1 Viewer)

jraccess

New member

Ranman256

Well-known member

Similar threads

Users who are viewing this thread