Searching through combo box

[rabia]

I have a search form1 with a combo box (cboAge).


"Under 18
18-25
26-35
Over 35"
Once the user chooses from one of the options, another form2 should open up with all the matching ages. I've created a text field (txtAge) in form2 which calculates the age based on the date of birth field from the table.
I used the macro and stated if combo box of age from form1 is "Under 18" then I'm opening a new form2 and in the where clause trying to get all the records that are less than 18 years old.
If [cboAge]='Over 18'
Open Form [form2]
Where = [forms]![form2]![txtAge] < 18
The above code is not even opening form2 when I select anything from cboAge.
Please help me out here. I'm totally new to the macros and the coding. Thanks!

[Bob Fitz]

1) What is the Row Source of the combo box?
2) What is the Column Widths property setting?
3) what is the Column Count property setting?

[rabia]

I've inserted the values in the combo box age which are
Under 18
18-25
26-35
Over 35
The column count is 1 and column width is 1"

[June7]

You don't reference textbox (nor any control) in the WHERE clause like that.

You apply parameter to a field in the table/query.

WHERE [Age] < 18

Construct a field in query that calculates the range values and apply the combobox selection as parameter to that field. So using the calculated [Age] field, an expression could be:

AgeRange: Switch([Age]<18, "Under 18", [Age]<26, "18-25", [Age]<36, "26-35", [Age]>35, "Over 35")

In the macro WHERE argument, apply combobox parameter to that constructed field: [AgeRange] = forms!form2.cboAge

Or use method suggested in your other thread https://www.accessforums.net/showthread.php?t=67521

[rabia]

Thanks June!
I already had a age query which was calculating the age, so I included another calculated field (AgeRange) to calculate the range.
In my form2 I've created a combo box referencing to AgeRange of age query.
I have the following code but its retrieving all the record
WHERE [cboAge]=[Forms]![Form2]![AgeRange]
cboAge is the combo box of form1
AgeRange is the combo box of form2
Please correct me where I'm wrong. Your help is greatly appreciated! Thanks

[rabia]

I want to point here that in cboAge from form1 I feed in the values myself. Do you think that could be the problem?
By the way your technique is really good and I think I'm almost there in resolving the issue.
Thanks!

[rabia]

I'm still stuck here June.
I think my where clause is not set up right.
I have the following code but its retrieving all the record
WHERE [cboAge]=[Forms]![Form2]![AgeRange]
or
WHERE [AgeRange]=[Forms]![Form1]![cboAge]
cboAge is the combo box of form1
AgeRange is the combo box of form2 referencing the query in which I'm calculating the range.
Please correct me where I'm wrong. Your help is greatly appreciated! Thanks

[June7]

If you want form1 code to open form2 filtered based on some age or age range, then an UNBOUND combobox needs to be on form1. The WHERE criteria to open form2 would be like:

[AgeRange] = forms!form1!cboRange

form2 would have BOUND textboxes to display the Age and AgeRange values

What do you mean by 'feed in the values myself' - combobox RowSourceType is set to ValueList?

[rabia]

Oops! I thought I did have a bound combo box in form2, but I was wrong.
I changed it to bound combo box and now its working.
Thank you so much June for helping me you. You are great!

[June7]

How can that be working? That is not what I described in my suggestion.

Controls used to select filter criteria should be UNBOUND, otherwise you CHANGE THE VALUE IN RECORD.

[rabia]

My combo box in form1 is unbound and combo box in form2 is bound.

[June7]

Having combobox bound to a calculated field is useless and just misleads user because the value of calculated field cannot be changed. Use a textbox and set it as Locked Yes and TabStop No.

[rabia]

You're right, it can mislead the user. I changed it to textbox.
Thanks!