A "ctrl + ditto" button?

[achung]

Hey all,

Im creating a form right now that has several fields that users will enter information into. When they hit enter the info and hit next, another identical page will come up that they have to fill out the same way with new info.

Sometimes there will be multiple entries that may have one or two fields that will have the same value. For example, 20 entries in a row may have the same values in fields 1 and 2, but different ones for 3 and 4. For these fields 1 and 2, I would like to put a button next to them that will pull that information out from the previous entry (which has the information I want ot put in fields 1 and 2).

I have seen forms with this function before, but have no idea how to create one. Does anybody have any ideas?

What I am doing right now is pressing ctrl+' and it copies the value from the corresponding field from the previous form. But I need this function to be performed with a button. Please help!

Thanks guys

Thanks

 

[Remou]

You can use the recordsetclone:

set rs=Me.recordsetclone
rs.MovePrevious
Me.txt1=rs!Field1
Me.txt2=rs!Field2

 

[bubba100]

Place this in the On Key Down event of the form. I believe the Key Preview must the set to "yes".

Dim strKey As String
strKey = Chr(KeyCode)
If KeyCode = 27 Then
KeyCode = 0
End If

'If the F2 Key has been Pressed, the Field from the previous invoice is duplicated

Select Case KeyCode

Case vbKeyF2 'Duplicate The Field From The Previous Record
SendKeys "^(')"

End Select

 

[achung]

Hi Remou,

Thanks. How do I use this code as a button?

 

[Remou]

Create a command button and paste the code into the click event (select [Event Procedure] and click ...) between Sub and End Sub.

 

[achung]

Thanks for clarifying. I am still screwing something up though...

When I press the button, my VB explorer pops up showing the code and ".txt1 =" is highlighted and a message saying "Compile Error: Method or data member cannot be found" pops up.

Here is my code:

Option Compare Database


Private Sub Command37_Click()


Set rs = Me.RecordsetClone
rs.MovePrevious
Me.txt1 = rs!Field1
Me.txt2 = rs!Field2


End Sub


Thanks again

 

[Remou]

You need to change the names of the controls (txt1, txt2) to actual controls on your form and the names of the fields (Field1, Field2) to the fields in the table that you want duplicated to the controls.

 

[achung]

Alright I made the adjustments, but when i click on the button now an error message pops up: "Run-time error '3021': No Current Record"


Here is my code as it is now:


Option Compare Database


Private Sub Command37_Click()


Set rs = Me.RecordsetClone
rs.MovePrevious
Me.Text27 = rs!Label28



End Sub


Thanks

 

[Remou]

You will need some error handling for when you are at the first or last record, in the meantime, to test the idea, move past the first record and try.

 

[achung]

Hey Remou,

There is an error with my "rs.MovePrevious". Everything else seems to be fine. Do I need to program an event or a procedure for it? My code is the still the same as my last post.

 

[Remou]

Are you saying that you always get an error at rs.MovePrevious, or only when you try the code on the first record of your form?

 

[achung]

Sorry, let me clarify. I have been trying it only on the second record of my form, and am getting the error at re.MovePrevious.

 

[Remou]

Ok. Try:

Private Sub Command37_Click()


Set rs = Me.RecordsetClone
rs.Bookmark=Me.Bookmark
rs.MovePrevious
Me.Text27 = rs!Label28



End Sub

 

[achung]

Thanks! It works perfectly. Looks like the bookmark was missing.

Thanks again, I have been trying to find the answer to this for over a week.

 

[Remou]

You will need to add a little error trapping to check for begining and end of file.

 

[achung]

You mean if I want to copy the field in the very last record to my first?

 

[Remou]

No. I mean if you click copy on the first record, MovePrevious will take you to BOF (beginning of file), and you will get an error. I was rambling when I mentioned EOF.

 

[achung]

Gotcha, well what you've given me so far is perfect. The error does show when I run the button on the first record, but that wont be a problem if thats the only error. Thanks