Filter unbound search combo box based on filter in form's OnOpenEvent

  • Thread starter Thread starter miatadiablo
  • Start date Start date
M

miatadiablo

I have a form that is filtered On Open. How do I update the code for
my unbound search box to list only records based on the form's OnOpen
filter instead of all records? I might mention that on the same form
I have a set of option buttons that filter the form (same field)
OnClick. How do I keep my unbound search box filtered based on the
form's filter?

Here's what I have:

Private Sub Form_Open(Cancel As Integer)
Me.optActive.Value = 1
DoCmd.ApplyFilter , "Status = 'Active'"
End Sub


Private Sub cboSearchCorps_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[AutoNum] = " & Str(Nz(Me![cboSearchCorps], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


As always, many thanks for anyone's attention and input
 
There are many possible solutions.

Depending on HOW the underlying queries for the unbound combos are
established you may simply be able to say in the oncurrent event (and
maybe the afterupdate event) me.comboname.requery.

Sometimes I will move control information to a hidden field on the
form on an afterupdate or oncurrent event and use that as part of the
criteria for the underlying query and issue a me.comboname.requery
after the move.

We need more information in order to be any more specific.

Ron
 
There are many possible solutions.

Depending on HOW the underlying queries for the unbound combos are
established you may simply be able to say in the oncurrent event (and
maybe the afterupdate event) me.comboname.requery.

Sometimes I will move control information to a hidden field on the
form on an afterupdate or oncurrent event and use that as part of the
criteria for the underlying query and issue a me.comboname.requery
after the move.

We need more information in order to be any more specific.

Ron
Click to expand...

Obviously I hit reply to author instead of reply. Did you recevie my
questions? I thought it would be in my sent items but I don't see
it. If you didn't, I'll post again.
 
Here is what you sent:

Both suggestions make sense but I can't get my head around
makingeither happen. This is what I'm using for the option buttons in
order to apply the filter:

Private Sub optWithdrawn_Click()
Me.FilterOn = True
Me.optWithdrawn.Value = 1
Me.Filter = "[Status] = 'Withdrawn'"'I
put in me.cboSearchCorps.requery here but it didn't make anychanges
End Sub

========================================

Everything that you show you are showing are changes that will affect
the query that is associated with the form and NOT the unbound cmbbox.
Its source is independent of the form. It is the query that is in the
source of the cmbbox that has to be changed.

Given the example above here is what I would probably try:
1) create an invisible txtbox on the form
2) in the onopen for the form move the default status for the filter
to that txtbox
3) Change/create a query for the source of the combo that references
that txtbox as criteria (you may have to make the query criteria
be - like "*" & form![formname]![hiddentxtfieldname] & "*"
4) put that me.cboSearchCorps.requery back in the code.

Alternately - since your filter statements imply that the status is
part of the query that is the basis for the form:
1) Change/create a query for the source of the combo that references
the form's status field as the criteria
2) In this case the requery would need to be done in the afterupdate
event of the status field or at whatever event changed the status.
3) This method results in NO records showing in the combo if there was
no value in the status field.
4) This method would work even when there was NO filtering on the main
form, whereas the first method may not depending on how you make the
test in the combo's query.

Ron
 
Back
Top