Form to generate a Report code

  • Thread starter Thread starter Pat Coleman
  • Start date Start date
P

Pat Coleman

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 said:
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.
Click to expand...

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.
Click to expand...

See Below.

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

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 & "'"
 
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 said:
Pat said:
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.
Click to expand...

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.
Click to expand...

See Below.

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

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 & "'"
Click to expand...
 
Pat said:
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 & "' "
Click to expand...


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]



Pat said:
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.
Click to expand...

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.
Click to expand...

See Below.

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

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 & "'"
Click to expand...
Click to expand...
 
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 said:
Pat said:
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 & "' "
Click to expand...


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]



Pat Coleman wrote:

Newbie Form / Report using code Question

I am tring to generate a form with two combo boxes, using a 'Value
Click to expand...
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
Click to expand...
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 & "'"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top