process filtered records - alternative
Posted on 2011-09-12
This is a repeat of a question I raised earlier. I received a response and awarded points because I thought it would fix my problem but it hasn't. So I will restate the problem, state what I have done and why it doesn't work.
I need to create a mechanism that allows users to filter on a database and then I will write some code to process the selected records.
I have created a form that allows me to put some instructions, a "Select All" button and a "Process records" button in the header. I created a subform in datasheet view so that the user can use the standard MS Access filters to display the records they want to process, filtering on various fields as required. Once they have displayed those records I want the user to be able to select which of the displayed records they want processed by checking the check box alongside each or by pressing the Select All button on the form. NB Each record has a field called Select, set to false when the form is first activated.
To handle the Select All functionality I tried the following code but it runs way too slow (7500 records takes about a minute to process), plus the form doesn't refresh properly when the process is finished. Can anyone help me figure out a solution?
Private Sub chkSelectAll_Click()
Dim rs As Recordset
On Error GoTo chkSelectAll_Click_Error
Set rs = Me.sFrmSelectNurses.Form.Recordset
Do While Not rs.EOF
If rs!SelectNurse <> Me.chkSelectAll Then
rs!SelectNurse = Me.chkSelectAll
Set rs = Nothing
On Error GoTo 0
LogError Err.Number, Err.Description, "Form_frmBulkSMS", "chkSelectAll_Click", Erl