Automatic record generation

[Dangermouse]

Hi, I am currently working within Access 2003, I have created a inventory
database, to log all of my product orders, I have created a PO form to add
the required details, but what I need to do is within the Inventory table
automatically create a number of records that is linked to the Qty of
products ordered on the PO, e.g. If I order 5 IPods, when I double click on
the Qty field, the database will open a form and generate 5 records
appertaining to that PO Number, thus allowing me to enter the serial no
etc.. of the products, into the inventory table

Thanks

 

[Jinjer]

Hi.

One way of doing it could be to put code in your double click event on the
qty control:

Private Sub YourQtyField_DoubleClick()

Dim n as integer
Dim sPO as string

n = me.YourQtyField 'order quantity
sPO = me.YourPOField 'PO number

'Insert the PO number into a table the number of quantity times.
For count = 1 to n
DoCmd.RunSQL "INSERT INTO Table1 (POfield) VALUES (" & sPO & ")"
next count

'After this code has run, open a form that has as its record source a query
from this table with the PO number as its critera.

DoCmd.OpenForm "YourPONumberForm"

End Sub


Hope this helps.

 

[Marshall Barton]

Hi, I am currently working within Access 2003, I have created a inventory
database, to log all of my product orders, I have created a PO form to add
the required details, but what I need to do is within the Inventory table
automatically create a number of records that is linked to the Qty of
products ordered on the PO, e.g. If I order 5 IPods, when I double click on
the Qty field, the database will open a form and generate 5 records
appertaining to that PO Number, thus allowing me to enter the serial no
etc.. of the products, into the inventory table

Another way would be:

Dim db As Database
Dim rs As Recordset
Dim N As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("Inventory")
For N = 1 To Me.quantity
rs.AddNew
rs!PoNum = Me.txtPoNum
rs!productID = Me.txtProductID
. . .
rs.Update
Next N