Copy record, view new record, close old record

[constableparks]

User clicks a button on a form and it creates a copy of the record with a new quote number. This code works perfectly, but I want to close the original record and keep the new record on the form. How?

'This will copy the current quote into a new quote number
Dim intNewNum As Integer

On Error GoTo Err_Click

If MsgBox("Are you sure you want to create a new quote using this quote as a template?", vbYesNo, "Confirm New Quote") = vbNo Then Exit Sub

intNewNum = Nz(DMax("QuoteNum", "[TblSys]")) + 1 'Gets the new Quote Number
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy

DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Me.QuoteNum = intNewNum 'Assigns the new Quote Number to the copied record
DoCmd.RunCommand acCmdSave

Exit_Click:
Exit Sub

Err_Click:
MsgBox Err.Description
Resume Exit_Click

 

[pbaldy]

I haven't used that particular method to move to a record, but I'd expect it to leave you on the newly created record. If it doesn't, try

DoCmd.GoToRecord , , acNewRec

instead of

DoCmd.RunCommand acCmdRecordsGoToNew

 

[constableparks]

The code that I have above does leave me on the new record, but it also allows the user to click the record selector on the form to go back to the original record. I would like the original record to clear out of the form.

Or maybe I should just clear out the "Customer" field in the new record to make it more obvious that they are working on the new record now.

 

[pbaldy]

Ah, I misunderstood. The only ways to do that I can think of offhand are:

1. Close and reopen the form in data entry mode
2. apply a filter to the form
3. change the recordsource of the form

2 and 3 would use the record number as a criteria. 1 may be simplest for you. If the copy/paste doesn't work in that scenario, you can copy the cell contents into variables and then put them back.

 

[missinglinq]

I think you really need to clarify the statements below:

...Copy record, view new record, close old record...

...I would like the original record to clear out of the form...

Are you talking about Deleting the original Record from the Form?

Or something else?

Linq ;0)>

 

[constableparks]

I think you really need to clarify the statements below:



Are you talking about Deleting the original Record from the Form?

Or something else?

Linq ;0)>

Not deleting...closing the original recordset so that it is no longer visible in the open form.

>Quote #500 is open on the form.
>User clicks the "Duplicate Quote" button
>New quote #501 (duplicate of #500) is created and is displaying in the form.
>Now I have both quotes open on the form. I only want to see the new quote on the form. [So my question is: how do I close the original quote on the form and keep the new quote as the active recordset of the form?]

 

[pbaldy]

Did you see the thoughts in post 4?

 

[constableparks]

Did you see the thoughts in post 4?

Yep - saw them. I like #2 but I'll need to dig into that a bit and figure out how to filter a query in vba. Thanks pbaldy.

 

[pbaldy]

Well, you're not filtering a query, you're filtering the form's source, whatever it is.

Me.Filter = "FieldName = " & Me.TextboxName
Me.FilterOn = True

 

[constableparks]

Much appreciated - thanks Paul.

 

[constableparks]

Thanks, everyone, for your help. Here is the final solution for anyone who is keeping track:

'This will copy the current quote into a new quote number
Dim intNewNum As Integer
On Error GoTo Err_Click

If MsgBox("Are you sure you want to create a new quote using this quote as a template?", vbYesNo, "Confirm New Quote") = vbNo Then Exit Sub

intNewNum = Nz(DMax("QuoteNum", "[TblSys]")) + 1 'Gets the new Quote Number
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Me.QuoteNum = intNewNum 'Assigns the new Quote Number to the copied record
Me.Description = "Enter the new description"
Me.CustomerName = "Enter the new Customer Name"
DoCmd.RunCommand acCmdSave
Me.Filter = "QuoteNum = " & intNewNum
Me.FilterOn = True

Exit_Click:
Exit Sub

Err_Click:
MsgBox Err.Description
Resume Exit_Click

 

[pbaldy]

Glad you got it working.