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

 

[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