Results 1 to 2 of 2
  1. #1
    Star Lounger
    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.



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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
    Next i

    ' Clean up
    On Error Resume Next
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ' Inform user, then clean up
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts