Query Search Forms

  • Thread starter Thread starter Carla Thistle
  • Start date Start date
C

Carla Thistle

Hello,
I need big time help. I've created a form based on query. I've added
unbounded combo boxes to the form in which users will use as search
criteria. Using an Applied Filtre Expression in the After Update Event in
the properties of the combo boxes, I've been able to make the form search
for information.

This is hard to explain so bear with me. My problem is this. If searching
using one combo box..no problem...search updates as it should. If searching
two combo boxes, it searches the first one you choose no problem, then as
soon as you make your choice in the second combo box, it searches for that
info only(still with me?). How do I get it to search for both combo boxes at
the same time. I plan on using a lot of combo boxes on this form and I need
to be able to search for just one or all boxes and have my results show
pretty much right away.

All suggestions will be greatly appreciated.

Thank you.
 
Carla

Include in the form a subroutine that will create the
Form's filter string from the values in the combo boxes
(those that have not been used will have a null value) and
apply it using DoCmd.ApplyFilter. In each of the combo
boxes AfterUpdate method, make a call to this subroutine.

Hope That Helps

Gerald Stanley MCSD
 
Gerald

Thank you very much for your quick reply. You'll have to forgive me, I'm not
sure what a subroutine is or how to include a subroutine. Do you have any
step by step instructions I could follow? You know a step by step for access
dummies :).

Thank you.
Carla

Are you saying to go into the form properties
 
Carla

Put the form in Design Mode and press the 'Code' Icon on
the Toolbar to open the form's VB code.

Here is a sample of the code for the subroutine - I have
named the combo boxes cmbCombo1 and cmbCombo2, you will
have to change these to the names of your combo boxes.

Private Sub ApplyFilter()
Dim strFilter As String

If Not IsNull(cmbCombo1.Value) Then
strFilter = "field1 = " & cmbCombo1.Value
End If
If Not IsNull(cmbCombo2.Value) Then
If strFilter = "" Then
strFilter = "field2 = " & cmbCombo2.Value
Else
strFilter = strFilter & "and field2 = " &
cmbCombo2.Value
End If
End If

DoCmd.ApplyFilter , strFilter

End Sub

Then you should code the AfterUpdate method of each combo
box as this

Private Sub cmbCombo1_AfterUpdate()
ApplyFilter
End Sub

Hope That Helps

Gerald Stanley MCSD
 
Carla

The syntax on the DoCmd statement should be

DoCmd.ApplyFilter ,strFilter

Hope That Helps

Gerald
 
Okay...I've made the change to the syntax. Once I did that I tried it and
parameter boxes popped up for field1 and field2...so I figuered they were
supposed to be named Quarterback_ID and Property Type_ID as in my query. So
I renamed them. Then tried again.

What I get now is this:

Run-time error '3075': Syntex error (missing operator) in query expression
'Quarterback_ID = 4 and Property Type_ID = 2'.

Any ideas (she says pleading with mercy????)

Thanks Gerald,
Carla
 
The problem lies with 'Property Type_ID'. It should be
either 'Property_Type_ID' or [Property Type_ID] depending
on whether or not there is a space in the field name
 
Good morning Gerald and thank you for staying with me.

Hip hip hooray....no more error messages. I changed the field1 and field2 to
[Quarterback_ID] and [Property Type_ID] as it shows on the query and on the
form. That being said...when I open my form and choose a Property Type or a
Quarterback from my combo boxes....nothing happens. No results come up on
the form. I'm thinking there's probably some detail you need to know that I
haven't told you. So I"m going to try to give you every detail I can. If I
don't need to be so redundent you can let me know :).

- I have a table, called Table A for simplicity
- I have a query, called Query A that I created from Table A (No criteria
has been set in this query)
- I have a form, called Form A that I created from Query A.
- On this form I have unbounded drop down boxes in which the choices made
are to set the criteria for the query and filter the information on the
form.
- I have the code as follows:

Private Sub ApplyFilter()
Dim StrFilter As String

