Forms and Queries

[Arlene]

Please can you help me as I am trying to run a query by clicking in a combi
box drop down. For example in the combi box I have a List Available, In
stock, Awaiting Stock. What I would like to do is when you click on available
in the combi box I could have a report of every thing that is availble and so
on with In Stock Etc.

 

[Ken Sheridan]

I'd suggest having the combo box in an unbound form and within that form put
a subform, in continuous form or datasheet view, based on a query which lists
ALL items, whether available, in stock etc.

For the subform control in the main unbound form set the LinkMasterFields
property to the name of the combo box, and the LinkChildFields property to
the name of the field in the subform's query which contains the values which
correspond with those in the combo box's list, i.e. 'available', 'in stock'
etc.

When you select an item in the combo box the subform should then
automatically show only those rows which match the selection in the combo box.

If you also want to print a report of the items then also create a report
based on the same query as the subform and add a button to the main form with
code along the following lines in its Click event procedure:

Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.YourComboBox

' make sure an item has been selected
' in the combo box
If Not IsNull(ctrl) Then
strCriteria = "Availability = """ & ctrl & """"
' print report filtered to selected availability
DoCmd.OpenReport "YourReport", WhereCondtion:=strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

In the above example Availability would be the name of the field in the
table, so should be changed to its actual name. You'd also need to change
YourComboBox, YourTable and YourReport to their actual names. Remember than
any object names which include spaces or other special characters must be
wrapped in brackets [like this].

If you want to preview a report rather than print it change the relevant
line to:

DoCmd.OpenReport "YourReport", View:= acViewPreview,
WhereCondtion:=strCriteria

You could of course have two buttons, one to print the report and one to
preview it.

Ken Sheridan
Stafford, England

 

[Arlene]

Ken,

Thank you for replying to E-mail it has been very usefull but I have a
slight Problem as when I try to preview the report it Does not like the
Phrase WhereCondtion:= in the code. Please can you help me with this.

Many Thanks

Arlene Clark

I'd suggest having the combo box in an unbound form and within that form put
a subform, in continuous form or datasheet view, based on a query which lists
ALL items, whether available, in stock etc.

For the subform control in the main unbound form set the LinkMasterFields
property to the name of the combo box, and the LinkChildFields property to
the name of the field in the subform's query which contains the values which
correspond with those in the combo box's list, i.e. 'available', 'in stock'
etc.

When you select an item in the combo box the subform should then
automatically show only those rows which match the selection in the combo box.

If you also want to print a report of the items then also create a report
based on the same query as the subform and add a button to the main form with
code along the following lines in its Click event procedure:

Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.YourComboBox

' make sure an item has been selected
' in the combo box
If Not IsNull(ctrl) Then
strCriteria = "Availability = """ & ctrl & """"
' print report filtered to selected availability
DoCmd.OpenReport "YourReport", WhereCondtion:=strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

In the above example Availability would be the name of the field in the
table, so should be changed to its actual name. You'd also need to change
YourComboBox, YourTable and YourReport to their actual names. Remember than
any object names which include spaces or other special characters must be
wrapped in brackets [like this].

If you want to preview a report rather than print it change the relevant
line to:

DoCmd.OpenReport "YourReport", View:= acViewPreview,
WhereCondtion:=strCriteria

You could of course have two buttons, one to print the report and one to
preview it.

Ken Sheridan
Stafford, England

Please can you help me as I am trying to run a query by clicking in a combi
box drop down. For example in the combi box I have a List Available, In
stock, Awaiting Stock. What I would like to do is when you click on available
in the combi box I could have a report of every thing that is availble and so
on with In Stock Etc.

 

[John W. Vinson]

Thank you for replying to E-mail it has been very usefull but I have a
slight Problem as when I try to preview the report it Does not like the
Phrase WhereCondtion:= in the code. Please can you help me with this.

Please copy and paste your actual code and the error message. Neither Ken nor
anyone on the newsgroup can see your screen.

John W. Vinson [MVP]

 

[Arlene]

Sorry Please find the code below. The Combo box name is Combo1, The report is
named Status and my Table name is Status Table.

Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo1

' make sure an item has been selected
' in the combo box
If Not IsNull(ctrl) Then
strCriteria = "Available = """ & ctrl & """"
' Preview report filtered to selected availability
DoCmd.OpenReport "Status ", acViewPreview, WhereCondtion:=strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

End Sub

 

[Arlene]

Sorry forgot to post the error Message. Named argument not found.
WhereCondtion:=

 

[John Spencer]

DoCmd.OpenReport "Status ", acViewPreview, WhereCondtion:=strCriteria

WhereCondition NOT WhereCondtion (notice the missing "i")

Of course, I usually use the alternate construction
DoCmd.OpenReport "Status ", acViewPreview, , strCriteria
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Arlene]

Hi John, Thanks it is working now but I have another Problem when I run the
report I have a box with Invalid Operation Please select an item from the
List First come up in front of the report. Sorry but I have another question
how can I do the same but in a query form rather than a report form.

 

[John Spencer]

Sorry, I am not sure I understand your latest set of questions.

I have no idea why you are getting the Invalid Operation, Please select and
item from the List First message.

Are you selecting something in Combo1 before you ?click on a button? to run
the report?
Did you drop the If ... Then ... Else ... End If structure in your code?

Private Sub ?????????????
Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo1

' make sure an item has been selected
' in the combo box

If Not IsNull(ctrl) Then
strCriteria = "Available = """ & ctrl & """"
' Preview report filtered to selected availability
DoCmd.OpenReport "Status ", acViewPreview, WhereCondtion:=strCriteri
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

End Sub

There is no such thing as a query form. Do you mean you want to filter the
records that a form displays? OR do you want to filter the records that a
query is displaying in Datasheet view?


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Arlene]

Please could you help as I seem to be doing something wrong. I have enter the
code below and the following error appears. Syntax error (Missing operator)
in query Expression (Billing Status =In Store (Off)â€â€â€).

The Code is

Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then
strCriteria = "Billing Status = """ & ctrl & """""
' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub

The Form Name is Billing Status Table and has a Field Name of Billing Status
Which has a list below of
In Store (Off), Not In Store (On), Terminate.

The Query is also named Billing Status
The Report Name is Billing Status

 

[John Spencer]

Now you know one reason that having spaces in field and table names is a bad
idea.

Try changing the line to
strCriteria = "[Billing Status] = """ & ctrl & """""


Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then

strCriteria = "[Billing Status] = """ & ctrl & """""

' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Arlene]

John Thanks for patience and Help and sorry for troubling you again but now
I have this error coming up.
Syntax error in string query Expression ([Billing Status] =In Store (Off)""").

