Double Click subform dataview record to add to second subform

[NotAnExpert]

Hi all.

I have a parent form with two sub-forms on it. The first sub-form is filtered by selection on parent form. However what I want to do is double click the record in the first sub-form which will then populate an entry in the second sub-form. If I double click again on the first sub-form I would like it to add a second record to the second sub-form.

I know this is possible but i'm not sure of the process to follow to make it work.

Could someone more knowledgeable point me in the right direction or a nod to an example would be awesome.

Thank you all.

 

[arnelgp]

you add a Code to the field/fields, Dbl

private sub field_DblClick()
With Me!secondSubForm.Form.Recordset
    .AddNew
    !FieldOnSubform2 = Me!fieldOnSubform1
    !anotherFieldOnSubform2 = Me!anotherFieldOnSubForm1
    .Update
End With
end sub

Click Event on the first subform.

 

[Pat Hartman]

Why are you duplicating the data? What happens if the user selects the same record from sf1 more than once? Will multiple copies end up in the table of sf2?

 

[NotAnExpert]

Why are you duplicating the data? What happens if the user selects the same record from sf1 more than once? Will multiple copies end up in the table of sf2?

Hi Pat, thanks for asking. The original data sits on SQL Server, but I need to be able to duplicate very specific information from this database into Access for an entirely separate purpose.

With regards to record duplication, yes, certain records will have multiple entries against it i.e. material/batch references

 

[NotAnExpert]

I think it might be best if I try to explain a little better. As i'm not sure the references look right to what I need...

I have a main form and that mainform contains two separate subforms. Both subforms are linked to the mainform by an ID.
The first subform gets data from SQL Server table, and the second subform is where I want to duplicate the data with extra bits.

I want to double click a record on the first subform, and some of that data appear in the second subform.

These subforms are not nested into each other and are completely separate.

Hope that helps!

 

[Pat Hartman]

With regards to record duplication, yes, certain records will have multiple entries against it i.e. material/batch references

That isn't what I asked. I asked if a user clicked TWICE on ONE record, would that record be copied TWICE or would that be a duplicate so you don't want to copy it a second time.?

I think it might be best if I try to explain a little better

We understood the request and arnel jumped right in with code to accomplish the task as that is his typical response. I didn't test it but he normally tests the code he posts so I'm sure it does what he thinks you want to do.

Seems pretty tedious to go through record by record and pick or not pick. What criteria is the user using? If you can define the criteria, you can copy ALL the data with a SINGLE append query so I wouldn't just jump in to implement arnel's solution even though it does address your specific question. Sometimes you need to look at the big picture rather than the details of a task.

Going further with this, once you copy data from the server to your local database, it is disconnected and will NEVER be updated unless you make that happen. If you don't care, then I don't care. However, If I needed to do further processing locally on some items but not all, I would probably copy only the primary key and look up the other details when I needed them. That would leave me with a mainform bound to a query that joined the local table to to the server table and was not updateable. Then I would use a subform that managed only the local data. This ensures that the server data is always current but it is separate from your local data and is not affected when you update the local data.