Copy the subform details to a new record in a single form view

[mvital]

Hi All,

I have been wrestling with this request for a couple of weeks. I have a single form view form in Access 2003. I have been asked to make it so that the user can duplicate (copy, ditto) a record in the details section of the form (subform). I found Allen Browne's code and some example code MS had too, but both of the examples are helpful, but my form is sort of unique. So I would like to brainstorm with you all. My form is unique in that they PK (primary key) is NOT an autonumber. It is a composite PK of just text fields. Their is also no pattern as to how the primary keys are assign - hope that makes sense. They can be any text, but just unique to the other records.

So, my question. Can you ditto (copy, duplicate) a record in the details section (subform) to a new record? I know I could easily do this by changing the design view to datasheet. I have other functionality on the subform (search list box) and w/ a datasheet view I would not be able to have it.

So this is where I need your help. How do I go about doing this? Should I have the user click on a button that takes them to another form where they make the selection?

Any ideas? Can this be done?

thanks in advance!

 

[Crowley16]

can you can use the subforms recordset object?

--------------------
Procrastinate Now!

 

[mvital]

I tried the recordset clone and it is not working just right either.

Thanks!

 

[TheAceMan1]

How are ya mvital . . .

In the Tag property of the textboxes to be transferred add a qestion mark [purple]?[/purple].

In the subforms [blue]On Dbl Click[/blue] event, copy/paste the following code ([blue]you![/blue] substitute proper names in [purple]purple[/purple]:

[blue]   Dim rst As DAO.Recordset, ctl As Control
   
   Set rst = Me.RecordsetClone
   rst.FindFirst "[[purple][b][i]YourPKname[/i][/b][/purple]] = '" & Me![purple][b][i]YourPKname[/i][/b][/purple] & "'"
   If Not Me.NewRecord Then DoCmd.RunCommand acCmdRecordsGoToNew
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Trim(rst(ctl.Name) & "") <> "" Then
            Me(ctl.Name) = rst(ctl.Name)
         End If
      End If
   Next
   
   Me!YourPKname = [green]'However you supply PK! . . .[/green]
      
   Set rst = Nothing[/blue]

Supplying your PK is up to you! . . .

To transfer a record to new just [blue]double-click its record selector[/blue] . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[mvital]

Thanks TheAceMan1 for helping me again! I'm not sure what this code would be doing. How does the new record get created? By just double clicking in the combo box control? Not sure what you mean by however you supply your PK?

Double click record selector?

Sorry for all the questions.

 

[TheAceMan1]

[blue]Can you ditto (copy, duplicate) a record in the details section (subform) to a new record?[/blue]

This is what the code does. By [purple]double-clicking[/purple] on [purple]the record selector[/purple] of an existing record in the subform, [blue]a new record is created[/blue] with those values.

[blue]How does the new record get created?[/blue]

Here's the code with comments:

[blue]   [green]'Declare variables[/green]
   Dim rst As DAO.Recordset, ctl As Control
   
   [green]'Set recordset object (Clone the subforms recordset)[/green]
   Set rst = Me.RecordsetClone
   
   [green]'Move to the same record in the clone. The new record will
   'be copied from the clone. This allows the code to move
   'the form to a new record without loosing the data to be copied.[/green]
   rst.FindFirst "[[purple][b][i]YourPKname[/i][/b][/purple]] = '" & Me![purple][b][i]YourPKname[/i][/b][/purple] & "'"
   
   [green]'If not already goto a new record on the subform.[/green]
   If Not Me.NewRecord Then DoCmd.RunCommand acCmdRecordsGoToNew
   
   [green]'Copy the record . . . The code loops thru all the controls
   'and transfers the values of those with a question mark in
   'the Tag property to the same control in the new record.[/green]
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Trim(rst(ctl.Name) & "") <> "" Then
            Me(ctl.Name) = rst(ctl.Name)
         End If
      End If
   Next
   
   [green]'A new record requires a PK and I have no Idea how this superduper PK is
   'generated . . . This is up to you![/green]
   Me![purple][b][i]YourPKname[/i][/b][/purple] = [green]'However you supply PK! . . .[/green]
      
   [green]'Remove the recordset object from memory.[/green]
   Set rst = Nothing[/blue]

Works fine for me . . .

[blue]Record Selectors[/blue] are the [blue]boxes just to the left of records[/blue]. The box for the current record will have a [blue]right pointing arrow

[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .