Form to generate a Report code

Newbie Form / Report using code Question

I am tring to generate a form with two combo boxes, using a 'Value List' as
the source type and manually entered values as the row source.
I also want a button called Preview which when pressed will preview a report
( called report01 ) based on the selection in the two combo boxes.

I want to stick to CODE only , as opposed to writing a query or using a
macro

So Far I have

Cmb01 will have 3 values "Distribuor"; "Wholesale"; "Retail"

Cmb02 will have 3 values "EastCoast" " MidWest" "WestCoast"

And the preview button.

The report - report01, is pulled from a customer table which includes two
fields called
1) 'Type' which is either Distributor, Wholesale or Retail.
2) 'Area' which is either EastCoast, MidWest or WestCoast

The question are

1. What is the code, if any is required, for the combo boxes to capture the
choice of values picked by the user and where to I paste this code in the
combo box.

2. What would be the corresponding code for the preview button to capture
the values of the Combo Box and pass it through to the Report.
I assume I paste this code in the 'OnClick' field of the Event Tab in the
properties of the Preview button.

I am thinking the preview code will be something like
DoCmd.OpenReport "report01", acPreview, "[Type] = Cmb01 AND [Area] =
Cmb02"

I know the above line of code has errors in it but I cant figure out the
right code for my little project.

Any help appreciated.

Pat Coleman wrote:

Newbie Form / Report using code Question

I am tring to generate a form with two combo boxes, using a 'Value List' as
the source type and manually entered values as the row source.
I also want a button called Preview which when pressed will preview a report
( called report01 ) based on the selection in the two combo boxes.

I want to stick to CODE only , as opposed to writing a query or using a
macro

So Far I have

Cmb01 will have 3 values "Distribuor"; "Wholesale"; "Retail"

Cmb02 will have 3 values "EastCoast" " MidWest" "WestCoast"

And the preview button.

The report - report01, is pulled from a customer table which includes two
fields called
1) 'Type' which is either Distributor, Wholesale or Retail.
2) 'Area' which is either EastCoast, MidWest or WestCoast

The question are

1. What is the code, if any is required, for the combo boxes to capture the
choice of values picked by the user and where to I paste this code in the
combo box.

None needed.


2. What would be the corresponding code for the preview button to capture
the values of the Combo Box and pass it through to the Report.
I assume I paste this code in the 'OnClick' field of the Event Tab in the
properties of the Preview button.

See Below.


I am thinking the preview code will be something like
DoCmd.OpenReport "report01", acPreview, "[Type] = Cmb01 AND [Area] =
Cmb02"

Close, but you need to get the value of the combo boxes, not
their names, and the values must end up in quotes:

DoCmd.OpenReport "report01", acPreview, _
"[Type] = '" & Cmb01 & "' AND [Area] = "' & Cmb02 & "'"


--
Marsh
MVP [MS Access]

Tried to insert the following code in to the OnClick event of the preview
button and it opens the report but does not filter it whatsoever - like it
is ignoring the two values.

Dim stDocName As String

stDocName = "Statement"
DoCmd.OpenReport stDocName, acPreview, "[Text2] = '" & Cmb01 & "' AND
[Text3] = '" & Cmb02 & "' "


Where is the error ???


"Marshall Barton" wrote in message
...
Pat Coleman wrote:

Newbie Form / Report using code Question

I am tring to generate a form with two combo boxes, using a 'Value List'
as
the source type and manually entered values as the row source.
I also want a button called Preview which when pressed will preview a
report
( called report01 ) based on the selection in the two combo boxes.

I want to stick to CODE only , as opposed to writing a query or using a
macro

So Far I have

Cmb01 will have 3 values "Distribuor"; "Wholesale"; "Retail"

Cmb02 will have 3 values "EastCoast" " MidWest" "WestCoast"

And the preview button.

The report - report01, is pulled from a customer table which includes two
fields called
1) 'Type' which is either Distributor, Wholesale or Retail.
2) 'Area' which is either EastCoast, MidWest or WestCoast

The question are

1. What is the code, if any is required, for the combo boxes to capture
the
choice of values picked by the user and where to I paste this code in the
combo box.

None needed.


2. What would be the corresponding code for the preview button to capture
the values of the Combo Box and pass it through to the Report.
I assume I paste this code in the 'OnClick' field of the Event Tab in the
properties of the Preview button.

See Below.


I am thinking the preview code will be something like
DoCmd.OpenReport "report01", acPreview, "[Type] = Cmb01 AND [Area] =
Cmb02"

Close, but you need to get the value of the combo boxes, not
their names, and the values must end up in quotes:

