save content of a subform to a table ?

[stonehead]

Hi all,

I have a subform displayed records filtered from a main table. Could you please show me know to save all those filtered records in my subform to another table ? I've searched the forum but could not find the answer. Your help is much appreciated.

 

[Remou]

What is the recordsource of the subform? What is the filter?

 

[stonehead]

My code so far:
'users enter their cause_id as filter

Private Sub txtCauseID_AfterUpdate()

If txthidden = "" Then
txthidden = txthidden & "'" & txtCauseID.Value & "'"
Else
txthidden = txthidden & ",'" & txtCauseID.Value & "'"
End If

Me.Controls("sfSelectedRec").Form.RecordSource = "Select * from dbo_hc_v_lt_plaintiff_labels where cause_id in (" & txthidden.Value & ")"

txtCauseID = ""
Screen.PreviousControl.SetFocus

end sub

 

[Remou]

Ok.

Set db=CurrentDB

strSQL="Select * INTO NEWTABLE from dbo_hc_v_lt_plaintiff_labels where cause_id in (" & txthidden.Value & ")"[/tt]

db.Execute strSQL, dbFailOnError

 

[stonehead]

Thanks so much. It works like a charm. I have more questions though.

how can I write to a table that's already exist instead of creating new one ? There will be 2 cases:

1/clear all old records of table and write new one to it
2/add more records to table without deleting the old ones.

I started working with Access since yesterday and my book is not that helpful so please excuse my many questions. Thank you.

 

[Remou]

It is generally better to list fields rather than use *. For the most part, it should not be necessary to copy sections of a table to another table. If a temporary table is necessary, you will usually find that just the ID field is sufficient.

You can use an append query to add records to an existing table.

[tt]strSQL="INSERT INTO NEWTABLE Select * FROM dbo_hc_v_lt_plaintiff_labels where cause_id in (" & txthidden.Value & ")"[/tt]

It is a good idea to read a great deal about SQL.

 

[stonehead]

Thanks a lot for your help.

 

[Remou]

You are welcome, and welcome to Tek-Tips. Make sure you read the FAQs and don't forget Advanced Search.