How Can I: Create a Continuation Recrod?

[RichardAnderson]

My database is basically a production schedule. 3 of the fields are QuantityOrdered, QuantityMade, and QuantityRemaining. If a record is not build completely, meaning 100 was ordered but we could only make 95, Then I need a new record to be entered automatically duplicating every field under a new ID.



I know you are going to ask WHY?

Well, the completed 95 will ship right away and the shipping info for the first shipment entered. When the remaining 5 are made and shipped, They will have different shipping info so will need to be on a separate record.

[Missinglinq]

Assuming that the QuantityRemaining on the original Record becomes the QuantityOrdered, in the 'continuation record,' something like this, behind a Command Button:

Private Sub cmdContinuationRecord_Click()

'Copy fields to variables
MyQuantityOrdered = Me.QuantityRemaining
MySecondField = Me.SecondField
MyThirdField = Me.ThirdField

'Go to a new record
DoCmd.GoToRecord , , acNewRec

'Reverse the process and plug old values into new record
Me.QuantityOrdered = MyQuantityOrdered
Me.SecondField = MySecondField
Me.ThirdField = MyThirdField

End Sub

Linq ;0)>

[RichardAnderson]

I will give this a try. I assume that I need to change Me.SecondField to Me.Status and so on?

Does the button go in the row with the record?

Will the button know what record to copy?

Also, Do I need to list these fields in the same order as the tab order when I am pasting in the new record?

[Missinglinq]

I assume that I need to change Me.SecondField to Me.Status and so on?

Yes.

Does the button go in the row with the record?

What kind of Form is this, i.e. Single View, Datasheet View or Continuous View? The type of Form determines how you implement this kind of thing. With a Single View or Continuous View Form you can place a Command Button on the Detail Section and run the code from the OnClick event of the button. On a Datasheet View Form Command Buttons are not allowed, so you'd need to run the code from the OnDoubleClick event of one or more Textboxes.

Will the button know what record to copy?

Yes.

Do I need to list these fields in the same order as the tab order when I am pasting in the new record?

No.

Linq ;0)>

[RichardAnderson]

Hello Linq ... You have been informative to say the least. I went to add the button and got a popup wizard. One option was was a duplicate record button. I rolled with that and it duplicated the record exactly. I guess I could do it this way and just have the use change the information needed. The fields that would need changed would be....

QuantityRemaining on the current record would become the new records QuantityOrdered
QuantityMade on the new record would need to become empty
And Completed on the current record would become the EnteredDate ​on the new record.

[Missinglinq]

...I need a new record to be entered automatically...

You could do that, but having the user re-enter/change fields isn't what I'd call 'automatically.'

Linq ;0)>

[RichardAnderson]

OK ...

I am getting an error.


So you can copy, this is what I have...

Private Sub cmdContinue_Click()

'Copy fields to variables
MyDateEntered = Me.Completed
MyItem = Me.Item
MyQuantityOrdered = Me.QuantityRemaining
MyQuantityRemaining = Me.QuantityRemaining
MyCustomer = Me.Customer
MySalesOrder = Me.SalesOrder
MyShipTo = Me.ShipTo
MyNotes = Me.Notes


'Go to a new record
DoCmd.GoToRecord , , acNewRec


'Reverse the process and plug old values into new record
Me.DateEntered = MyDateEntered
Me.Item = MyItem
Me.QuantityOrdered = MyQuantityOrdered
Me.QuantityRemaining = MyQuantityRemaining
Me.Customer = MyCustomer
Me.SalesOrder = MySalesOrder
Me.ShipTo = MyShipTo
Me.Notes = MyNotes


End Sub

[Missinglinq]

You've got Option Explicit at he top of the code module, which means that you have to explicitly diminish each Variable! The Variables, in this case, are all of the 'My' names, i.e. MyDateEntered, MyItem, etc.

Immediately under the line

Private Sub cmdContinue_Click()

you need

Dim MyDateEntered As Date

Dim MyItem As String

and so forth, telling Access the Datatype that will be assigned to each given Variable.

Linq ;0)>

[RichardAnderson]

First I want to say that I am sorry for the long delay in getting back on this issue...

This is working great except for one thing....

When a field that is not required is blank, then an error occurs. I noticed this when testing with a black Notes field.

Here is my code.

Private Sub cmdCarryOver_Click()

'Set Variables
Dim MyDateEntered As Date
Dim MyItem As String
Dim MyQuantityOrdered As Integer
Dim MyCustomer As String
Dim MySalesOrder As String
Dim MyShipTo As String
Dim MyNotes As String




'Copy fields to variables
MyDateEntered = Me.Completed
MyItem = Me.Item
MyQuantityOrdered = Me.QuantityRemaining
MyCustomer = Me.Customer
MySalesOrder = Me.SalesOrder
MyShipTo = Me.ShipTo
MyNotes = Me.Notes




'Go to a new record
DoCmd.GoToRecord , , acNewRec




'Reverse the process and plug old values into new record
Me.DateEntered = MyDateEntered
Me.Item = MyItem
Me.QuantityOrdered = MyQuantityOrdered
Me.Customer = MyCustomer
Me.SalesOrder = MySalesOrder
Me.ShipTo = MyShipTo
Me.Notes = MyNotes