If Not IsNull(Quarterback.Value) Then
StrFilter = "[Quarterback_ID] = " & City.Value
End If
If Not IsNull(Property_Type.Value) Then
If StrFilter = "" Then
StrFilter = "[Property Type_ID] =" & Property_Type.Value
Else
StrFilter = StrFilter & "and [Property Type_ID] = " &
Property_Type.Value
End If
End If

DoCmd.ApplyFilter , StrFilter

End Sub

and I have the AfterUpdate Properties of the combo boxes set as follows:
1. Private Sub Property_Type_AfterUpdate()
ApplyFilter
End Sub

2. Private Sub Quarterback_AfterUpdate()
ApplyFilter
End Sub

That's it. Should I be adding anything into the criteria part of the query?
Also....Does the [Quarterback_ID] and [Property Type_ID] fields in the code
refer to the Quarterback_ID and Property Type_ID on the form...or in the
query?

I really do appreciate your time on this Gerald. I can't thank you enough.
I've been spinning my wheels for a long time on this thing.

*smiles*
Carla
 
Carla

Comments in your e-mail. Have you tried debugging the code
to see if it is doing what you expect?

Hope This Helps

Gerald Stanley
-----Original Message-----
Good morning Gerald and thank you for staying with me.

Hip hip hooray....no more error messages. I changed the field1 and field2 to
[Quarterback_ID] and [Property Type_ID] as it shows on the query and on the
form. That being said...when I open my form and choose a Property Type or a
Quarterback from my combo boxes....nothing happens. No results come up on
the form. I'm thinking there's probably some detail you need to know that I
haven't told you. So I"m going to try to give you every detail I can. If I
don't need to be so redundent you can let me know :).

- I have a table, called Table A for simplicity
- I have a query, called Query A that I created from Table A (No criteria
has been set in this query)
- I have a form, called Form A that I created from Query A.
- On this form I have unbounded drop down boxes in which the choices made
are to set the criteria for the query and filter the information on the
form.
- I have the code as follows:

Private Sub ApplyFilter()
Dim StrFilter As String

If Not IsNull(Quarterback.Value) Then
StrFilter = "[Quarterback_ID] = " & City.Value
Click to expand...
**
** Is city.value correct?
**
End If
If Not IsNull(Property_Type.Value) Then
If StrFilter = "" Then
StrFilter = "[Property Type_ID] =" &
Click to expand...
**
** Try StrFilter = "[Property Type_ID] = " &
**
Property_Type.Value
Else
StrFilter = StrFilter & "and [Property
Click to expand...
Type_ID] = " &
**
** Try StrFilter = StrFilter & " and [Property
Type_ID] = " &
**
Property_Type.Value
End If
End If

DoCmd.ApplyFilter , StrFilter

End Sub

and I have the AfterUpdate Properties of the combo boxes set as follows:
1. Private Sub Property_Type_AfterUpdate()
ApplyFilter
End Sub

2. Private Sub Quarterback_AfterUpdate()
ApplyFilter
End Sub

That's it. Should I be adding anything into the criteria
Click to expand...
part of the query?
**
** No
**
Also....Does the [Quarterback_ID] and [Property Type_ID] fields in the code
refer to the Quarterback_ID and Property Type_ID on the form...or in the
query?
Click to expand...
**
** The Query
**
 
Gerald...it's working...it's really working. YEEAAAHHHHHH.

I owe you big time Gerald....I have no idea who you are or how I can thank
you.

So now I can add in as many combo boxes as I want...right? Just so long as I
add to the code.
Ohhh here's a question. Does it make a difference as to what order the combo
box choices are made? Probably not, but I thought I'd ask.

Again...Thank you, thank you, thank you.

*big huge smile, a big hug, and an ever appreciating smile*
Carla

Gerald Stanley said:
Carla

Comments in your e-mail. Have you tried debugging the code
to see if it is doing what you expect?

Hope This Helps

Gerald Stanley
-----Original Message-----
Good morning Gerald and thank you for staying with me.

Hip hip hooray....no more error messages. I changed the field1 and field2 to
[Quarterback_ID] and [Property Type_ID] as it shows on the query and on the
form. That being said...when I open my form and choose a Property Type or a
Quarterback from my combo boxes....nothing happens. No results come up on
the form. I'm thinking there's probably some detail you need to know that I
haven't told you. So I"m going to try to give you every detail I can. If I
don't need to be so redundent you can let me know :).

