CODEWITHSUNDEEP
ms-accessvba
Camille Ricketts
Camille Ricketts

Reputation: 1

How do you transfer all data/records from one form to another using command button?

I have a main form with a continuous form as the sub form which shows services rendered to that particular client. Somehow duplicate clients were entered (names spelt wrong) and the records are separated between those two client names. I want to be able click a button from my sub form(incorrect client) and transfer those records to another form(data temporary host) that will allow me to ultimately transfer those same records from the middle form to the final form(the correct client).

Upvotes: 0

Views: 78

Answers (1)

Matt Hall
Matt Hall

Reputation: 2412

I'm assuming the foreign key that relates your subform records to your main form record is a client ID of some kind, so you just need a way to update this.

One way I could think of would be to have a text box in the footer of your Services Rendered subform that would take the client ID you'd like the records visible in your subform to be transferred to. A command button (again, in your subform footer) could then take that client ID value you've specified and update the client ID for all records showing in Services Rendered subform.

So if, for example you had a client with these services rendered:

enter image description here

...but unfortunately someonr made another client record for the same person (misspelling the last name):

enter image description here

You could specify the client ID you want your subform records to transfer to using the text box in the subform footer:

enter image description here

...and then click the command button to run a sub routine that will do the update for you:

enter image description here

You can then go to the client you transferred those records to and check it has worked:

enter image description here

Here's the VBA on the command button:

Private Sub cmdTransfer_Click()

    Dim rs As DAO.Recordset
    Set rs = Me.Form.Recordset

    If _
        Not (rs.BOF And rs.EOF) _
    Then

        rs.MoveFirst

        Do While Not rs.EOF
            rs.Edit
            rs!ClientID = Me.txtTransClientId
            rs.Update
            rs.MoveNext
        Loop

    End If

    Me.Requery

End Sub

Here's my mock-up file if you want to look at this in more detail.

Note that I've not done anything about the remaining duplicated client record, so I'd recommend building that in to the sub as well (e.g. a yes/no message box prompt for the user asking if they now want to delete the client).

Hope this helps. Now, I can't speak for the others who have replied, but I accept all major credit cards.

Upvotes: 1

Related Questions