Selection from a filtered form

[superchrisdub]

Hi,

I have a form within which there is a subform. The recordsource for the subform is a table with a Selected (Yes/No) column. So if you want a record to be included in the processing then you just put a tick in the Selected column. What I want is, if a user applies a filter to the subform, then all filtered records get a tick put in the Selected column automatically. Currently I have a button in the main form which puts a tick in the Selected column, which is not very convenient or professional-looking, and requires the user to remember to do it. I tried putting some code in the ApplyFilter event but that seems to fire before the filter has been applied, so it ended up putting a tick in the Selected column of every record. Is there some way of putting the ticks in the Selected column automatically after the filter has been applied. I'm sure there is an easy way to do it, which I have missed as I am not an experienced Access developer, although I am very familiar with Excel VBA.


Any help would be greatly appreciated.

Thanks,
Chris

 

[jdraw]

Have you considered using a query as the recordsource of the subform instead of the table?
Perhaps you could show us a jpg of the form/subform and filter. You can post attachment if you use zip format.

I don't know your filter criteria, but you might be able to make a combobox of filter choices, then adjust (vba) your query/recordsource where clause with the criteria, then requery the form.

 

[superchrisdub]

Hi,

I have attached a screenshot which should make it clearer. If a user wants to filter on, say, the Status column for just records that are "Out of Force", how can you make the Selected column get a tick for all the filtered records, automatically, rather than the user having to press the Confirm Selection button. Basically, once the user has filtered down to the records they want, they want to export the data to Excel (I know I can mail merge direct from Access but my boss just wants the selected records exported to Excel for now). The issue is that I want the Selected column to be automatically ticked for all the records included in the filtering process. I don't think it would make any difference if it was done with a query or a table. The requirement is still the same, either way.

All help gratefully received.

thanks,
Chris

 

[jdraw]

How many columns are selectable as filters?

Could use a Form,
show some filter options, (combo, cascading combos??)
allow user to select accordingly,
get confirmation,
select filtered records and export to Excel.

 

[JHB]

..What I want is, if a user applies a filter to the subform, then all filtered records get a tick put in the Selected column automatically.

What is the meaning of that, is it supposed that you afterwards untick some of the records?
How/ where do you set the filter?
Show the code you've in the ApplyFilter event.

 

[superchrisdub]

Hi,

The filter is applied using the options available when you click the down arrow on a column, e.g. click the down arrow in the column header for Status and select only "Out of Force". At this point, The records are filtered to just show records with a status of "Out Of Force". I want all these records to have a tick added in the "Sel'd" (Short for Selected) column, put in automatically, without the user having to add the ticks by pressing Confirm Selection. The attachments show what I want to achieve.

The code I tried in ApplyFilter should give you an idea of what I am trying to achieve:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    Dim rs As DAO.Recordset
    
    Set rs = Me.Recordset
    
    Do Until rs.EOF
        Me.Selected = True
        rs.MoveNext
    Loop
    
    Set rs = Nothing
End Sub

The problem is that a tick gets applied to the whole recordset, not just the "Out of Force" records. Do you see what I mean. I need that code added to just the filtered records. But ApplyFilter is triggered before the filter actually takes place.

My problem is that I have learned Access VBA ad hoc, as I go along and from what I find on the internet. I'm sure there is a much better way of doing things than what I have done.


Finally, after the filter has been applied and ticks put in the Sel'd (Selected) column, the user should be able to untick a record if they need to.


Thanks again,

Chris

 

[JHB]

Try the below code, remember to change the table and field name.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
  Dim dbs As DAO.Database
  
  Set dbs = CurrentDb
  dbs.Execute ("UPDATE [COLOR=Red]Vendors[/COLOR] SET [COLOR=red]IsSelected[/COLOR] = False;")
  dbs.Execute ("UPDATE [COLOR=red]Vendors[/COLOR] SET [COLOR=red]IsSelected [/COLOR]= True Where " & Me.Filter)
End Sub

 

[jdraw]

I agree with JHB that updating the IsSelected will put the ticks in the table field. But I question that the tick is necessary. If you do a SELECT query with the proper filter/criteria, the appropriate records will be used and there seems to be no need for the tick. My guess is that the tick is what you understand and why you do not use a Select with criteria.

 

[superchrisdub]

Thank you, JHB. That did the trick perfectly.

 

[JHB]

You're welcome.