- I have a table, called Table A for simplicity
- I have a query, called Query A that I created from Table A (No criteria
has been set in this query)
- I have a form, called Form A that I created from Query A.
- On this form I have unbounded drop down boxes in which the choices made
are to set the criteria for the query and filter the information on the
form.
- I have the code as follows:

Private Sub ApplyFilter()
Dim StrFilter As String

If Not IsNull(Quarterback.Value) Then
StrFilter = "[Quarterback_ID] = " & City.Value
Click to expand...
**
** Is city.value correct?
**
End If
If Not IsNull(Property_Type.Value) Then
If StrFilter = "" Then
StrFilter = "[Property Type_ID] =" &
Click to expand...
**
** Try StrFilter = "[Property Type_ID] = " &
**
Property_Type.Value
Else
StrFilter = StrFilter & "and [Property
Click to expand...
Type_ID] = " &
**
** Try StrFilter = StrFilter & " and [Property
Type_ID] = " &
**
Property_Type.Value
End If
End If

DoCmd.ApplyFilter , StrFilter

End Sub

and I have the AfterUpdate Properties of the combo boxes set as follows:
1. Private Sub Property_Type_AfterUpdate()
ApplyFilter
End Sub

2. Private Sub Quarterback_AfterUpdate()
ApplyFilter
End Sub

That's it. Should I be adding anything into the criteria
Click to expand...
part of the query?
**
** No
**
Also....Does the [Quarterback_ID] and [Property Type_ID] fields in the code
refer to the Quarterback_ID and Property Type_ID on the form...or in the
query?
Click to expand...
**
** The Query
**
I really do appreciate your time on this Gerald. I can't thank you enough.
I've been spinning my wheels for a long time on this thing.

*smiles*
Carla


.
Click to expand...
Click to expand...
 
Carla

That's great.

As to the question on combo boxes, it makes no difference
as to the order as each condition is ANDED in the Filter.

Regards
-----Original Message-----
Gerald...it's working...it's really working. YEEAAAHHHHHH.

I owe you big time Gerald....I have no idea who you are or how I can thank
you.

So now I can add in as many combo boxes as I want...right? Just so long as I
add to the code.
Ohhh here's a question. Does it make a difference as to what order the combo
box choices are made? Probably not, but I thought I'd ask.

Again...Thank you, thank you, thank you.

*big huge smile, a big hug, and an ever appreciating smile*
Carla

Gerald Stanley said:
Carla

Comments in your e-mail. Have you tried debugging the code
to see if it is doing what you expect?

Hope This Helps

Gerald Stanley
-----Original Message-----
Good morning Gerald and thank you for staying with me.

Hip hip hooray....no more error messages. I changed the field1 and field2 to
[Quarterback_ID] and [Property Type_ID] as it shows on the query and on the
form. That being said...when I open my form and choose a Property Type or a
Quarterback from my combo boxes....nothing happens. No results come up on
the form. I'm thinking there's probably some detail you need to know that I
haven't told you. So I"m going to try to give you every detail I can. If I
don't need to be so redundent you can let me know :).

- I have a table, called Table A for simplicity
- I have a query, called Query A that I created from Table A (No criteria
has been set in this query)
- I have a form, called Form A that I created from Query A.
- On this form I have unbounded drop down boxes in which the choices made
are to set the criteria for the query and filter the information on the
form.
- I have the code as follows:

Private Sub ApplyFilter()
Dim StrFilter As String

If Not IsNull(Quarterback.Value) Then
StrFilter = "[Quarterback_ID] = " & City.Value
Click to expand...
**
** Is city.value correct?
**
End If
If Not IsNull(Property_Type.Value) Then
If StrFilter = "" Then
StrFilter = "[Property Type_ID] =" &
Click to expand...
**
** Try StrFilter = "[Property Type_ID] = " &
**
Property_Type.Value
Else
StrFilter = StrFilter & "and [Property
Click to expand...
Type_ID] = " &
**
** Try StrFilter = StrFilter & " and [Property
Type_ID] = " &
**
Property_Type.Value
End If
End If

