Concept of a new database

[Kurtb4 via AccessMonster.com]

Hello,
This is the start of a new database adventure for a small manufactoring
process.

The customer wants to track Inventory 'sold' and 'on-hand' as well as
lifetime repair history for serial numbered items. This is no problem. I
would use tables 'tblInventory' and 'tblRepairs'. He also wants to track item
count of manufactoring orders for a specific model number. This is no problem
either with a table 'tblManufOrders'. Each Model number order would be
represented in 'tblManufOrders' as one record each without serial numbers.

My problem is what to do if a manufactoring order has been filled and the
Model number needs to be entered into 'tblInventory'. If the manufactoring
order calls out, let's say, for 50 items of a specific model number I would
like to copy from 'tblManufOrders' and append into 'tblInventory' 50 generic
records which then could be each edited with the serial numbers given to each
item in the manufactoring process.

Perhaps somebody in this forum would have some conceptual ideas on how to
accomplish this task?

Thanks,
Kurt

 

[Allen Browne]

In most manufacturing processes, I am guessing that the serial numbers are
likely to be sequential, so would it be good to get Access to assign the
serial numbers for you too? Otherwise you will need allows nulls within the
SerialNo field, which might be less than optimal.

You will need to adjust this code to handle the actual data types you use.
For the sake of illustration, they are simply numbers so we can work with
them sequentially. You could call the function for model 56 to make 50 new
items starting with serial number 1234 like this:
Call MakeInventory(46, 1234, 50)

Function MakeInventory(lngModelID As Long, lngFirstSerialNo As Long,
lngHowMany As Long)
Dim rs As DAO.Recordset
Dim lngI as Long

Set rs = dbEngine(0)(0).OpenRecordset("tblInventory", dbOpenDynaset,
dbAppendOnly)
For lngI = 0 To lngHowMany - 1
rs.AddNew
rs!ModelID = lngModelID
rs!SerialNo = lngFirstSerialNo + lngI
rs.Update
Next
rs.Close
Set rs = Nothing
End Function

The alternative would be to either execute an Append query statement 50
times in a loop, or to create a Cartesian Product that generates 50 copies
of the entry in the Append query statement.

 

[Kurtb4 via AccessMonster.com]

Hi Allen,
Thanks for the information. I will try your suggestions. With intermediate
Access experience I'll need a good back bone for building applications.

I'm sure I'll be a regular member and your support will be appreciated.
Thanks again,
Kurt Boettcher