Results 1 to 2 of 2
2004-03-27, 22:30 #1
- Join Date
- May 2002
- Patagonia Region Chile
- Thanked 0 Times in 0 Posts
Append duplicate record based on quantity field (A2k )
Good evening all.
I have three tables. tblorders, tblorder_detail and tblproduction. tblorders and tblorder_detail are normal master detail type tables (1 order to many ordered items.)
tblproduction has a unique record for every item that needs to get produced. If tblorder_detail has a record for big green widgets then tblproduction needs a record for big green widgets.
NOW... if the quanity on that single record in the tblorder_detail is 3 then tblproduction needs to have 3 records in it. That way 3 big green widgets get produced. Each big green widget takes on a life of its own in our production system so each one needs a single record even if they are identical big-green widgets.
It seems I need an append query that looks through the tblorder_detail and appends records to tblproduction. The query somehow looks at quantity and adds that number of new records.
tblproduction has an autonum field and a serialnum field. The serial num is the primary key and is a concatenation of some date info (month and year), the model number of the item to be produced and and the autonum field. The usage of that autonum field would keep the serial number unique for several identical entries of the biggreenwidget coming from the same order detail record. tblproduction it also has a field taken from the tblorder_detail.ID which could be a comparison field as the query appends. that id should repeat 3 times if the quantity was 3. This seems key to confirm quantity is 3 then number of record is 3.
Sounds like what I would call a LOOP. If I said LOOP, that would imply that I actually knew what a LOOP was... which I don't really.
When to trigger this append may be when frm_production_pipe_line is opened or maybe its a button on that form. I think I once appended I'd want to leave it even if the order_detail from whence it came was changed later.
Can anyone plumb me up on how to do this. (I know Hans can after reading a wicked post on release numbers that seemed similar but more complex) and looking at his posted database kathi.mdb. Pleae chime in if you are able.
2004-03-27, 23:30 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Append duplicate record based on quantity field (A2k )
I would put code in the After Update event of the form (that might be a subform) bound to tblOrder_Detail, to append the required number of records to tblProduction after a new record has been created in tblOrder_Detail. Since I don't know the field names in your tables, and the exact way you construct the serial number, I have made them up in the code below; you should replace them by the actual names. I have also made assumptions about data types, which may be incorrect. The code uses DAO, so you should select Tools | References... in the Visual Basic Editor and make sure that Microsoft DAO 3.6 Object Library is ticked. The code also assumes that you have already verified (for example in the Before Update event) that a valid quantity and model number have been entered.
Private Sub Form_AfterUpdate()
' Declare variables
Dim lngNumProducts As Long
Dim lngModelNum As Long
Dim lngAutoNum As Long
Dim i As Long
Dim strSerialNum As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
' Set error handling
On Error GoTo ErrHandler
' Initialize variables
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblProduction", dbOpenDynaset)
lngNumProducts = Me.txtQuantity
lngModelNum = Me.txtModelNum
' Loop to create the required number of records
For i = 1 To lngNumProducts
' Create a new record
' Get the AutoNumber
lngAutoNum = rst![ID] ' name of AutoNumber field
' Construct the serial number - adapt as needed
strSerialNum = Format(lngAutoNum, "0000000000") & _
Format(Date, "mmyyyy") & Format(lngModelNum, "000000")
' Set the serial number
rst![SerialNum] = strSerialNum
' Save the record
' And on to the next
' Clean up
On Error Resume Next
Set rst = Nothing
Set dbs = Nothing
' Inform user, then clean up
MsgBox Err.Description, vbExclamation