Copying a record in a data entry form

[jgarnick]

I have a data entry form with an Add Record command button. There are certain instances when the new record's data should be the same as the previous record's data, with the exception of two or three fields. I tried adding a command button that would duplicate the record, but I got a pasting error--no explanation.

What's the best way to accomplish this? Thanks!

 

[cmmrfrds]

You could use the fields collection of the RecordSet. Save your current RecordSet values into an array, then add the new record and fill the fields from the array. For example:
Dim fldArray() as Variant, indx as integer
Dim RSMT as DAO.Recordset
Set RSMT= Me.RecordsetClone
For indx = 0 to "the number of fields in your recordset "
fldArray(indx +1) = RSMT.Fields(indx +1).Value
Next '- end of for

Go to new record you'll have to check the syntax

set RSMT.BookMark = Me.BookMark
For indx = 0 to "the number of fields in your recordset "
RSMT.Fields(indx +1).Value = fldArray(indx +1)
Next '- end of for

I use the fields collection but not for your specific purpose, but this should work okay. The collection is relative to zero so the first position in the arrary is empty.

Jerry

 

[cmmrfrds]

I meant to have the index for the fields collections like so RSMT.Fields(indx).Value

 

[jgarnick]

Thanks so much for your help Jerry, but I am getting a compile error message that says "invalid use of property" on the set rsmt.bookmark statement. Do you know why?

Also, what did you mean--should I use rsmt.fields(indx).value INSTEAD of rsmt.fields(indx+1).value??

I have never worked with arrays, so excuse my ignorance!

Thanks!!

 

[cmmrfrds]

Sorry, don't need the Set statement to equate the bookmark.
RSMT.Bookmark = Me.Bookmark

What I meant to do was equate the index setting in the arrary with the fields collection. The fields collection will start at 0 where the arrary could start at 1, but the default is 0 for the array unless you explicitly use "Option base 1" which I am sure is not the case so as not to be confusing use this syntax.
fldArray(indx) = RSMT.Fields(indx).Value

I tried to be too generalized and it was needlessly confusing, this was just in case the Option Base was set to 1 then the arrary would have started at 1 instead of 0.

Let me know if you continue to get an error.
Jerry

 

[jgarnick]

Sorry to be such a bother, but now I get a message that says "NO current record." Below is the code I put behind a command button that the user would click if they want to copy the info on the form in front of them. What else am I doing wrong? Thanks so much!

Private Sub Command120_Click()
On Error GoTo Err_Command120_Click

Dim fldarray() As Variant, indx As Integer
Dim rsmt As DAO.Recordset
Set rsmt = Me.RecordsetClone
For indx = 0 To 36
fldarray(indx) = rsmt.Fields(indx).Value
Next

DoCmd.GoToRecord , , acNewRec

rsmt.Bookmark = Me.Bookmark
For indx = 0 To 36
rsmt.Fields(indx).Value = fldarray(indx)
Next

Exit_Command120_Click:
Exit Sub
Err_Command120_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Command120_Click

End Sub

 

[cmmrfrds]

First we need to know what line generated the error. Can you add some debug code. After the first For statement, add this line. ie.
Debug.Print " Loop showing field names, Name = " ; rsmt.Fields(indx).Name
This will show that it is processings all the fields correctly.

After going to the new record add this line
Debug.Print " Bookmark for new record = " ; Me.BookMark
This will make sure it is going to the new record correctly.

After setting the RSMT.BookMark add this line
Debug.Print " Bookmark for new record = " ; RSMT.BookMark
This will show that it is able to set the bookmark.

After the second For statement, add this line.
Debug.Print " second Loop showing field values, Value = " ; rsmt.Fields(indx).Value
This will show that it is accessing the new record correctly.

When the ERROR occurs, then do Control G
This will bring up the debug window and let you review the progress through the code.
Depending on where the debug results stop will show where the error is occurring. Does the form show the new record? Let's see what happens. Copy and paste the debug messages.

Jerry

 

[cmmrfrds]

Replace the line DoCmd.GoToRecord , , acNewRec and lines below with this syntax. This will add a new record and update it. The syntax is consistent with the reference to the rsmt object. The LastModified sets the bookmark to the current record which you just added.

rsmt.AddNew
For indx = 0 To 36
rsmt.Fields(indx).Value = fldarray(indx)
Next
rsmt.Update
rsmt.Bookmark = .LastModified

 

[jgarnick]

Thanks so much for your help Jerry--I haven't had time to get back to this--I plan on working on it tomorrow--will let you know what the outcome is! Thanks again !

 

[VKTiger]

If all else fails, I have a data entry form that will copy the previous form. I created a command button, and added the following code to the "On Click" Event Procedure:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_copy_Click:
Exit Sub

Err_copy_Click:
MsgBox Err.Description
Resume Exit_copy_Click

Good Luck!

 

[jgarnick]

Hello Jerry,

Well I replaced the lines that you suggested with the code above, and I got an error message that said "Invalid or unqualified reference" and it stopped on the .lastmodified part of the bookmark statement.

Should I still add the debug code?? I haven't done that yet.

I wish I knew more about this stuff!! Thanks again for all your help!

VKTiger--thanks, but that was the first route I took. The command button wizard creates that code when you choose Duplicate record. But it gave me a Pasting error--thanks anyways!

 

[cmmrfrds]

Sorry, The line should have read.
rsmt.Bookmark = rsmt.LastModified

Please try this, It should work.

Jerry

 

[jgarnick]

AArrgghh! I get a message that says "No current record".

I guess I'll add the debug code, unless you think otherwise....thanks..........

 

[jgarnick]

I added the debug code and this is the message in the debu window:

Loop showing field names, Name=CATEGORY

so it appears that is it not processing the fields correctly. Category is a field name that happens to be a combo box--perhaps I'll take that out and carry on to see what happens ??

 

[jgarnick]

Well I took out the field category from my query that the form is based on and then the message just went to the next field name in the query. I took that out and it went to the next field and so on.

Like I said before, I have not used arrays, so I don't understand what is happening or not happening..........

I so appreciate your help!

 

[cmmrfrds]

I will try to look at this is a couple of hours, right now I need to leave. Can you show what fields are on your form? Hold off on this method until I get a chance to review your output. If you want leave me a phone number and I will call you. my email is cmmrfrds@cpinternet.com

Jerry