Filter in header affects find record combo

[Emases]

I have a toggle button in the header of a form that filters the
records. It displays all records, and filters out those tasks that are
not complete.

There is a combo box created using the wizard that is used to find
records based on the [Job_No] which is not a primary key.

Its code is:

Sub Combo38_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Job_No] = " & Me![Combo38]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

However, when the form is filtered to display only uncomplete tasks
(e.g. 80 records out of 200), the combo box still shows [Job_No]s for
200 records not the 80 that exist after the filter has been applied.

Please can you advise on the coding necessary to update the combo box
accordingly.

Ta
Steven

 

[Klatuu]

You will have to make the rowsource of your combo box match the filtering
imposed by the toggle button and requery the combo. Something like this in
the After Update event of the toggle button will do it.

If Me.ToggleButton = True Then
Me.ComboBox.RowSource = Something
Else
Me.ComboBox.RowSource = Something Else
End If
Me.ComboBox.Requery

 

[Emases]

I have updated it to this:

Sub Combo38_AfterUpdate()

If Me.Toggle57 = True Then
Me.Combo38.RowSourceType = "Table/Query"
Me.Combo38.RowSource = "SELECT MXC.JOBID, MXC.Job_No FROM MXC WHERE
(((MXC.WKDate) Is Null) ORDER BY JOBID)"
Me.Combo38.Requery
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Job_No] = " & Me![Combo38]
Me.Bookmark = Me.RecordsetClone.Bookmark

Else
Me.Combo38.RowSourceType = "Table/Query"
Me.Combo38.RowSource = "SELECT MXC.JOBID, MXC.Job_No FROM MXC)
ORDER BY JOBID)"
Me.Combo38.Requery
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Job_No] = " & Me![Combo38]
Me.Bookmark = Me.RecordsetClone.Bookmark

End If
End Sub

But it still does not work?

 

[Emases]

I have updated the code as follows:

Private Sub Togglebox_AfterUpdate()

If Me.Togglebox Then
Me.Filter = "WorkCompleteDate Is Null"
Me.Togglebox.Caption = "Uncomplete Tasks" ' changes the toggle button
caption
Me.Togglebox.ForeColor = 255 'changes toggle button caption colour
red
Me.Cbox.RowSource = "SELECT Customer.JOBID, Customer.Job_No FROM
Customer WHERE (((Customer.Job_No) Is Not Null) And
((Customer.WorkCompleteDate) Is Null)) ORDER BY Customer.Job_No"

Else
Me.Filter = ""
Me.Togglebox.Caption = "All tasks" ' changes the toggle button
caption
Me.Togglebox.ForeColor = 32768 'changes toggle button caption
colour green
Me.Cbox.RowSource = "SELECT Customer.JOBID, Customer.Job_No
FROM Customer WHERE ((Customer.Job_No) Is Not Null) ORDER BY
Customer.Job_No"

End If
Me.FilterOn = Me.Togglebox
Me.Cbox.Requery

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Job_No] = " & Me![Cbox]
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

But it still displays all Job_Nos.

 

[Klatuu]

The logic and the code look good. I would try using the select statemets you
build in the immediate window to create recordsets and see what they return.