Now you know one reason that having spaces in field and table names is a bad
idea.

Try changing the line to
strCriteria = "[Billing Status] = """ & ctrl & """""


Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then

strCriteria = "[Billing Status] = """ & ctrl & """""

' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please could you help as I seem to be doing something wrong. I have enter
the
code below and the following error appears. Syntax error (Missing
operator)
in query Expression (Billing Status =In Store (Off)""").

The Code is

Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then
strCriteria = "Billing Status = """ & ctrl & """""
' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub

The Form Name is Billing Status Table and has a Field Name of Billing
Status
Which has a list below of
In Store (Off), Not In Store (On), Terminate.

The Query is also named Billing Status
The Report Name is Billing Status

 

[Douglas J. Steele]

The original suggestion appears to have had one too many double quotes:

strCriteria = "[Billing Status] = """ & ctrl & """"

That's three double quotes before & ctrl &, and four double quotes after.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John Thanks for patience and Help and sorry for troubling you again but
now
I have this error coming up.
Syntax error in string query Expression ([Billing Status] =In Store
(Off)""").

Now you know one reason that having spaces in field and table names is a
bad
idea.

Try changing the line to
strCriteria = "[Billing Status] = """ & ctrl & """""


Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then

strCriteria = "[Billing Status] = """ & ctrl & """""

' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please could you help as I seem to be doing something wrong. I have
enter
the
code below and the following error appears. Syntax error (Missing
operator)
in query Expression (Billing Status =In Store (Off)""").

