Multiple criteria to select records from a form

[Jolene Updike]

I am having the "former" problem addressed in the following post- I want to
open a new form that looks up records based on the criteria set forth in the
first form.

Is there any way to have two combo boxes and uswe the selections from both to
open a subform? I would want to select a number and a date and then show all
records that meet that criteria.

thanks
Sara

Do you want to *open* a new, pop-up form? Or are you talking about a
true Subform, which is permanently open in a subform control on the
mainform?

If the latter, you can use the names of the two combo boxes, separated
by a semicolon, as the Master Link Field property of the subform
control; and the two fields to be searched (again separated by
semicolons) as the Child Link Field.

If you're opening a new form instead, you can set the WhereCondition
property to a text string containing the values of the two combos:

Dim strWhere As String
strWhere = "[FieldA] = " & Me!cboComboA _
& " AND [FieldB] = " & Me!cboComboB
DoCmd.OpenForm YourForm, WhereCondition := strWhere

--

Where do I put that above code? On the command button that opens up the
second form, or on the second form itself (which event)? This seems to be
the solution to my problem, I just can't quite figure out how to make it work.


Jolene Updike
Virginia Commonwealth University Archaeology

 

[Albert D.Kallal]

The idea here (at least I think) is that you will press a button to open the
new form.

The code you would place behind the button would normally be

docmd.OpenForm "yourFormNameGoesHere"

Of course, the above will simply open the form without restricting the
records loaded into that form. So, you can use the "where" clause of the
open form to restrict the records to any criteria you want.

You don't however mention what controls/text boxes on the first form you
wish to use to restrict the 2nd form that launches.

The example code given assumes two combo boxes.

Dim strWhere As String
strWhere = "[FieldA] = " & Me!cboComboA _
& " AND [FieldB] = " & Me!cboComboB

DoCmd.OpenForm YourForm,,,strWhere

in the above example, both comboa and combob are number fields. If you plan
to restrict the form via text fields, then you have to surround the criteria
with quotes.

 

[Scott B]

Albert,

What would you do with ths example if you wanted to restrict the range of
dates. For instance, open a form with records from 6/1/05 - 6/30/05. The
rest of your example is perfect for my needs, but I need to create criteria
for a form like you do for a report from a query.

Thanks,
Scott B

 

[Scott B]

Albert,

What would you do with ths example if you wanted to restrict the range of
dates. For instance, open a form with records from 6/1/05 - 6/30/05. The
rest of your example is perfect for my needs, but I need to create criteria
for a form like you do for a report from a query.

Thanks,
Scott B

 

[Albert D.Kallal]

Assuming you place two calendar controls on the form. you get


The code behind this button will be:

Dim strStartDate As String
Dim strEndDate As String

Dim strWhere As String

' build data conditions.

strStartDate = "#" & Format(Me.ActiveXStart.Value, "mm/dd/yyyy") & "#"
strEndDate = "#" & Format(Me.ActiveXEndDate.Value, "mm/dd/yyyy") & "#"

strWhere = "(PROJ_START between " & strStartDate & _
" and " & strEndDate & ")"

' combo box.

' lets assume we have a combo box to restrict to the sales rep

if isnull(me.salesrep) = false then
strWhere = strWhere & " and (SalesRep = '" & Me.salesrep & "')"
end if


DoCmd.OpenReport "repSales", acViewPreview, , strWhere

You will of course have the change the above field names used, but I think
you get the idea.

If you take a look at the following screen shots, I use the above code

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html