Duplicate select field values from selected record in form - Access 07

[salvipride]

Hello,

This is my very first post, I've been looking through the posts to see if I can find a solution to my request, I found a few but apparently the don't work on my version of access. I'm not a programmer, however, I've created an access 2007 database that has the following elements:

- a table called: tblEquipment
- a split form called: frmEquipment

The fields of the table exposed on the form are:

- equipmentname
- equipmentid
- equipmentlocation
- equipmentmodel

I'd like to code a button to place on the form, so that when it is clicked, it will create a duplicate of the currently selected record visible on the form, and I need it to only duplicate the following fields: equipmentname, equipmentlocation and equipmentmodel. I do not need the equipmentid field to be duplicated since I need for the user doing data entry to be forced to enter a unique id.

Any help is greatly appreciated!

Regards.

 

[kjv1611]

So, may I ask why you want to purposefully duplicate your data? And then do you mean to duplicate the data on the FORM or in the table? You could give the appearance of duplicating the data on the form, I believe, without actually duplicating the data in the table, which just seems like an all-around win in such a situation.

 

[TheAceMan1]

How are ya salvipride . . .

See my post here: thread702-1382356

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[salvipride]

kjv1611, I apologize for not clarifying that. The form's purpose is for working with records in the table called tblEquipment, so when I duplicate a record on the form, a new record would be inserted in the table.

TheAceMan1, Thank you for your reply! I actually tried what you suggest on the post you linked, however it did not work I assume it might have to do with me being on access 2007, I checked everything at least 10 times and no go. I appreciate your post!

Regards,

 

[kjv1611]

salvipride,

How did it not work? I don't see anything in TheAceMan1's code that should not work on Access 2007. Some things did not carry forward, but they are very few and far between.

Try it with a couple of minor changes just to see:

   Dim ctl As Control
   Dim DQ As String
   
   DQ = Chr(34) [green]'34 is the Ascii code for the double quote  
                'I think the code example had one too many quotes.[/green]
   
   If Not Form.NewRecord Then
      For Each ctl In Form.Controls
         If ctl.Tag = "?" Then
            ctl.DefaultValue = DQ & ctl & DQ
         End If
      Next
   End If

Also, did you put the question mark in the tag property of the controls you wanted to reference? And what event did you put the code in?

When you say something didn't work, please tell us HOW it didn't work. If you got an error message, what was the message, and on what line of code did you receive it?

If no error message, have you tried stepping through the code and hovering over variables to see what they are?

Another thing you can do is insert Debug.Print commands throughout the code and open the Immediate Window pane within the VB Editor while you're stepping through the code, so you can see for sure what things are (because some variables will not give you anything when you hover over them.

So if it were me, I would want to insert something like:

   Dim ctl As Control
   Dim DQ As String
   
   DQ = Chr(34) [green]'34 is the Ascii code for the double quote  
                'I think the code example had one too many quotes.[/green]
   
   If Not Form.NewRecord Then
      For Each ctl In Form.Controls
         [blue]If ctl.Tag = vbNullString Or IsNull(ctl.Tag) Then
         Else
            Debug.Print ctl.Name & vbCrLf & _
                        "   Tag = " & ctl.Tag & vbCrLf & _
                 "-----------------------------"
         End If[/blue]
         If ctl.Tag = "?" Then
            ctl.DefaultValue = DQ & ctl & DQ
         End If
      Next
   End If

With that, you'll see what the tag value is for each control, so long as it has a value. I added in the double conditional statement, b/c I couldn't remember for sure whether the Tag value is by default a Null value or a Null String value. I supposed I could have just checked with the Len() function, but I didn't. Of course you can change it to your preferences, or to other suggestions.

 

[salvipride]

kjv1611,

Thank you for taking the time to rewrite the code and all of your feedback, I appreciate it!, sorry for not posting before, I've been a bit busy. I gave the code another shot and it turns out it worked the whole time! I just did not expect it to work the way it does.

Here's how I implemented it:

The form that I use is a split form showing data entry fields on the top and a datasheet view on the bottom. I had initially created a "Duplicate" button with the access wizard, and it worked perfect because, I would select any record on the datasheet view, and that would show that record on the form at which point if I needed to duplicate the record, I clicked the "Duplicate button" and the whole record would be duplicated, after the click of the button, I would be at the very last record (newly inserted) with all of the field values of the record I was on before clicking the button.

I decided to implement a duplicate traping on a field that holds the equipment ID which should be unique (I added vb code on the field's BeforeUpdate event because many times users would forget to update the equipment ID so I ended up with many duplicated. The trap works perfect, but since the "Duplicate" button duplicated everything, even the equipment id field, it stopped being a solution, so that's why I started looking for a way to select which filed to duplicate.

I added a ? on the tag property field for each of the controls I would like to get duplicate data from, then I created a button on my form, and added TheAceMan1's code to the on click event, and the current behavior is a little diferent, because; I click a record on my datasheet view (that is beneath the form data entry fields, doing this will show the records field contents on my form fields, when I click the button I assigned the code to, nothing happens, however, if I click another button I have to insert a new record, I can see that the fields of my new record are populated with the field values of the record I selected when I clicked the button, but if I decide that I no longer wish to insert the new record and either try to delete the record (which shows an * on the record's row selector in the datasheet instead of a pencil) nothing happens, the record does not go away.

Ideally, I would like to have the same behavios (click the button, that inserts a new record with the fields filled with data from the record selected before clicking the button, and to be an actual record on my underlying table, so that if I decide to void it, I can delete it.

Thank you all for your help on this, I trully appreciate it!

Warm regards,

MH