Create Consecutive Records

[LadyDi]

I have created a database for my co-workers to enter information regarding orders. My co-workers have mentioned that sometimes, a customer will submit 20 to 30 some orders all containing the same information. They would like a feature to be added to the database that will enable them to, in essence, copy the first order 20 or 30 times instead of needing to enter all the information manually that many times. I tried using an append query, but that did not suit their fancy because they would have to click the button to run the query 20 plus times to get the right number of copies. Is there any way they can tell the database that they want 20 copies made, press a button, and the database will make the copies? The primary key of the database is an AutoNumber, I don't know if that could cause problems with a feature like this or not. Any suggestions?

 

[Luddite Lad]

You could have a pop up to record the number of orders the user wishes to add, and then modify your button to loop through your append query X time.

I would think carefully on the consequences going down this path though. ie. accidentally creating excess blank orders. I'm presuming that the users want to create X number of identical order headers and then come back and populate the body of each order as required.

 

[missinglinq]

You don't really need to run 20 Append Queries. This bit of code will do it:

Private Sub TwentyCopies()
For I = 1 To 20
  DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdCopy
  DoCmd.GoToRecord , , acNewRec
  DoCmd.RunCommand acCmdPaste
Next I
End Sub

You could modify it to use the Lad's suggestion. Replace the 20 with a variable entered by your users.

 

[Mike375]

The attached has two simple macors with Macro5 running Macro4. You can convert them to code.

[Forms]![MasterForm]![Text1526] is referring to unbound textbox 1526. The number of times to run is entered in the textbox and each time it runs that value is reduced by 1 and the macro stops when it reaches 0 And can't be run while 0 is in the box or if the box is null.

I would also have a reverse type situation in case the buyer places 20 orders and then changes it to 15 while on the phone with the person.

Exercise caution as Luddite Lad has suggested. I have a similar situation and a tabular form opens (after the appending) showing the new records and then the relevant detail for each record is entered.

 

[ajetrumpet]

You don't really need to run 20 Append Queries. This bit of code will do it:

Private Sub TwentyCopies()
For I = 1 To 20
  DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdCopy
  DoCmd.GoToRecord , , acNewRec
  DoCmd.RunCommand acCmdPaste
Next I
End Sub

You could modify it to use the Lad's suggestion. Replace the 20 with a variable entered by your users.

i think this is best. awesome hint. unbelievable how simple something like this is, isn't it?

 

[LadyDi]

Thank you all for your suggestions. Now, I have another question. I am trying to use this code

Private Sub TwentyCopies()
For I = 1 To 20
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Next I
End Sub

However, I am running into a problem. The code will copy the record once and then it takes me back to the first record on the form and gives me an error message the a certain field needs to be filled in. I have a couple fields in the form that are only visible when certain options are selected. On the record I am trying to copy, that field is not visible and therefore does not need to be filled out. However, it is visible on the first record on the form and it is filled out on that record. When I tell that message OK, I get another message the states "The value you entered isn't appropriate for the input mask "I" specified for this field." I simply copied this code and pasted it in my database. The only thing I changed, was I changed the 20 to a 10. What do I need to do to get this to work?

I think perhaps some things are not quite as easy as they look.

 

[DCrake]

Simple Software Solutions

In order to run the code supplied you must first save the first record before you use the line

DoCmd.RunCommand acCmdSelectRecord

Even though the user has entered the record onto the form it will not be commed until the user moves the record pointer to a different record or a new record.

It may be a bit more long winded but I would have the tendancy to add the repeat records using Recordsets and .AddNew .Update within a For Next loop.

Also to be on the safe side I would also have the user enter the quantity twice to confirm the number of repeats. This would validate any transpositions or incorrect values. 200 instead of 20; 19 instead of 91, etc.

Caution is a valuble asset to behold.

CodeMaster:

 

[LadyDi]

I'm afraid, I'm not very proficient at writing code as of yet. How should the code read? Can you have parameters in the code, like you have in queries? Should the code read something like this?

Private Sub TwentyCopies()
For I = 1 To [Number of Copies to be Made]
DoCmd.AddNew
DoCmd.Update
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Next I
End Sub

Or do I need to have an unbound text box on the form where the user enters the number of copies to be made, so the code would read something like this?

Private Sub TwentyCopies()
For I = 1 To Me.Text12
DoCmd.AddNew
DoCmd.Update
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Next I
End Sub

 

[missinglinq]

First, you have to remove these two lines

DoCmd.AddNew
DoCmd.Update

and use the code I posted. The AddNew and Update DCrake mentioned is an entirely different way of tackling this problem, and his saying it's a "bit more long winded" is the understatement of the century! It's just a way to complicated approach to this simple a problem.

