Solved Copy Recordset

[wanzi]

I created a small MySQL database for our club using Access 2021 via an ODBC connection, which also works well.
I manage the members using a simple form. If I now create a new member and the following member has the same data. Only the date of birth and first name are different. I just want to copy the current data set and I can't do it. Maybe someone has already solved this and can help me with a template.

Thank you and Merry Christmas!

I solved it like this and it works:
Private Sub cmdCopy_Click()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryMitglieder", dbOpenDynaset)
Dim strNeueMitNr As String
Dim strNachname As String
Dim strStrasse As String
Dim strOrt As String
strNeueMitNr = neueMitNr("tblMitglieder")
strNachname = Me.Nachname
strStrasse = Me.Strasse
strOrt = Me.Ort

With rs
.AddNew
!MitNr = strNeueMitNr
!Nachname = strNachname
!Strasse = strStrasse
!Ort = strOrt

.Update
.Requery
End With

rs.Close
Set rs = Nothing

'an update with the new copied DS
Me.Requery

'and then go to the last record and display
DoCmd.GoToRecord , , acLast

End Sub

 

[theDBguy]

Hi. Welcome to AWF!

Have you tried the Ribbon menus yet? I think there's a Copy Record button for that.

 

[wanzi]

No ribbon menu, I'm looking for something in VBA.

 

[theDBguy]

No ribbon menu, I'm looking for something in VBA.

There's also the VBA equivalent:

   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.RunCommand acCmdPasteAppend

 

[Pat Hartman]

Really, the same address and phone number and email? I would think it would be more trouble to write over the actual differences. I would just use cntl-' to copy the specific fields that are the same.

Another common method is in the afterupdate event, Copy the values you want to propagate to the Default property of the matching control. This lets you copy only a couple of fields rather than the whole record which is probably more useful. Obviously, for the first record you enter after the form is opened, there will be no defaults.

 

[Gasman]

When I had to enter over 23K records I used something similar.
I had a checkbox for whether I wanted to copy previous record or not.

Then code like this for each field I wanted to copy.
In my case it was Date, Ship and Rank.

Private Sub Rank_ID_AfterUpdate()
    If Me.chkCopy Then
        Me![Rank_ID].DefaultValue = """" & Me![Rank_ID].Value & """"
    Else
        Me![Rank_ID].DefaultValue = 0
    End If

End Sub

 

[wanzi]

Thanks for the information. I found out for a recordset method and found the following code and adapted to my needs. A new data record is created, but the first is displayed when I close the form and open again. What is missing in the code? Please help!

 

[Gasman]

No code supplied?
We are not clairvoyant.

Thanks for the information. I found out for a recordset method and found the following code and adapted to my needs. A new data record is created, but the first is displayed when I close the form and open again. What is missing in the code? Please help!

 

[wanzi]

Excuse, here is the code:
Private Sub cmdCopy_Click()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryMitglieder", dbOpenDynaset)
Dim strNeueMitNr As String
Dim strNachname As String
Dim strStrasse As String
Dim strOrt As String
strNeueMitNr = neueMitNr("tblMitglieder")
strNachname = Me.Nachname
strStrasse = Me.Strasse
strOrt = Me.Ort

With rs
.AddNew
!MitNr = strNeueMitNr
!Nachname = strNachname
!Strasse = strStrasse
!Ort = strOrt
.Update
.Requery
End With

rs.Close
Set rs = Nothing

End Sub

 

[Gasman]

Nothing is missing.
If you want to go to the last record when you open the form, just issue that command on form load.

Open Form Move to last record

SOLVED: Open Form Move to last record I am trying to open a form and move to the last record. I am using this code: Private Sub Form_Load() If Me.RecordsetClone.RecordCount > 0 Then Me.RecordsetClone.MoveLast End If End Sub It does not move me to last record, how might it...

www.access-programmers.co.uk

 

[Pat Hartman]

It is poor practice to completely duplicate a record.

The "set default" method prevents this because at least ONE of the required fields needs to be different otherwise what would be the point of duplicating a record. This allows your BeforeUpdate validation procedure to keep you from accidentally creating a meaningless duplicate.

A new data record is created, but the first is displayed when I close the form and open again.

When you have duplicate records, there is no way for a query or code to distinguish them and that is why this is such poor practice.