A form to enter report criteria...(couldn't think of a better subject!!)

[Louly]

Hi everybody, your help will be very much appreciated.

I have a form that allows the user to set a criteria before opening the
report. It has 2 combo boxes lets call them A and B. A= the clients
names, and B= the documents we have in the DB. Each client has many
documents. If both combo boxes are left empty the report lists all the
clients with all their documents.

What I want to do now is as follows; when the user leaves cbo A empty
and selects, for example, a certain document in cbo B --> the report
should list all the available documents under the type selected (in cbo
B) for all the available clients. (note: the report is grouped 1st by
the "client", and 2nd by the 'type of document".) Or visa versa
selecting a client in cbo A and leaving cbo B empty --> the report
should list all the documents available for the selected client.

Did I make too complicated? Hope not!

Thanks for your cooperation.

Louly

[Keith Wilby]

"Louly" <mi*****@gmail.com> wrote in message
news:11**********************@y41g2000cwy.googlegr oups.com...

Hi everybody, your help will be very much appreciated.

I have a form that allows the user to set a criteria before opening the
report. It has 2 combo boxes lets call them A and B. A= the clients
names, and B= the documents we have in the DB. Each client has many
documents. If both combo boxes are left empty the report lists all the
clients with all their documents.

What I want to do now is as follows; when the user leaves cbo A empty
and selects, for example, a certain document in cbo B --> the report
should list all the available documents under the type selected (in cbo
B) for all the available clients. (note: the report is grouped 1st by
the "client", and 2nd by the 'type of document".) Or visa versa
selecting a client in cbo A and leaving cbo B empty --> the report
should list all the documents available for the selected client.

Did I make too complicated? Hope not!

Thanks for your cooperation.

Hi Louly.

What you need to do is build a SQL string to use as a WHERE clause for the
OpenReport method. Try something like this in a command button's click
event (untested):

Dim strSQL As String
If Not IsNull Me.cboA Then
strSQL = "[FieldA] = " & Me.cboA
If Not IsNull Me.cboB Then strSQL = strSQL & " And "
End If
If Not IsNull Me.cboB Then strSQL = strSQL & "[FieldB] = " & Me.cboB

DoCmd.OpenReport "rptMyReport", acViewPreview,, strSQL

HTH - Keith.
www.keithwilby.com

[Louly]

Thank you Keith for the prompt reply.

This question may sound stupid; I don't get what you mean by [FieldA]
and [FieldB].

Lou
Keith Wilby wrote:

"Louly" <mi*****@gmail.com> wrote in message
news:11**********************@y41g2000cwy.googlegr oups.com...

Hi everybody, your help will be very much appreciated.

I have a form that allows the user to set a criteria before opening the
report. It has 2 combo boxes lets call them A and B. A= the clients
names, and B= the documents we have in the DB. Each client has many
documents. If both combo boxes are left empty the report lists all the
clients with all their documents.

What I want to do now is as follows; when the user leaves cbo A empty
and selects, for example, a certain document in cbo B --> the report
should list all the available documents under the type selected (in cbo
B) for all the available clients. (note: the report is grouped 1st by
the "client", and 2nd by the 'type of document".) Or visa versa
selecting a client in cbo A and leaving cbo B empty --> the report
should list all the documents available for the selected client.

Did I make too complicated? Hope not!

Thanks for your cooperation.

Hi Louly.

What you need to do is build a SQL string to use as a WHERE clause for the
OpenReport method. Try something like this in a command button's click
event (untested):

Dim strSQL As String
If Not IsNull Me.cboA Then
strSQL = "[FieldA] = " & Me.cboA
If Not IsNull Me.cboB Then strSQL = strSQL & " And "
End If
If Not IsNull Me.cboB Then strSQL = strSQL & "[FieldB] = " & Me.cboB

DoCmd.OpenReport "rptMyReport", acViewPreview,, strSQL

HTH - Keith.
www.keithwilby.com

[Keith Wilby]

"Louly" <mi*****@gmail.com> wrote in message
news:11*********************@y41g2000cwy.googlegro ups.com...

Thank you Keith for the prompt reply.

This question may sound stupid; I don't get what you mean by [FieldA]
and [FieldB].

Just replace them with your real field names (client names and documents).

Regards,
Keith.