Automatic record generation

  • Thread starter Thread starter Dangermouse
  • Start date Start date
D

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
 
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.
 
Dangermouse said:
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
Click to expand...


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
 
Back
Top