Form-Combo Box

J

Joel

I am using a combo box on my form to run a report. The
combo box on the form is tied to a query used by the
report in question. However, I have ran into a
limitation, if I do not make a selection from the combo
box the report does not return any data.

What I need is to return all records when the combo box
is left blank.

Can anyone provide any information on how to get this
done? Your help is greatly appreciated.

Joel
 
V

Van T. Dinh

What do you meant by "...using a combo box on my form to run a report..."?
Are you using the value of the ComboBox as part of the Filter for the
Report? a Parameter for the Query being used as the RecordSource of the
Report? ...

Post relevant details of Tables, Fields, Query, how you open the Report, ...
 
G

Guest

Correct, I use a value of the ComboBox as a filter to run
a report and it works fine. But if I do not select a value
from the combobox then the report does not pick up any
data.

I am sure there is a way to do this. I have done something
similar in the past, using a string on a query where you
can either enter a value of leave blank for all. But I do
not know how to perform this via a combobox.

Any you can offer is greatly appriciated.

Joel
 
J

Jim

What I like to do is create two text boxes on the form that the query uses,
example

two text boxes, choice1 and choice2

on the combo box I make sure there is an "All" value (make this the default)
on the AfterUpdate on the combo box I use an if statement to fill in the two
text boxes

If Me!CmbChoice = "All" Then
Me!choice1 = "1" or "A*"
Me!choice2 = "9999" or "Z*"
Else
Me!choice1 = Me!CmbChoice
Me!choice2 = Me!CmbChoice
End If

then in the query that the form is based on in the criteria box I point to
the two text boxes

Between [Forms]![Form1]![choice1] And [Forms]![Form1]![choice2]

Hope this helps

James
 
V

Van T. Dinh

Set the criteria of the Query being used as the RecordSource to:

.... WHERE ([YourField] = Forms!YourForm!ComboBox)
OR (Forms!YourForm!ComboBox Is Null)

When nothing selected, ComboBox value is Null. Hence the RHS of OR is True
and the whole expression is True which means all Records are selected.
 
J

Joel

Thank you very much for your help. It worked great.

Joel

-----Original Message-----
Set the criteria of the Query being used as the RecordSource to:

.... WHERE ([YourField] = Forms!YourForm!ComboBox)
OR (Forms!YourForm!ComboBox Is Null)

When nothing selected, ComboBox value is Null. Hence the RHS of OR is True
and the whole expression is True which means all Records are selected.

--
HTH
Van T. Dinh
MVP (Access)



Correct, I use a value of the ComboBox as a filter to run
a report and it works fine. But if I do not select a value
from the combobox then the report does not pick up any
data.

I am sure there is a way to do this. I have done something
similar in the past, using a string on a query where you
can either enter a value of leave blank for all. But I do
not know how to perform this via a combobox.

Any you can offer is greatly appriciated.

Joel


to
run a report..."? you
open the Report, ...
Click to expand...


.
Click to expand...
 
J

Joel

Jim

Thank you for your help. I used a different approched
sugested by another user Van T. Dinh found under the
reply under my original questio and I was able to
accomplish the task.

Thanks again for your help.

Joel

-----Original Message-----
What I like to do is create two text boxes on the form that the query uses,
example

two text boxes, choice1 and choice2

on the combo box I make sure there is an "All" value (make this the default)
on the AfterUpdate on the combo box I use an if statement to fill in the two
text boxes

If Me!CmbChoice = "All" Then
Me!choice1 = "1" or "A*"
Me!choice2 = "9999" or "Z*"
Else
Me!choice1 = Me!CmbChoice
Me!choice2 = Me!CmbChoice
End If

then in the query that the form is based on in the criteria box I point to
the two text boxes

Between [Forms]![Form1]![choice1] And [Forms]![Form1]! [choice2]

Hope this helps

James

I am using a combo box on my form to run a report. The
combo box on the form is tied to a query used by the
report in question. However, I have ran into a
limitation, if I do not make a selection from the combo
box the report does not return any data.

What I need is to return all records when the combo box
is left blank.

Can anyone provide any information on how to get this
done? Your help is greatly appreciated.

Joel
Click to expand...


.
Click to expand...
 
Top