Copy data from form to table

[primagic]

I have datasheet form with a list of records whose record source is an sql statement.

I am trying to take all those records on the subform and insert them into another table.

I am using the code below to do this. However, I need to know how I make it do every record on the datasheet form.

Any ideas?

DoCmd.GoToRecord , , acFirst
DoCmd.RunSQL ("INSERT INTO tblCampaignHistory ([CampaignID],[OrganisationID]) VALUES (CampaignID.value, Forms!frmCampaigns!frmCampaignRecipients.form!SchoolID)")
DoCmd.SetWarnings (False)
DoCmd.GoToRecord , , acNext
DoCmd.RunSQL ("INSERT INTO tblCampaignHistory ([CampaignID],[OrganisationID]) VALUES (CampaignID.value, Forms!frmCampaigns!frmCampaignRecipients.form!SchoolID)")
DoCmd.SetWarnings (False)

 

[Remou]

Take the SQL statement for the subform and add a WHERE statement that includes the link master/child field (generally an ID of some description). This should return all the records in the subform. You then only need to create an INSERT query from that statement:

  ssql= "INSERT INTO TableX (ID, Field, Field) " _
  & "SELECT ID, Field, Field FROM SubformRecordSource " _
  & "WHERE ID = " & [FieldOrControl]

http://lessthandot.com

 

[randy700]

I would do the insert from the data source, not the displays on a form.
That being said, this might work...

DoCmd.GoToRecord, , acFirst
Do Until acLast
    DoCmd.RunSQL ("INSERT INTO tblCampaignHistory ([CampaignID],[OrganisationID]) _
        VALUES (CampaignID.value, Forms!frmCampaigns!frmCampaignRecipients.form!SchoolID)")
    DoCmd.SetWarnings (False)
    DoCmd.GoToRecord , , acNext
    DoCmd.RunSQL ("INSERT INTO tblCampaignHistory ([CampaignID],[OrganisationID]) _
        VALUES (CampaignID.value, Forms!frmCampaigns!frmCampaignRecipients.form!SchoolID)")
    DoCmd.SetWarnings (False)
    DoCmd.GoToRecord acNext
Loop

Randy

 

[Remou]

@randy700
It looks very unsafe, surely if a query to insert all the records at once is impossible (though I can't see why), it would be better to use the recordsetclone?

For that matter, anything that involves adding complete records from one table to another suggests poor database design.

http://lessthandot.com

 

[primagic]

Thanks for your help guys. I managed to solve it using the following:

Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset

Set dbs = CurrentDb()
'look at open, but hidden form to find policy uwrs
Set rst1 = Forms!frmCampaigns.frmCampaignRecipients.Form.RecordsetClone
Set rst2 = dbs.OpenRecordset("tblCampaignHistory", dbOpenDynaset)


rst1.MoveFirst

Do Until rst1.EOF
rst2.AddNew
rst2!CampaignID = Forms!frmCampaigns!CampaignID
rst2!OrganisationID = rst1!SchoolID
rst2.Update

rst1.MoveNext

Loop

Me.Form.Refresh
rst1.Close
rst2.Close

I am only taking to fields not the entire table