I'm missing the boat on something simple!

  • Thread starter Thread starter Dick Starr
  • Start date Start date
D

Dick Starr

I have a bartender tip tracking database built.
There is an employee table and a Tip table.

What I want to accomplish is to have the user do the following:
bring up a report that asks for the bartender name (preferrably from a
dropdown list box, and then print a report that lists only that particular
bartender's tips for the (month) lets say.

I believe there is a way to get a query to deliver only the requested data.

What I am getting now is a report for ALL bartenders.

Any assistance, including slaps upside the head will be greatly appreciated.
 
I have a bartender tip tracking database built.
There is an employee table and a Tip table.

What I want to accomplish is to have the user do the following:
bring up a report that asks for the bartender name (preferrably from a
dropdown list box, and then print a report that lists only that particular
bartender's tips for the (month) lets say.

I believe there is a way to get a query to deliver only the requested data.

What I am getting now is a report for ALL bartenders.

Any assistance, including slaps upside the head will be greatly appreciated.
Click to expand...

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a EmployeeID number you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the EmployeeID field as well as the
Employee Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the EmployeeID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
EmployeeID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to a valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's EmployeeID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the Employee Name in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.
 
Fred,

Thanks for the detailed procedure. Now I have soem ID10T problems!

I followed each step of your instructions, added nothing.
The Param form does not open with the report.
There are 3 small parameter boxes opening sequentially asking for name,
startdate, enddate.
The syntax appears correct. I copied your strings and checked for proper
syntax.

Any Ideas?
 
Fred,

Thanks for the detailed procedure. Now I have soem ID10T problems!

I followed each step of your instructions, added nothing.
The Param form does not open with the report.
There are 3 small parameter boxes opening sequentially asking for name,
startdate, enddate.
The syntax appears correct. I copied your strings and checked for proper
syntax.

Any Ideas?
Click to expand...

I suspect you did not correctly enter the coding for the report in the
proper place..

Open the report in design view.
Display the Report's properties box.
Click on the Events tab.
On the Open event property line enter
[Event Procedure]
Click on the little button with 3 dots that appears on that line.
When the Open event code window opens, the cursor will be flashing
between 2 already existing lines of code.
Between those 2 lines write:

DoCmd.OpenForm "ParamForm" , , , , , acDialog

NOTE: Make sure you include the 5 commas and enclose the form name
within quotes, as above.

Exit the code window.

Now do the same thing for the report's Close event, writing:

DoCmd.Close acForm, "ParamForm"

as the code.
Save the report changes.

Now do the same thing for the ParamForm's command button, writing
[Event Procedure]
on the Open event property line and

Me.Visible = False

as it's code. Save the changes.

Open the report in Preview. The report's Open event should now open
the form.
Enter the correct dates and employee in the appropriate controls on
the form. Click the form's Command Button.
The form will become not visible and the report will preview.
When you close the report, it will also close the form.

In the event you still have a problem, place a breakpoint in the
report's open event and step through the code line by line.
 
Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog
End Sub


Criteria line of EmployeeID
[forms]![ParamForm]![cboFindName]

Criteria Line of Date
[forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate]
* That might be it!
Not the problem
Criteria line of query now reads:
Between[forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate]
No change in symptoms.


The query uses 2 tables. Employees and tips
The employeeID and Name fields are taken from the query but are supplied
from the employee table.
Is this a potential issue?


I'm stumped by this one!




:[

Fred,

Thanks for the detailed procedure. Now I have soem ID10T problems!

I followed each step of your instructions, added nothing.
The Param form does not open with the report.
There are 3 small parameter boxes opening sequentially asking for name,
startdate, enddate.
The syntax appears correct. I copied your strings and checked for proper
syntax.

Any Ideas?
Click to expand...

I suspect you did not correctly enter the coding for the report in the
proper place..

Open the report in design view.
Display the Report's properties box.
Click on the Events tab.
On the Open event property line enter
[Event Procedure]
Click on the little button with 3 dots that appears on that line.
When the Open event code window opens, the cursor will be flashing
between 2 already existing lines of code.
Between those 2 lines write:

DoCmd.OpenForm "ParamForm" , , , , , acDialog

NOTE: Make sure you include the 5 commas and enclose the form name
within quotes, as above.

Exit the code window.

Now do the same thing for the report's Close event, writing:

DoCmd.Close acForm, "ParamForm"

as the code.
Save the report changes.

Now do the same thing for the ParamForm's command button, writing
[Event Procedure]
on the Open event property line and

Me.Visible = False

as it's code. Save the changes.

Open the report in Preview. The report's Open event should now open
the form.
Enter the correct dates and employee in the appropriate controls on
the form. Click the form's Command Button.
The form will become not visible and the report will preview.
When you close the report, it will also close the form.

In the event you still have a problem, place a breakpoint in the
report's open event and step through the code line by line.
Click to expand...
 
Hi,

I got nearly everything running. The security function in access 2007 had
macros and vb modules locked out.

Everything but the combo box works. BUT, It's blank.
I rebuilt the entire query, form and report to be sure it was not something
I missed.
Any suggestions?
Now, this is just me, but...
If the query has not yet run, and there's no data, how does the drop down
box get populated?


Dick Starr said:
Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog
End Sub


Criteria line of EmployeeID
[forms]![ParamForm]![cboFindName]

Criteria Line of Date
[forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate]
* That might be it!
Not the problem
Criteria line of query now reads:
Between[forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate]
No change in symptoms.


The query uses 2 tables. Employees and tips
The employeeID and Name fields are taken from the query but are supplied
from the employee table.
Is this a potential issue?


I'm stumped by this one!




:[

Fred,

Thanks for the detailed procedure. Now I have soem ID10T problems!

I followed each step of your instructions, added nothing.
The Param form does not open with the report.
There are 3 small parameter boxes opening sequentially asking for name,
startdate, enddate.
The syntax appears correct. I copied your strings and checked for proper
syntax.

Any Ideas?

:

On Thu, 8 Jan 2009 07:36:01 -0800, Dick Starr wrote:

I have a bartender tip tracking database built.
There is an employee table and a Tip table.

What I want to accomplish is to have the user do the following:
bring up a report that asks for the bartender name (preferrably from a
dropdown list box, and then print a report that lists only that particular
bartender's tips for the (month) lets say.

I believe there is a way to get a query to deliver only the requested data.

What I am getting now is a report for ALL bartenders.

Any assistance, including slaps upside the head will be greatly appreciated.

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a EmployeeID number you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the EmployeeID field as well as the
Employee Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the EmployeeID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
EmployeeID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to a valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's EmployeeID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the Employee Name in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.
Click to expand...

I suspect you did not correctly enter the coding for the report in the
proper place..

Open the report in design view.
Display the Report's properties box.
Click on the Events tab.
On the Open event property line enter
[Event Procedure]
Click on the little button with 3 dots that appears on that line.
When the Open event code window opens, the cursor will be flashing
between 2 already existing lines of code.
Between those 2 lines write:

DoCmd.OpenForm "ParamForm" , , , , , acDialog

NOTE: Make sure you include the 5 commas and enclose the form name
within quotes, as above.

Exit the code window.

Now do the same thing for the report's Close event, writing:

DoCmd.Close acForm, "ParamForm"

as the code.
Save the report changes.

Now do the same thing for the ParamForm's command button, writing
[Event Procedure]
on the Open event property line and

Me.Visible = False

as it's code. Save the changes.

Open the report in Preview. The report's Open event should now open
the form.
Enter the correct dates and employee in the appropriate controls on
the form. Click the form's Command Button.
The form will become not visible and the report will preview.
When you close the report, it will also close the form.

In the event you still have a problem, place a breakpoint in the
report's open event and step through the code line by line.
Click to expand...
Click to expand...
 
Hi,

I got nearly everything running. The security function in access 2007 had
macros and vb modules locked out.

Everything but the combo box works. BUT, It's blank.
I rebuilt the entire query, form and report to be sure it was not something
I missed.
Any suggestions?
Now, this is just me, but...
If the query has not yet run, and there's no data, how does the drop down
box get populated?

Dick Starr said:
Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog
End Sub

Criteria line of EmployeeID
[forms]![ParamForm]![cboFindName]

Criteria Line of Date
[forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate]
* That might be it!
Not the problem
Criteria line of query now reads:
Between[forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate]
No change in symptoms.

The query uses 2 tables. Employees and tips
The employeeID and Name fields are taken from the query but are supplied
from the employee table.
Is this a potential issue?

I'm stumped by this one!

:[
On Fri, 9 Jan 2009 07:30:10 -0800, Dick Starr wrote:

Fred,

Thanks for the detailed procedure. Now I have soem ID10T problems!

I followed each step of your instructions, added nothing.
The Param form does not open with the report.
There are 3 small parameter boxes opening sequentially asking for name,
startdate, enddate.
The syntax appears correct. I copied your strings and checked for proper
syntax.

Any Ideas?

:

On Thu, 8 Jan 2009 07:36:01 -0800, Dick Starr wrote:

I have a bartender tip tracking database built.
There is an employee table and a Tip table.

What I want to accomplish is to have the user do the following:
bring up a report that asks for the bartender name (preferrably from a
dropdown list box, and then print a report that lists only that particular
bartender's tips for the (month) lets say.

I believe there is a way to get a query to deliver only the requested data.

What I am getting now is a report for ALL bartenders.

Any assistance, including slaps upside the head will be greatly appreciated.

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a EmployeeID number you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the EmployeeID field as well as the
Employee Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the EmployeeID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
EmployeeID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to a valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's EmployeeID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the Employee Name in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


I suspect you did not correctly enter the coding for the report in the
proper place..

Open the report in design view.
Display the Report's properties box.
Click on the Events tab.
On the Open event property line enter
[Event Procedure]
Click on the little button with 3 dots that appears on that line.
When the Open event code window opens, the cursor will be flashing
between 2 already existing lines of code.
Between those 2 lines write:

DoCmd.OpenForm "ParamForm" , , , , , acDialog

NOTE: Make sure you include the 5 commas and enclose the form name
within quotes, as above.

Exit the code window.

Now do the same thing for the report's Close event, writing:

DoCmd.Close acForm, "ParamForm"

as the code.
Save the report changes.

Now do the same thing for the ParamForm's command button, writing
[Event Procedure]
on the Open event property line and

Me.Visible = False

as it's code. Save the changes.

Open the report in Preview. The report's Open event should now open
the form.
Enter the correct dates and employee in the appropriate controls on
the form. Click the form's Command Button.
The form will become not visible and the report will preview.
When you close the report, it will also close the form.

In the event you still have a problem, place a breakpoint in the
report's open event and step through the code line by line.
Click to expand...
Click to expand...
Click to expand...

The drop-down gets populated via it's own Rowsource property.
In Form Design view, click on the combo box property sheet's Data tab.
Click on the Rowsource property. Then click on the little button with
the 3 dots that appears on that line.
The query design grid will appear. Add the necessary table to get the
EmployeeID and Employee Names and proceed as though you were creating
a new query to display the EmployeeID and their Names. Save the
changes.
OR....
Add a new combo box to the form using the Combo Wizard. It will walk
you through the necessary steps.
 
Hi Fred,

You are right on the money.
Somehow, the wizard didn't set up the rowsource correctly.
I fixed that and everything works perfectly.
Now I can finish the rest of the reports.
Thanks for all your assistance with this.

Dick Starr

fredg said:
Hi,

I got nearly everything running. The security function in access 2007 had
macros and vb modules locked out.

Everything but the combo box works. BUT, It's blank.
I rebuilt the entire query, form and report to be sure it was not something
I missed.
Any suggestions?
Now, this is just me, but...
If the query has not yet run, and there's no data, how does the drop down
box get populated?

Dick Starr said:
Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog
End Sub

Criteria line of EmployeeID
[forms]![ParamForm]![cboFindName]

Criteria Line of Date
[forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate]
* That might be it!
Not the problem
Criteria line of query now reads:
Between[forms]![ParamForm]![StartDate] And [forms]![ParamForm]![EndDate]
No change in symptoms.

The query uses 2 tables. Employees and tips
The employeeID and Name fields are taken from the query but are supplied
from the employee table.
Is this a potential issue?

I'm stumped by this one!

:[

On Fri, 9 Jan 2009 07:30:10 -0800, Dick Starr wrote:

Fred,

Thanks for the detailed procedure. Now I have soem ID10T problems!

I followed each step of your instructions, added nothing.
The Param form does not open with the report.
There are 3 small parameter boxes opening sequentially asking for name,
startdate, enddate.
The syntax appears correct. I copied your strings and checked for proper
syntax.

Any Ideas?

:

On Thu, 8 Jan 2009 07:36:01 -0800, Dick Starr wrote:

I have a bartender tip tracking database built.
There is an employee table and a Tip table.

What I want to accomplish is to have the user do the following:
bring up a report that asks for the bartender name (preferrably from a
dropdown list box, and then print a report that lists only that particular
bartender's tips for the (month) lets say.

I believe there is a way to get a query to deliver only the requested data.

What I am getting now is a report for ALL bartenders.

Any assistance, including slaps upside the head will be greatly appreciated.

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a EmployeeID number you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the EmployeeID field as well as the
Employee Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the EmployeeID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
EmployeeID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to a valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's EmployeeID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the Employee Name in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


I suspect you did not correctly enter the coding for the report in the
proper place..

Open the report in design view.
Display the Report's properties box.
Click on the Events tab.
On the Open event property line enter
[Event Procedure]
Click on the little button with 3 dots that appears on that line.
When the Open event code window opens, the cursor will be flashing
between 2 already existing lines of code.
Between those 2 lines write:

DoCmd.OpenForm "ParamForm" , , , , , acDialog

NOTE: Make sure you include the 5 commas and enclose the form name
within quotes, as above.

Exit the code window.

Now do the same thing for the report's Close event, writing:

DoCmd.Close acForm, "ParamForm"

as the code.
Save the report changes.

Now do the same thing for the ParamForm's command button, writing
[Event Procedure]
on the Open event property line and

Me.Visible = False

as it's code. Save the changes.

Open the report in Preview. The report's Open event should now open
the form.
Enter the correct dates and employee in the appropriate controls on
the form. Click the form's Command Button.
The form will become not visible and the report will preview.
When you close the report, it will also close the form.

In the event you still have a problem, place a breakpoint in the
report's open event and step through the code line by line.
Click to expand...
Click to expand...

The drop-down gets populated via it's own Rowsource property.
In Form Design view, click on the combo box property sheet's Data tab.
Click on the Rowsource property. Then click on the little button with
the 3 dots that appears on that line.
The query design grid will appear. Add the necessary table to get the
EmployeeID and Employee Names and proceed as though you were creating
a new query to display the EmployeeID and their Names. Save the
changes.
OR....
Add a new combo box to the form using the Combo Wizard. It will walk
you through the necessary steps.
Click to expand...
 
Excuse me for butting in but this is just what I'm looking for too. I
followed the instructions you gave Dick and managed to open the report. The
only trouble is that there is nothing in it when I know there should be. Am I
missing something fundamental?
 
Back
Top