The Code is

Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then
strCriteria = "Billing Status = """ & ctrl & """""
' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub

The Form Name is Billing Status Table and has a Field Name of Billing
Status
Which has a list below of
In Store (Off), Not In Store (On), Terminate.

The Query is also named Billing Status
The Report Name is Billing Status


:

Sorry, I am not sure I understand your latest set of questions.

I have no idea why you are getting the Invalid Operation, Please
select
and
item from the List First message.

Are you selecting something in Combo1 before you ?click on a button?
to
run
the report?
Did you drop the If ... Then ... Else ... End If structure in your
code?

Private Sub ?????????????
Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo1

' make sure an item has been selected
' in the combo box

If Not IsNull(ctrl) Then
strCriteria = "Available = """ & ctrl & """"
' Preview report filtered to selected availability
DoCmd.OpenReport "Status ", acViewPreview,
WhereCondtion:=strCriteri
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

End Sub

There is no such thing as a query form. Do you mean you want to
filter
the
records that a form displays? OR do you want to filter the records
that
a
query is displaying in Datasheet view?


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John, Thanks it is working now but I have another Problem when I
run
the
report I have a box with Invalid Operation Please select an item
from
the
List First come up in front of the report. Sorry but I have another
question
how can I do the same but in a query form rather than a report form.

:

DoCmd.OpenReport "Status ", acViewPreview,
WhereCondtion:=strCriteria

 

[Arlene]

Douglas,

Thanks for your help as it is much appreciated.

Arlene

The original suggestion appears to have had one too many double quotes:

strCriteria = "[Billing Status] = """ & ctrl & """"

That's three double quotes before & ctrl &, and four double quotes after.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John Thanks for patience and Help and sorry for troubling you again but
now
I have this error coming up.
Syntax error in string query Expression ([Billing Status] =In Store
(Off)""").

Now you know one reason that having spaces in field and table names is a
bad
idea.

Try changing the line to
strCriteria = "[Billing Status] = """ & ctrl & """""


Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then

strCriteria = "[Billing Status] = """ & ctrl & """""

' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please could you help as I seem to be doing something wrong. I have
enter
the
code below and the following error appears. Syntax error (Missing
operator)
in query Expression (Billing Status =In Store (Off)""").

The Code is

Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then
strCriteria = "Billing Status = """ & ctrl & """""
' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub

The Form Name is Billing Status Table and has a Field Name of Billing
Status
Which has a list below of
In Store (Off), Not In Store (On), Terminate.

The Query is also named Billing Status
The Report Name is Billing Status


:

Sorry, I am not sure I understand your latest set of questions.

I have no idea why you are getting the Invalid Operation, Please
select
and
item from the List First message.

Are you selecting something in Combo1 before you ?click on a button?
to
run
the report?
Did you drop the If ... Then ... Else ... End If structure in your
code?

Private Sub ?????????????
Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo1

' make sure an item has been selected
' in the combo box

If Not IsNull(ctrl) Then
strCriteria = "Available = """ & ctrl & """"
' Preview report filtered to selected availability
DoCmd.OpenReport "Status ", acViewPreview,
WhereCondtion:=strCriteri
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

End Sub

There is no such thing as a query form. Do you mean you want to
filter
the
records that a form displays? OR do you want to filter the records
that
a
query is displaying in Datasheet view?


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John, Thanks it is working now but I have another Problem when I
run
the
report I have a box with Invalid Operation Please select an item
from
the
List First come up in front of the report. Sorry but I have another
question
how can I do the same but in a query form rather than a report form.

:

DoCmd.OpenReport "Status ", acViewPreview,
WhereCondtion:=strCriteria

 

[Arlene]

John,

Thanks for your help as it is much appreciated.

Arlene

Now you know one reason that having spaces in field and table names is a bad
idea.

Try changing the line to
strCriteria = "[Billing Status] = """ & ctrl & """""


Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then

strCriteria = "[Billing Status] = """ & ctrl & """""

' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Please could you help as I seem to be doing something wrong. I have enter
the
code below and the following error appears. Syntax error (Missing
operator)
in query Expression (Billing Status =In Store (Off)""").

The Code is

Const conMESSAGE = "Please select an item from the list first."
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.Combo200

' make sure an item has been selected in the combo box

If Not IsNull(ctrl) Then
strCriteria = "Billing Status = """ & ctrl & """""
' Preview report filtered to selected Billing Status
DoCmd.OpenReport "Billing Status", acViewPreview, =strCriteria
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub

The Form Name is Billing Status Table and has a Field Name of Billing
Status
Which has a list below of
In Store (Off), Not In Store (On), Terminate.

The Query is also named Billing Status
The Report Name is Billing Status