If you're entering a new record and want to copy it, before you save it, you can add this line of code to the beginning of the sub:

If Me.Dirty Then Me.Dirty = False

which will force a Save, so the code would then be

Private Sub TwentyCopies()

If Me.Dirty Then Me.Dirty = False

For I = 1 To Me.Text12
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Next I
End Sub

You can do it either way, with or without the Text12. But if you use the one with Text12, you need to do something to insure that it will always have a value in it, or you'll pop an error if it's left empty. What I would do is to give the textbox a Default Value of, say ten or twenty, whatever you think the usual number of copies will be, then the user can always change it if they want to make more or less, but you'll never error out.

 

[LadyDi]

I'm afraid, even with the Me.Dirty line added to the code, I am still getting that same error message. -- The code will copy the record once and then it takes me back to the first record on the form and gives me an error message the a certain field needs to be filled in. I have a couple fields in the form that are only visible when certain options are selected. On the record I am trying to copy, that field is not visible and therefore does not need to be filled out. However, it is visible on the first record on the form and it is filled out on that record. When I tell that message OK, I get another message the states "The value you entered isn't appropriate for the input mask "I" specified for this field." Then the debugger highlights the DoCmd.RunCommand acCmdSelectRecord line-- I put the code on the On Click Event of a button. This is exactly how it looks in my database:

Private Sub Copy_Click()
If Me.Dirty Then Me.Dirty = False
For I = 1 To 10
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Next I

End Sub

What should I do now?

 

[DCrake]

Simple Software Solutions

customer will submit 20 to 30 some orders all containing the same information

When they submit the order then surely each order will have its own purchase order number? how do you intend to validate this item?

if they all come on the same order number why not sum the quantity and add just one order?

Confused?

 

[LadyDi]

Each order does have it's own order number, that is what the AutoNum on the database is for. Usually, the orders will all be identical with the exception of the ship to address. The orders will all have the same part numbers, dates, customer, etc. They will also have to be entered into the ordering system individually. We want to use the database to keep track of the orders that are entered and any errors that might be on the order form. Therefore, we do not enter the ship to address on the database, but we do need the orders entered individually so that we have an accurate number of orders to report at the end of the month.

 

[missinglinq]

The code is tried and tested, so apparently you have something else going on that's interfering. The fact that it's returning to the first record would indicate, for instance, that you have a requery going on somewhere; this is not done by the copy code. When the code runs successfully, focus remains on the last copied record.

gives me an error message that a certain field needs to be filled in.

Do you, in fact, have required field(s)?

I have a couple fields in the form that are only visible when certain options are selected. On the record I am trying to copy, that field is not visible and therefore does not need to be filled out.

Try copying a record that has all fields visible, and see if that makes a difference.

OK, I get another message the states "The value you entered isn't appropriate for the input mask "I" specified for this field."

This would seem to indicate that, for the current record, you've done just that, entered data that doesn't comply with the Input Mask. Do you have an Input Mask on any textboxes?

 

[LadyDi]

I tried using this code on a record where all fields are visible. Now I am getting a different error message. This one states "Some of the field names for the data you tried to paste don't match field names on the form. Microsoft Office Access treats data in the first row on the Clipboard as field names. In this case, some of those field names don't match the field names on the form. Do yu want to paste only that data whose names match the field names on the clipboard?" How do I solve this problem?

 

[missinglinq]

"Some of the field names for the data you tried to paste don't match field names on the form. Microsoft Office Access treats data in the first row on the Clipboard as field names. In this case, some of those field names don't match the field names on the form. Do you want to paste only that data whose names match the field names on the clipboard?" How do I solve this problem?

To be honest, I have no idea, unless you've got a corrupted form. This error message sounds as if it's coming from an Excel app instead of Access, where the first row can be either a field title or actual data. Hopefully someone else can come along and shed some light on this.

Two questions:

Can you zip up your database and post/attach it here, along with the version it was created in?

How many fields are we talking about here? I have another method of doing this, but you have to re-assign each textbox value individually.

 

[LadyDi]

There are 13 fields on the database plus two subforms with three fields each. I'm afraid, I cannot post the database here because it is split and set up to automatically update. It was created in Access 2003.

 

[missinglinq]

There are 13 fields on the database plus two subforms with three fields each. I'm afraid, I cannot post the database here because it is split and set up to automatically update. It was created in Access 2003.

Oh, ho! Subforms! You made no mention of subforms before! My guess is, and it is just a guess, is that this is the source of your troubles! I seldom use subforms; my area of practise, in health care informatics, simply seldom need them. Hopefully someone else who does use them extensively will come along and help you. It is, I suspect, going to be difficult, however, since no one can actually see your form/subforms and how they're set up. Sorry!