How to copy selected records from a continuous form

[mdavid]

Hi,
I have a main form with a subform continuous form, each form on the continuous form contains FieldA, FieldB, FieldC and a YesNo Checkbox, there are also Fields D-E that are not on the form but in the table.
On the main form is a button called "CopySelected Records". I want to select (click checkbox of) some of the continuous form records, and then click CopySelectedRecords to copy the selected records - Fields A-E - to a second table. What would be the code in the CopySelectedRecords_OnClick procedure?

Thanks for any help


David

[ranman256]

it runs a query. docmd.openquery "qaCopyQry"
the query would pull the data in the subform using the current record key . (does your subtable have key?, like an autonum to identify it?)
it would be like

qaCopyQry=select * from subtable where [id]=forms!fMasterForm!subform!form!txtID

the query would see what record you are on. If you do not use autonums in the subform, you must supply lots of other fields:
where [state]=forms!fMasterForm!subform!form!txtState and [name]=forms!fMasterForm!subform!form!txtName

(not the greatest method but is works)

When building the where clause, always use the BUILDER to get the pathname correct...
forms!fMasterForm!subform!form!txtID

[CJ_London]

if the yesno control is bound to a field in your table, you need to query that table to identify which records have been selected. If it is not bound then selecting one will select all and you need a different approach. There is an example of how to make a continuous subform 'multi select' here https://www.access-programmers.co.uk...d.php?t=289116

[mdavid]

Hi Ranman, Thanks for your reply, afraid I don't understand.
I thought the query would be:
Select * from subtable where [LinkChildField] = [LinkMasterField] and YesNoCheckbox = True

When you say "autonums in the subform" do you mean the tables PK? - the table does have a PK - or are you referring to a key on the form?

In your reply you only mention the query - I wanted to also copy the selected records to another table I don't see that part?

Also you say "use the BUILDER to get the pathname correct", this is something I've spent many hours battling with, how do I use the Builder for this? is there a tutorial?

Excuse my ignorance, I have alot to learn - and it's getting late!

Thanks
David

[mdavid]

Thanks for the link Ajax, looks like something I'll use. At the same time I'd like to copy all the selected records to another table is there a block copy or do I need to loop thru the results copying each record separately?

Thanks
David

[CJ_London]

I haven't check the actual code but you would use a single sql query to insert the data into another table. The filter string generated by the code would be used as a criteria. Perhaps something like

SQLStr="INSERT INTO newTable (fld1, fld2, fld3...) SELECT fld1, fld2, fld3... FROM origTable WHERE PK IN (" & me.Selected & ")"
currentdb.execute SQLStr

replace table and field names with your own. the bit in red is the equivalent of the filter string created by the current code