Need help with multiple duplicate records from a form button

[markarmer]

I have two tables, one named tlkpBatch, the other named Animals.



When I buy new chickens I use a form with two text boxes called txtBatchName and txtQTY - In the batch name I type in a value such as 201110419-WhiteLayers (the numbers are the date, then a simple description), and in the txtQTY box I type in the number of birds that are in this batch.

I also have a form for the animal table where I type in information about the individual birds, this has multiple fields such as the hatch date, breed & vendor etc... and use a combobox which pulls from the tlkpBatch table so I can assign the specific animal to that batch.

Finally, at the bottom of this page I created a command button named cmdDuplicate so when I have entered the information for one of the new animals I can click that and it duplicates the record using a macro from the button wizard in Access 2010.

This all works perfectly and does exactly what I need HOWEVER, often my batches are of 100 to 200 birds, so sitting there clicking the button 200 times gets a little tedious.

Is there any way to automate this so clicking the button once will automatically create the right number of duplicates based on the QTY in my tlkpBatch table?

A real life example being I received 75 chickens yesterday, created my batch name & put 75 in the QTY field, saved it then opened up my animals form, created a new record with all of the information about one of the specific animals in then sat and clicked the duplicate button 74 times to get my birds entered, it would be great to have the button take my QTY of 75, know I just created one record and duplicate it 74 times.

Thank You very much in advance for anyone willing to help with this.

[pbaldy]

One option is this type of thing:

http://www.baldyweb.com/MultiselectAppend.htm

but instead of looping a listbox you use a For/Next loop that takes the upper number from your quantity textbox.

[jzwp11]

If within a batch there are many birds that have the same parameters (hatch date, breed & vendor ) then you should have groups within a batch and have 1 record in that table that defines that group. Then from there have your specific birds in a related table. How you define a "group" depends on your application. You might choose to define a group by breed or by hatch date or by vendor; you'll have to decide.


tblBatch
-pkBatchID primary key, autonumber
-BatchNo
-dteBatch (I would have a separate date field rather than combining it in the batch no, you can always combine the date with whatever you have in the batchno field to display something like this 201110419-WhiteLayers; having a separate date field will allow for easier searches)

I assumed, just for illustration purposes, to separate a batch by breed since that seem logical to me.

tblBatchBreeds
-pkBatchBreedID primary key, autonumber
-fkBatchID foreign key to tblBatch
-fkBreedID foreign key to a table that holds all possible breeds

tblIndividuals
-pkIndID primary key, autonumber
-fkBatchBreedID foreign key to tblBatchBreeds
-IndvIDNo
-dteHatch (hatch date)

As to the vendor, does 1 vendor supply the entire batch? If, so then you should have a vendor field in the tblBatch. If multiple vendors are involved, then it will depend on how the vendors typically supply the batch (by breed?).

[markarmer]

That does look like what I need to do - do you have a minute to show how I would edit the code? I just tried hacking it up to apply to my form but made no progress

[pbaldy]

What have you got so far?

[markarmer]

Well, as you will see I am a complete newbie and am still getting my head around this, in fact, this is the first database I have ever built... here is what I have so far based on your link...

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("Animals", dbOpenDynaset, dbAppendOnly)
'add selected value(s) to table
Set ctl = Me.QTY
For Each varItem In ctl.txtQTY
rs.AddNew
rs!Animals_ID = ctl.ItemData(varItem)
rs!Animal_Batch = Me.txtBatch
rs.Update
Next varItem
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select

[pbaldy]

Well, you're treating the textbox like a listbox. You would replace the parts related to the listbox and use a For/Next loop based on the textbox. Relevant changes include:

Dim x As Integer

For x = 1 To Me.txtQTY
'code to add a record here
Next x

[markarmer]

Ahh.. Almost there now - It is working in that now clicking the button creates the correct number of records, the only problem is that they are all empty records not duplicates. Here is what I have for the button code now:

Private Sub addbatch_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim x As Integer
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("Animals", dbOpenDynaset, dbAppendOnly)
'add selected value(s) to table
Set ctl = Me.txtQTY
For x = 1 To Me.txtQTY
rs.AddNew
rs.Update
Next x
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub

[pbaldy]

In between these lines:

rs.AddNew
rs.Update

you need lines that add values to fields, like you had before:

rs!Animal_Batch = Me.txtBatch

[markarmer]

In between these lines:

rs.AddNew
rs.Update

you need lines that add values to fields, like you had before:

rs!Animal_Batch = Me.txtBatch

That Was It! - Thank you so VERY much, you have save me hours of tedious work.

Mark

[pbaldy]

Happy to help Mark!