Auto Duplicate Record based on QTY (1 Viewer)

[HeavyD]

Good day folks,

I have an inventory DB that generates current inventory by comparing two tables, "Item Purchases" and "Returns".
During the form entry of Item Purchases, I would like to duplicate the record N times, where N is equal to the QTY of items on the form.

I would like to incorporate the duplication into my "Commit Record" macro.

What are the arguments for the DoCmd.CopyObject and DoCmd.SaveRecord?
DoCmd.CopyObject (DestinationDatabase, NewName, SourceObjectType, SourceObjectName)
DoCmd.Save (ObjectType, ObjectName)

Macro
Call DupRecord(QTY)
MessageBox -> "Record Saved"

Sub DupRecord(QTY) '<------ How do I pass the QTY from the Item Purchase Form to the subroutine DupRecord?
Dim i As Integer
i = 0
Do While (True)
DoCmd.CopyObject '<------ How to duplicate a multifield record into the Purchases table?
DoCmd.SaveRecord '<----- How to save the record?
i = i + 1
If i > QTY Then Exit Do
Loop
End Sub

This would be my first implementation of a subroutine. Any suggestions on implementation are welcome.
Thanks for the advice!!
Darrell

 

[arnelgp]

use Insert Query instead.
make sure your form is Unbound, otherwise it will create N+1 records.

 

[HeavyD]

I'm spinning my wheels here. Overall objective remains the same, I'm just breaking the problem down into smaller pieces.

How do I pass an integer value from a newly filled form to a subroutine.

This is just a testing subroutine. I'm trying to pass the integer value of the Quantity field to the subroutine.

The following throws an error: User-defined type not defined.

Private Sub DupeRecord(ByRef QTY As Form.Quantity)
Dim i As Integer
i = 0
Do While (True)
MsgBox "Loop"
i = i + 1
If i = QTY Then Exit Do
Loop
End Sub

Any advice?
Thanks

 

[plog]

Your Sub signature is using a data type the computer has never heard of:

Private Sub DupeRecord(ByRef QTY As Form.Quantity)

Form.Quantity needs to be a datatype (Integer, Boolean, String, etc) not a specific data input.

That's the error, but I would avoid it entirely. Don't pass any value to the Sub, just reference Form.Quantity inside the sub itself. Also remove the potential for an infite loop or instead of not running your code will never not stop:

Do While (True)

 

[HeavyD]

@plog, yes please! Tell me how.

The actual form name is SCA_Add and the Field name is InvoiceDetailActualQuantityReceived

Private Sub DupeRecord()
Dim i As Integer
i = 0
Do While (True)
MsgBox "Loop"
i = i + 1
If i = SCA_Add.InvoiceDetailActualQuantityReceived Then Exit Do '<------ this??
Loop
End Sub

 

[Pat Hartman]

This can be done by using a "tally" table. This is a table with one field that contains a number. You need a row in this table for each "quantity" you want to produce. So, if your maximum quantity is 100, you need 100 rows with the values 1-100.
Here is an example:

 

[HeavyD]

@ plog Good 'ol trial and error.

Private Sub Command134_Click()
Dim i As Integer
Dim QTY As Integer
QTY = Forms!SCA_Add!InvoiceDetailActualQuantityReceived.Value '<---- seems to work
i = 0
Do While (True)
MsgBox "Loop"
i = i + 1
If i = QTY Then Exit Do
Loop
End Sub

 

[HeavyD]

Ok, I have the loop functionality working.
Next: I need to make N new records from the same form.

@arnelgp , tell me more about this INSERT INTO

I
Public Sub Command134_Click()
Dim i As Integer
Dim QTY As Integer
QTY = Forms!Purchases!Quantity.Value
i = 0
Do While (True)
'MsgBox "Loop"
INSERT INTO Table (Val1, Val2, Val3) '<------------ This throws an error at "Table"
VALUES (Forms!SCA_Add!Val1.Value, Forms!SCA_Add!Val2.Value, Forms!SCA_Add!Val1.Value3)
i = i + 1
If i = QTY Then Exit Do
Loop
End Sub

The error is Compile Error: Expected: End of Statement

