Clone a record?

[Fozzy9767]

I need a push button method of cloning a record. I want to give my end user a simple form to choose a record by choosing the name of an agent from a drop down, click a button which should copy that record from the underlying table and then present two text boxes that are populated from the new record for modification before saving the record.

I actually have a related table that also needs a new record copied from the relevant one to a new one, only updating the related field.

Kind of hard to grasp the how to of this. I think after they have nade a choice and click the copy button I need to store the combobox value (which is actually the related field)in a variable, copy the record from tblagent, paste append it to the same table, display the two text boxes(probably on a new form, with a finish button). Once they make the changes the finish button would save the new record, copy the record related to the original agent(stored in the variable) from tblBuyrate, do another paste append and then update the related field with the new data.

I can think through how to do some of this, but I'm not sure if this is the best way to go about this. I would welcome any suggestions. Thanks,

Ken

 

[MajP]

Ken,
Not to say you are, but my first guess is that your tables are not normalized. Your are doing a lot of copying of common information, which hints of a non-normalized structure. Can you post the relevant table fields and you rowsource of your combo box?

 

[Fozzy9767]

These tables have exactly one field in common, everything else is unrelated. The agent table holds address and other contact information while the buyrate table holds buyrate information. How is this not normalized?
Ken

 

[MajP]

From your quote:

click a button which should copy that record from the underlying table and then present two text boxes that are populated from the new record for modification before saving the record.
...
copy the record from tblagent, paste append it to the same table

This is what sound non-normalized. If you are pasting a record from tblAgent into tblAgen, and all but two fields remain the same, it may not be normalized. Again, would need to see the table.

 

[Fozzy9767]

I see now. Here is the thing. I have a record on the agent which details all his contact info. In the record are two fields which other tables are related to. One is the name, usually in this or a similar format: "CBL - Agent 179", the other is a 13 digit unique ID.

We want to create a new "group" for some agents that will have a name such as "CBL - Agent 179 QSR". All other data in this table will remain the same except the 13 digit number. The same goes for the buyrate table which is related to the name field, all entries for the new record will be the same.

It's stupid but this is the only way our processing center could find to differentiate the "QSR" type for statement printing purposes.

The 13 digit number is related to other tables which contain massive amounts of transactional data, but all my reporting keys off these two tables for headers and formats. Worse yet, the new records don't need to be created until they are needed, so in most cases they will never be needed. I just want to make it simple for the end user to do on a case by case basis.

Thanks, Ken.

 

[Fozzy9767]

As usual I am having syntax problems. Here is my code so far:

Private Sub Command6_Click()
Dim AgentName As String
Dim AgentSort As String
Dim NewAgent As String
Dim strSQL As String
Dim t As TableDef
Dim b As TableDef

AgentName = Me.AgentCombo.Value
AgentSort = Me.NewSortcode
NewAgent = Me.NewAgentName
strSQL = "Insert into t" & vbCrLf & "Select newagentlist.*" & vbCrLf & "where newagentlist.[agent code] = AgentName" & vbCrLf & "From newagentlist;"
DoCmd.RunSQL strSQL
strSQL = "Update t Set [agent Code] = NewAgent, sortcode = AgentSort;"
DoCmd.RunSQL strSQL
strSQL = "Insert into newagentlist" & vbCrLf & "SELECT t.*" & vbCrLf & "from t;"
DoCmd.RunSQL strSQL
strSQL = "Insert into b" & vbCrLf & "Select buyrate.*" & vbCrLf & "WHERE buyrate.agentcode = AgentName" & vbCrLf & "from buyrate;"
DoCmd.RunSQL strSQL
strSQL = "Update b Set agentcode = NewAgent;"
DoCmd.RunSQL strSQL
strSQL = "Insert into buyrate" & vbCrLf & "Select b.*" & vbCrLf & "from b;"
DoCmd.RunSQL strSQL

End Sub

Can somebody point out my obvious error please?
Thanks.

 

[Fozzy9767]

I think I've made some progress, but I'm stuck on this error: "Invalid use of '.', '!', or '()' in query expression
'newagentlist.*
where newagentlist.[agent code] = " & "CBL - Agent 144"'."

This is the code that gives the error:

strSQL = "Insert into t" & vbCrLf & "Select newagentlist.*" & vbCrLf & "where newagentlist.[agent code] = " & """" & AgentName & """" & vbCrLf & "From newagentlist;"

I can't see what is causing this. Any help?

Thanks, Ken.

 

[Fozzy9767]

I have reposted this to the VBA coding forum as it is no longer a form issue. Thanks, Ken.