Copy and Paste in VBA

[ECEK]

I'm probably going about this the wrong way.
I have the following code (I have written what I want it to do)

Basically copy data from my initial form and paste it into the new form

Private Sub ALCAdviser_AfterUpdate()
If Me.PON1 = "Has Partner" Then

'COPY THE DATA THAT IS IN Me.ALCAdviser


DoCmd.OpenForm "frm_partnerform", acNormal, "", "[clientref2]=" & "'" & [clientref2] & "'", , acNormal

'PASTE DATA INTO Me.frm_partnerform.ALCAAdviser

End If
End Sub

Can anybody point me in the right direction please?

 

[Ranman256]

you dont copy /paste,
you run an append query that uses a reference from the source form.
or
open the form, then filter the form to the existing record needed.

 

[JHB]

If "frm_partnerform" bound to a table or query, then insert/append the data into the table.

 

[ECEK]

Isn't there any SQL that could just hold the data ?

 

[Frothingslosh]

SQL doesn't hold data - it allows you to work with data. The data itself never leaves the tables, and while you can certainly create a recordset, I've not heard of an existing recordset being assigned as the record source to a newly opened form, since the record source is queried when the form is run, and if a query is assigned as a record source after the form is open, it is immediately run. In fact, I don't think you *CAN* assign an already-open recordset as a record source.

It would be best if you told us what precisely you are trying to do (I mean beyond 'copy data from one form and place it in another' - there may be a better approach that you're not aware of.

Normally, however, if you want to open one form with a dataset based on another form, you use a SQL statement with criteria pointing at the form that is already open, either as part of the record source or as part of an applied filter. There are a couple other ways I can think of as well (such as recordset navigation), but they're a bit more complicated and typically used for pretty specific reasons. I mean, I guess you could open an unbound form and then pull each field in from an open recordset or the original form, but that's making things WAY harder than they need to be.

 

[ECEK]

I have my records of clients.
I want to assign an adviser to each record.
However
They are sometimes joined (Mr & Mrs) by a LINK field (each record having the same LINK code.

When I select an adviser for one I want to auto fill the adviser field in the LINK'ed record.

Hope this makes sense.

 

[Cronk]

You have not given any information about your table structure, but assuming your tblPeople (if that's what it's called) is something like
PersonID
HasPartner (seems to be superfluous because value is true if PartnerID is not null)
PartnerID (foreign key to Partner's PersonID)
AdviserID

The sql to update the partner's adviserID is as follows (depending on the names of your form's controls)

currentdb.execute "Update tblPersonID set AdviserID =" & me.cboAdviser & " where PersonID=" & me.txtPartnerID

 

[Cronk]

I see I misread your question. You want a global update. The sql in your update query would be something like

update tblPerson as Person inner join tblPerson as Partner on Person.PersonID =Partner.PartnerID set Partner.adviserid = People.PartnerID where not Person.adviserID is null