Also, how can I increment the record ID number for each new record?

 

[plog]

Again, you should never use While(true). 1--its an infinite loop, you may never come out of it even with an Exit inside it. 2--an Exit inside of it is poor coding--you should make your code clearer to understand by putting the test inside the While().

With that said, you shouldn't even use a While in this instance. You will know how many passes through the loop you need before you enter so that means you should use a for loop:

For...Next statement (VBA)

Office VBA reference topic

docs.microsoft.com

 

[Pat Hartman]

And we all know that action queries are faster than loops when working with tables

 

[arnelgp]

Public Sub Command134_Click()
    Dim i As Integer
    Dim QTY As Integer
    QTY = Forms!Purchases!Quantity.Value
    i = 0
    Do While i < QTY
        'MsgBox "Loop"
        With Currentdb.CreateQuerydef("", "INSERT INTO [Table] (Val1, Val2, Val3) " & _
            "VALUES ([p1], [p2], [p3])")
            .Parameters("p1")=Forms!SCA_Add!Val1.Value
            .Parameters("p2)=Forms!SCA_Add!Val2.Value
            .Parameters(p3")= Forms!SCA_Add!Val1.Value3
            .Execute
        End With
        i = i + 1
    Loop
End Sub

 

[Cronk]

If the table has already more than the number of records to be added, how about one line of code

Public Sub Command134_Click()  
        Currentdb.execute "INSERT INTO Purchases (Field1, Field2, Field3) SELECT TOP " & Forms!Purchases!Quantity " & _
              Forms!SCA_Add!Val1 & ", " & Forms!SCA_Add!Val2 & ", " &  Forms!SCA_Add!Val1.Val3 & " FRom Purchases"
End Sub

 

[HeavyD]

@arnelgp
Well sir. That would have taken me a long time to do.

Thank you very much for the bump in the right direction.

 

[Pat Hartman]

I know when you're working with 10 records nothing you do matters. but creating a query, saving it, and running it for every row you want to duplicate is a little over the top. That should beat the VBA loop for slowness hands down.

 

[HeavyD]

New snag, same thread.

One of the fields I am trying to duplicate is an attachment field to a PDF. I learned quickly that this can't be done. In addition, duplicating PDF's for every record will quickly increase the size of the DB to undesired proportion. I explored using OLE instead, but I have gathered that it is not the preferred method due to file size/bloat over time. I also considered external hyperlinks, but don't want to rely on local file structures for functionality. The folder where the PDFs are archived could move or be changed by anyone.

Proposed solution:
A new table that contains only PDF attachments. The main DB will have a hyperlink field to the respective PDF in the PDF only table. One PDF attachment, many copied links to that attachment.

High level overview:

Purchases Table
Record 1 --->[PDF Table]!Record 1
Record 2 --->[PDF Table]!Record 1
Record 3 --->[PDF Table]!Record 1

PDF Table
Record 1 (PDF Attachment)

Entry Form
A command button to:
- Add PDF attachment to [PDF Table].
- Link [Purchases Table]!Record # to [PDF Table]!Record 1.
- Copy [Purchases Table]Record # N times, where N is equal to QTY.

Questions:
The Entry Form is associated with the Purchases Table. When trying to add the PDF attachment field to the form associated with the Purchases table, a Relationship dialogue box opens up. I can understand that the relationship will be one to many.

What should the datatypes be on the two tables? PDF Table=> attachment. Purchases Table=> attachment or hyperlink?

Any other general advice?
Thanks.

 

[Pat Hartman]

No solution that uses Jet/ACE to hold objects will work for very long.
The attachment data type is not supported by SQL Server so if you are using it with Jet/ACE and you convert to SQL Server, you will also need to rebuild your forms.

I use path references all the time. To make the feature more stable, the paths are on the server and the high level part is saved separately from the file name. Therefore, if the path has to change, it can be done with one update using one form. For example, I have an application that supports benefits audits for multiple companies so the high level path is \\servername\AuditDocs\xxx Where xxx is the companyAbbreviation and that is stored with each company record. The first part is stored with the general setup data and the actual file name is stored in a record related to an employee of the parent company.