Duplicating a specific record button

[ECEK]

My main form is a split form.
I wish to add a command button on the "single form" part that duplicates that record and opens a pop-up form containing the duplicated record.

As I see it:

I append all of the data from the main form into a "holding table" using the "Unique" ID as my link.

I edit my duplicate then append it back into the main table (minus the old ID - The table will create that for me)

I can open a sub-popup-form using ="[ID]=" & [ID] What Im looking to do is the same thing but a new holding table.

I have always struggled to get data from a form into another table.

Your help in this process is always appreciated.

 

[ECEK]

Would it perhaps be easier to open a sub form of the specific data and have the duplication button on that form?

 

[Minty]

Have a look at this thread - it is very similar to your question.
http://www.access-programmers.co.uk/forums/showthread.php?t=289296

 

[ECEK]

Ive tried the following code but to no avail

Private Sub Command124_Click()
sSqlString = "INSERT INTO tbl_cases_pending_new_hld (np_branch, status,consultant,para) "

End Sub

Im not brilliant at vba

I just want to append all of the data on my form into a new holding table. I can do the rest from there

 

[Minty]

Okay - you need the complete syntax for an insert and you then need to execute it; The bits in red you will need to alter to suit your table

Dim sSQL As String
Dim lngNewRow as Long
Dim db As Database

Set db = CurrentDB 

sSQL = "INSERT INTO [COLOR="Red"]YourTableName [/COLOR]"
sSQL = SQL & "SELECT [COLOR="red"]AllYour fields, CommaSeperated, gohere, except the primary key [/COLOR]FROM [COLOR="red"]YourTableName [/COLOR]"
sSQL = SQL & "WHERE [COLOR="red"]YourPrimaryKey [/COLOR]= " & [COLOR="Green"]Me.TheFomControlNameWithYourPrimaryKey[/COLOR] & " ;"

db.Execute (sSql)
lngNewRow = db.OpenRecordset("SELECT @@IDENTITY")(0)   ' [COLOR="Green"]lngNewRow will contian the new PK ID for you to do funky things with...[/COLOR]
Set db = Nothing

 

[ECEK]

Minty: Many thanks. I'll give this a try next week and report back. Have a great weekend.

 

[ECEK]

Morning Minty. Hope you had a great weekend?

Well I didn't get past the third line !! and its only 9:12 am !!!

Compile error:
Expected user-defined type, not project

I created a module and inserted this:

Function Test()
   Dim db As DAO.Database
End Function

I changed the line:

Dim db As Database

to

Dim db As DAO.Database

I'm getting my self into a right mess.

This is my current code:

Private Sub cmd_duplicate_01_Click()
Dim sSQL As String
Dim lngNewRow As Long
Dim db As DAO.Database

Set db = CurrentDb

sSQL = "INSERT INTO tbl_cases_pending_new_hld "
sSQL = SQL & "np_branch,rep_status,so_ref,consultant,para,client,clientref,rec_type,date_fr,drc FROM tbl_cases_pending"
sSQL = SQL & "WHERE tbl_cases_pending.ID = " & Me.ID & " ;"

db.Execute (sSQL)
lngNewRow = db.OpenRecordset("SELECT @@IDENTITY")(0)   ' lngNewRow will contian the new PK ID for you to do funky things with...
Set db = Nothing
End Sub

It errors saying:

The Microsoft Access database cannot find the input table or query 'WHERE tbl_cases_pending.ID = 1;'. Make sure it exists and that its name is spelled correctly.




Can I ask for your assistance in just getting one field [ID] into a table. I can then create an append query to a holding table (user edits) then append back into the main table.

So to recap:

I have my main data.
Press to view one record.
Press to append THAT records ID to a holdingtable1.
Then Append ID with linked data into holdingtable2.
User edits holdingtable2
Confirm to Append back into main table.

What could posssibly go wrong !!

 

[Minty]

Right - you are referencing tbl_cases_pending as the ID but it doesn't exist in your query.
My code will duplicate a record into the same table.

Having read your description I think you are over complicating things with the holding tables, I think they are unnecessary. The easiest route to achieve your goal would be to load the existing record onto a unbound form, make the edits you require, then save (append) the data from the unbound form into your main table. If they don't press save the new record isn't created.

Does that make sense?

 

[ECEK]

Yes it makes sense totally. (As I understand it : It's doing it my way but having the data virtually (on the new form) as opposed to using holding tables) The principle is the same though?

I feel that I would get lost using VBA (unless it's effectively done for me)

So as I see it then:

I have my main data.
Press to view one record.
Press to send THAT records data to virtualform1.

User edits virtualform1.

Confirm to send virtualform1 data into main table.

I understand I just can't seem to get going with the VBA.

 

[Minty]

Break it down in to small manageable steps.

1. Create the unbound form for the duplicate / or change your existing form to manage all the work (This is a harder and more difficult, but probably a more streamlined approach)
2. Assuming a new unbound form, create the code to load the record to be duplicated.
3. Create the code to save (append) the new record back to the main table.

Unbound forms require additional work, as you have to load the control contents individually. Once done though it's easy to manoeuvre you way around doing other things with the data.
So initially, you will have to create a recordset with the existing data, then populate the controls.

You can do this using an OpenArgs() method on the new form, passing the existing record ID onto the form to get the correct data into the recordset.

All of these methods are demonstrated here, but if you get stuck then post the question here and show us what you have done so far. To do this sensibly you will have to get stuck into VBA coding. It's easier than you think once you wade in.

 

[ECEK]

Significant steps:

I now have my duplicate button on my main form and can populate my "virtual" form [frmVirtual01] with specific data. This is what is behind the duplicate command button:

Private Sub Command126_Click()
If Me.NewRecord Then 
    DoCmd.OpenForm "frmVirtual01"
  Else 
    DoCmd.OpenForm "frmVirtual01", , , , , , Me.ID & ";" & Me.np_branch & ";" & Me.rep_status
  End If
End Sub

I also have this code set OnLoad on my frmVirtual01 form.

Private Sub Form_Load()
  Dim strOpenArgs() As String

  If Not IsNull(Me.OpenArgs) Then
    strOpenArgs = Split(Me.OpenArgs, ";")
    Me.txtID = strOpenArgs(0)
    Me.txtnpbranch = strOpenArgs(1)
    Me.txtrep_status = strOpenArgs(2)
  Else
    Me.txtOtherInfo = "Unknown"
  End If
End Sub

If you can give me any pointers as to how I get my data from "frmVirtual01" back into my table it would be greatly appreciated.

 

[Minty]

Okay I would create an Insert SQL statement

Dim sSql as string

sSql = "INSERT INTO YourTable (Field1, Field2, etc) "
sSql = sSsql & "VALUES ([COLOR="red"]'[/COLOR]" & Me.txtField1 & "[COLOR="red"]'[/COLOR] , [COLOR="Red"]'[/COLOR]" & Me.txtField2 & "[COLOR="red"]'[/COLOR] , etc "

Debug.Print sSql

Currentdb.Execute sSql, dbFailOnError

I would add that you need to ensure text fields are delimited in single quotes (as per red bits above). Any date fields need to have # around them and be in mm/dd/yyyy or yyyy/mm/dd format . Allen Browne has a function you can use to get around having to format dates every time - http://allenbrowne.com/ser-36.html

The Debug will print what string you are forming before executing it in the immediate window in the vba window. This will assist you greatly when it doesn't work first time(trust me it won't) you are bound to get a typo or field name a bit wrong or something - I still do..

 

[ECEK]

I've managed to simply create an append query using the "build feature" off the form.
Wow. Got there in the end!!
Thanks ever so much Minty.