DoCmd.OpenReport "report01", acPreview, _
"[Type] = '" & Cmb01 & "' AND [Area] = "' & Cmb02 & "'"


--
Marsh
MVP [MS Access]

Pat Coleman wrote:

Tried to insert the following code in to the OnClick event of the preview
button and it opens the report but does not filter it whatsoever - like it
is ignoring the two values.

Dim stDocName As String

stDocName = "Statement"
DoCmd.OpenReport stDocName, acPreview, "[Text2] = '" & Cmb01 & "' AND
[Text3] = '" & Cmb02 & "' "


You're missing a comma:

DoCmd.OpenReport stDocName, acPreview, , "[Text2] = . . .

Where did Text1 and Text2 come from? I thought you said the
names of the fields (in the table/query) were named Type and
Area??
--
Marsh
MVP [MS Access]




"Marshall Barton" wrote
Pat Coleman wrote:

Newbie Form / Report using code Question

I am tring to generate a form with two combo boxes, using a 'Value List'
as
the source type and manually entered values as the row source.
I also want a button called Preview which when pressed will preview a
report
( called report01 ) based on the selection in the two combo boxes.

I want to stick to CODE only , as opposed to writing a query or using a
macro

So Far I have

Cmb01 will have 3 values "Distribuor"; "Wholesale"; "Retail"

Cmb02 will have 3 values "EastCoast" " MidWest" "WestCoast"

And the preview button.

The report - report01, is pulled from a customer table which includes two
fields called
1) 'Type' which is either Distributor, Wholesale or Retail.
2) 'Area' which is either EastCoast, MidWest or WestCoast

The question are

1. What is the code, if any is required, for the combo boxes to capture
the
choice of values picked by the user and where to I paste this code in the
combo box.

None needed.


2. What would be the corresponding code for the preview button to capture
the values of the Combo Box and pass it through to the Report.
I assume I paste this code in the 'OnClick' field of the Event Tab in the
properties of the Preview button.

See Below.


I am thinking the preview code will be something like
DoCmd.OpenReport "report01", acPreview, "[Type] = Cmb01 AND [Area] =
Cmb02"

Close, but you need to get the value of the combo boxes, not
their names, and the values must end up in quotes:

DoCmd.OpenReport "report01", acPreview, _
"[Type] = '" & Cmb01 & "' AND [Area] = "' & Cmb02 & "'"

Thank you for your help, it works fine now.

The Text2 and Text3 were just renaming of the fields in the main customer
table.

Back in business now.

Pat
"Marshall Barton" wrote in message
...
Pat Coleman wrote:

Tried to insert the following code in to the OnClick event of the preview
button and it opens the report but does not filter it whatsoever - like
it
is ignoring the two values.

Dim stDocName As String

stDocName = "Statement"
DoCmd.OpenReport stDocName, acPreview, "[Text2] = '" & Cmb01 & "' AND
[Text3] = '" & Cmb02 & "' "


You're missing a comma:

DoCmd.OpenReport stDocName, acPreview, , "[Text2] = . . .

Where did Text1 and Text2 come from? I thought you said the
names of the fields (in the table/query) were named Type and
Area??
--
Marsh
MVP [MS Access]




"Marshall Barton" wrote
Pat Coleman wrote:

Newbie Form / Report using code Question

I am tring to generate a form with two combo boxes, using a 'Value
List'
as
the source type and manually entered values as the row source.
I also want a button called Preview which when pressed will preview a
report
( called report01 ) based on the selection in the two combo boxes.

I want to stick to CODE only , as opposed to writing a query or using
a
macro

So Far I have

Cmb01 will have 3 values "Distribuor"; "Wholesale"; "Retail"

Cmb02 will have 3 values "EastCoast" " MidWest" "WestCoast"

And the preview button.

The report - report01, is pulled from a customer table which includes
two
fields called
1) 'Type' which is either Distributor, Wholesale or Retail.
2) 'Area' which is either EastCoast, MidWest or WestCoast

The question are

1. What is the code, if any is required, for the combo boxes to
capture
the
choice of values picked by the user and where to I paste this code in
the
combo box.

None needed.


2. What would be the corresponding code for the preview button to
capture
the values of the Combo Box and pass it through to the Report.
I assume I paste this code in the 'OnClick' field of the Event Tab in
the
properties of the Preview button.

See Below.


I am thinking the preview code will be something like
DoCmd.OpenReport "report01", acPreview, "[Type] = Cmb01 AND
[Area] =
Cmb02"

Close, but you need to get the value of the combo boxes, not
their names, and the values must end up in quotes:

DoCmd.OpenReport "report01", acPreview, _
"[Type] = '" & Cmb01 & "' AND [Area] = "' & Cmb02 & "'"