Append to table from form filter result

[MikeCDPQ]

Hi all,

I would like to have an idea of how to go about appending the records resulting from a form filter to another existing table.

I am almost there but I find my solution to be very complex and not "elegant". (looping through the records and running an append query after each record found ???)

I am sure there is a very simple way but I just can't seem to come up with it.

All suggestions will be appreciated and considered

Thanks a bunch !

Mike

 

[Remou]

Hi
How about an append query? For example:

strSQL= "INSERT INTO tblTable ( Code, FirstName, LastName ) " _ 
& "SELECT tblTable1.Code, tblTable1.FirstName, tblTable1.LastName " _ 
& "FROM tblTable1 " _ 
& "WHERE (((tblTable1.FirstName) Like "*mar*"));"
DoCmd.RunSQL strSQL

 

[MikeCDPQ]

Thanks but that would only work from a table to another table.

As I mentionned I need the append the result of a form filter.

Thanks anyway.

 

[TheAceMan1]

How are ya MikeMcKesson . . .

[blue] . . . that would only work from a table to another table.[/blue]

The above is essentially what your doing! . . . and [blue]Remou[/blue] has shown a method to accomplish this. Your missing the point . . .
[ol][li]The form is based on a [blue]recordsource (query/table) that gets its data from a table[/blue].[/li]
[li]The filter, returns a subset of that recordsource, hence a subset of the table.[/li]
[li]Filter is just another name for Criteria![/li][/ol]
Assuming this will be done in VBA, what you do is setup an [blue]Append SQL[/blue] statement that includes an [blue]SELECT statement with criteria set according to filter[/blue]. [purple]This SELECT statement is the data source for the append query![/purple] . . .

[blue]   Dim SQL As String
   
   SQL = "INSERT INTO [b]tblAce[/b] ( txtField, qDate ) " & _
         [purple]"SELECT txtField, qDate " & _
         "FROM [b]tblTest[/b] " & _
         "WHERE ([b]tblTest[/b].txtField) Like 'A*')[/purple];"
   DoCmd.RunSQL SQL[/blue]

All you have to do is [blue]concatenate the Where Clause properly[/blue], per your filter . . .

[purple]Your thoughts?[/purple]

See Ya! . . . . . .

 

[RoyVidar]

This is perhaps more a forms question than Access Modules/VBA coding (forum702)?

I haven't worked with this, just did a small test right now.

When working with an Access forms .Filter property, you can do something like this

[tt]debug.print me.filter[/tt]

which will print the .Filter to the immediate pane (hit ctrl+g to study) - when stripped off the excess parenthesis etc, it might look something like:

[tt]tbl.fld1 = 'test' AND tbl.fld2 = 0 ...[/tt]

which I think can be described as an SQL Where clause, without the keyword Where.

To take this a step further, I think you can just concatenate that into the sql string, just remember the litteral Where - for identical tables, try something like:

[tt]strSql = "insert into newtable " & _
"select * from mytable " & _
"where " & me.filter[/tt]

Roy-Vidar

 

[MikeCDPQ]

Thanks a bunch everybody.

I am getting the point now. This just might do the trick. I'll try that at work on Monday and let you know.

Thanks a bunch and have a great weekend.

Mike.

 

[MikeCDPQ]

Hi all,

I am happy to report that your solution with a bit of tweaking worked beautifully.

Thanks a bunch everyone !