Use filter by form group of data as query criteria

[tagteam]

I would like to use the found set of data from a filter as the criteria in a query.

For example:

• use filter by form to find a group of 50 cars
• run an append query to copy certain fields of data to another table for those 50 cars

I already have the append query and button and it works fine if I am on a single car



I would like to be able to run the filter by form and click the button and have it append the necessary fields for all the cars found in the filter.

[Dal Jeanis]

That would depend on which method you are using to filter the records. Behind your "filter by form", are you using VBA to build the SQL for a filter, or are you using some other method?

If you are already doing this in VBA, then it's just a matter of building your insert SQL using the same filter criteria that you built for the form.

[tagteam]

I am using a form. It is the filter by form built in to the access menu.

I have a form that is based on a query. The query just gets me all the data from the table. I am only using the query so I can set the sort method and for the future if they want to exclude any type of car
In the form the user selects Advanced -> Filter By Form
the user gets back a set of 30 records found by the filter sorted by carID

I would like to then have a button that runs a query and gets data based on only the records found by the filter in this example the 30 records.
I already have the queries built in the design window and want to make this found set the criteria.

For example, my current query has the criteri [Forms]![CarData]![CarID] and this moves the current car lease information to the history table. I want to replace this with the found set of data. I am sure it probably wont work in the query design window because I will have to do some kind of for loop or something.
Thanks

[Dal Jeanis]

Yep, you need a VBA routine to loop through the recordset and move each one.

Is your functioning button executing a macro or some VBA code? If the latter, then please post the code here, and I can give you the structure to loop around it.

[Dal Jeanis]

Here's a rough skeleton for the click code for a control button that sets a particular field (MyField) to a particular value ("NewValue") for all the visible records on a form, as the form is currently filtered.

Assuming that your current code is VBA, that code would be placed in the lines between .Edit and .Update, and it would need to be modified to make it use the rst recordset, rather than using the current record. (If that makes any sense to you.)

Private Sub cmdSetAll_Click()
    Dim rst As Recordset

On Error GoTo Err_cmdSetAll_Click

    Set rst = Me.Recordset
    With rst
       .MoveFirst
       Do 
          .Edit
          !MyField = "NewValue"
          .Update
          .Movenext
       Loop Until .EOF
    End With

Exit_cmdSetAll_Click:
    Exit Sub

Err_cmdSetAll_Click:
    MsgBox Err.Description
    Resume Exit_cmdSetAll_Click

End Sub

[tagteam]

First of all, thank you for your help!

The functioning button was created using the macro builder so it basically runs a macro that runs the append query.

The append query was built in Access Query Design mode.

I have 23 fields that i copy from the existing record in the Car table and append to the Lease History table

Could i run the code below and during the .Edit phase run a macro that runs the append query? (see below)

Private Sub cmdSetAll_Click()
    Dim rst As Recordset

On Error GoTo Err_cmdSetAll_Click

Set rst = Me.RecordsetClone
    With rst
       .MoveFirst
       Do 
          .Edit
          ****run append query here*****
            DoCmd.RunMacro "q_appendLeaseHistory"
          .Movenext
       Loop Until .EOF
    End With

Exit_cmdSetAll_Click:
    Exit Sub

Err_cmdSetAll_Click:
    MsgBox Err.Description
    Resume Exit_cmdSetAll_Click

End Sub

I am thinking this will not work because the parameter query for the append query is the [Forms]![CarData]![CarID] and since I will be doing this in VBA it will not change the value of the CarID since it is actually not changing in the form. Does my thinking make sense? That makes me think I might need to put the sql statement in the VBA.

Thanks

[Dal Jeanis]

That's as easy as anything else.

By the way, I made a mistake - I believe the RecordsetClone doesn't include the filters, whereas Recordset does. Code changed above.