DoCmd.ApplyFilter , StrFilter

End Sub

and I have the AfterUpdate Properties of the combo boxes set as follows:
1. Private Sub Property_Type_AfterUpdate()
ApplyFilter
End Sub

2. Private Sub Quarterback_AfterUpdate()
ApplyFilter
End Sub

That's it. Should I be adding anything into the criteria
Click to expand...
part of the query?
**
** No
**
Also....Does the [Quarterback_ID] and [Property Type_ID] fields in the code
refer to the Quarterback_ID and Property Type_ID on the form...or in the
query?
Click to expand...
**
** The Query
**
I really do appreciate your time on this Gerald. I can't thank you enough.
I've been spinning my wheels for a long time on this thing.

*smiles*
Carla


.
Click to expand...
Click to expand...


.
Click to expand...
 
Gerald,

If I have anymore problems, do you mind if I save your email address and use
your expertise for help?

Carla

Gerald Stanley said:
Carla

That's great.

As to the question on combo boxes, it makes no difference
as to the order as each condition is ANDED in the Filter.

Regards
-----Original Message-----
Gerald...it's working...it's really working. YEEAAAHHHHHH.

I owe you big time Gerald....I have no idea who you are or how I can thank
you.

So now I can add in as many combo boxes as I want...right? Just so long as I
add to the code.
Ohhh here's a question. Does it make a difference as to what order the combo
box choices are made? Probably not, but I thought I'd ask.

Again...Thank you, thank you, thank you.

*big huge smile, a big hug, and an ever appreciating smile*
Carla

Gerald Stanley said:
Carla

Comments in your e-mail. Have you tried debugging the code
to see if it is doing what you expect?

Hope This Helps

Gerald Stanley
-----Original Message-----
Good morning Gerald and thank you for staying with me.

Hip hip hooray....no more error messages. I changed the
field1 and field2 to
[Quarterback_ID] and [Property Type_ID] as it shows on the
query and on the
form. That being said...when I open my form and choose a
Property Type or a
Quarterback from my combo boxes....nothing happens. No
results come up on
the form. I'm thinking there's probably some detail you
need to know that I
haven't told you. So I"m going to try to give you every
detail I can. If I
don't need to be so redundent you can let me know :).

- I have a table, called Table A for simplicity
- I have a query, called Query A that I created from Table
A (No criteria
has been set in this query)
- I have a form, called Form A that I created from Query A.
- On this form I have unbounded drop down boxes in which
the choices made
are to set the criteria for the query and filter the
information on the
form.
- I have the code as follows:

Private Sub ApplyFilter()
Dim StrFilter As String

If Not IsNull(Quarterback.Value) Then
StrFilter = "[Quarterback_ID] = " & City.Value
**
** Is city.value correct?
**
End If
If Not IsNull(Property_Type.Value) Then
If StrFilter = "" Then
StrFilter = "[Property Type_ID] =" &
**
** Try StrFilter = "[Property Type_ID] = " &
**
Property_Type.Value
Else
StrFilter = StrFilter & "and [Property
Type_ID] = " &
**
** Try StrFilter = StrFilter & " and [Property
Type_ID] = " &
**
Property_Type.Value
End If
End If

DoCmd.ApplyFilter , StrFilter

End Sub

and I have the AfterUpdate Properties of the combo boxes
set as follows:
1. Private Sub Property_Type_AfterUpdate()
ApplyFilter
End Sub

2. Private Sub Quarterback_AfterUpdate()
ApplyFilter
End Sub

That's it. Should I be adding anything into the criteria
part of the query?
**
** No
**
Also....Does the [Quarterback_ID] and [Property Type_ID]
fields in the code
refer to the Quarterback_ID and Property Type_ID on the
form...or in the
query?
**
** The Query
**

I really do appreciate your time on this Gerald. I can't
thank you enough.
I've been spinning my wheels for a long time on this thing.

*smiles*
Carla


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top