Queries again and again...

[SusanM]

Mr. Vinson, in response to your replies to my question:
I am quite sure that I do not know all the ways that
Access was designed to work. I do not have any VB
knowledge. I have been using Access for about 6 months. I
am sure that if talked to 5 different people, I would get
5 different suggestions to my question. I have 800
employees in my database. I am tracking daily attendance
for everyday of the year. I have a form with that
includes name, id, dept, date of hire etc...
On the menu I have created for data entry, when I
click "daily attendance by employee"
I want to be able to type in criteria that will bring up
a specific employee, specific month, specific month,
specific day and see that employee's activity.
I have a macro set up on the command button, parameter
queries ask: year? month? day? id?
I would appreciate any help or suggestions from anyone
out there. As always, thanks to everyone for all your
help and patience. SusanM

 

[Dan Artuso]

Hi Susan,
I just read your previous post and John's reply.
All you have to do is follow his steps.

1) Create a query with your 4 paramters
2) Create a form that will display the results of the query. The
recordsource of the form will be your query
3) in your macro:
-simply open the form
-the user will then be prompted to enter the parameters
-your form will open with matching result(s) displayed

 

[SusanM]

Hello, been instructed to follow up this instead of
creating new questions. Sorry did not know procedure
Below steps have almost worked. The form, opens blank, in
form view, but the design view is correct. Any
suggestions?
Thanks

-----Original Message-----
Hi Susan,
I just read your previous post and John's reply.
All you have to do is follow his steps.

1) Create a query with your 4 paramters
2) Create a form that will display the results of the query. The
recordsource of the form will be your query
3) in your macro:
-simply open the form
-the user will then be prompted to enter the parameters
-your form will open with matching result(s) displayed

--
HTH
Dan Artuso, Access MVP


"SusanM" <[email protected]> wrote in

message news:[email protected]...

 

[John Vinson]

Hello, been instructed to follow up this instead of
creating new questions. Sorry did not know procedure
Below steps have almost worked. The form, opens blank, in
form view, but the design view is correct. Any
suggestions?
Thanks

Susan,

Please give us some help here.

YOU can see your database. We cannot.

Please post the following:

- The steps of your Macro
- The SQL view of the parameter queries (again, you should probably
not be running any parameter queries at all!!!!)
- The Recordsource property of the Form that's coming up blank (again
in SQL view)

 

[SusanM]

Here we go:
I have a table with 800 employees.(id#,last name, first
name,date of hire etc...And a calendar year table with
id, year, month, day.
I need to bring up a specific employee on a form by id,
yr, month and day to track attendance.
What I have done so far:
1. created a query with 4 parameters, yr, month, day,id.
2. created a form based on this query. the recordsource
for this form is the query.
3. created a command button using a macro: "open form"
It asks the 4 parameter values, I key those in and
his is where is comes up a blank form. The design view
looks fine. click to form view and it is blank.
If I do not use the parameters, it works fine, but brings
all 800 employees up.
I apologize to all for any inconvience this is causing
the newsgroup.
SusanM

 

[John Vinson]

Here we go:
I have a table with 800 employees.(id#,last name, first
name,date of hire etc...And a calendar year table with
id, year, month, day.
I need to bring up a specific employee on a form by id,
yr, month and day to track attendance.
What I have done so far:
1. created a query with 4 parameters, yr, month, day,id.

Please open this Query in design view. On the menu click View... and
select SQL. Copy and paste the SQL text of this Query to a reply to
this message. This will enable me to understand what the query is
doing.

2. created a form based on this query. the recordsource
for this form is the query.
Excellent.

3. created a command button using a macro: "open form"
It asks the 4 parameter values, I key those in and
his is where is comes up a blank form.

How does the macro "ask for" parameters? Normally the QUERY ITSELF
would prompt for the parameters, either by using a parameter such as

[Enter ID#:]

or (better) by using a small unbound Form, named perhaps frmCrit; this
would have controls such as a combo box named cboEmployee and you'ld
use a criterion of

=[Forms]!frmCrit!cboEmployee

In either case, your Macro would open the Form, which would invoke the
Query, which would either prompt for the parameters (the first method)
or read them directly from the Form (the second). There would be no
need to deal with the parameters in the Macro at all, and in fact I
can't think of a good way to do so!

The design view
looks fine. click to form view and it is blank.
If I do not use the parameters, it works fine, but brings
all 800 employees up.

It's clearly a problem with the Query, and if you could post the SQL
view of the query (as I've requested a couple of times), we should be
able to solve that problem.

I apologize to all for any inconvience this is causing
the newsgroup.

I realize it takes a while to figure out the jargon - obviously if the
term "SQL view" is new to you, it'll be hard to figure out what I'm
asking! Hope the instructions above help.

 

[Dan Artuso]

Hi Susan,
You're not causing any inconvience, it's just that we're trying to get you
to post the relevant info.
The form is probably coming up blank because there are no results
returned from the query for the criteria you entered.

The problem is with the query criteria. Copy the SQL view of your query and post
it here.

 

[SusanM]

Good Afternoon Gentlemen,
Thank you again for your response Below is the SQL view
of the query.

SELECT [DAILY ATTEND].YEAR, [DAILY ATTEND].MONTH, [DAILY
ATTEND].DAY, [DAILY ATTEND].[FILE ID], [DAILY
ATTEND].DESCRIP, [DAILY ATTEND].CODE
FROM [DAILY ATTEND]
WHERE ((([DAILY ATTEND].YEAR)=[Enter Year]) AND (([DAILY
ATTEND].MONTH)=[Enter Month]) AND (([DAILY ATTEND].DAY)=
[Enter Day]) AND (([DAILY ATTEND].[FILE ID])=[Enter File
ID]));

-----Original Message-----

Here we go:
I have a table with 800 employees.(id#,last name, first
name,date of hire etc...And a calendar year table with
id, year, month, day.
I need to bring up a specific employee on a form by id,
yr, month and day to track attendance.
What I have done so far:
1. created a query with 4 parameters, yr, month, day,id.

Please open this Query in design view. On the menu click View... and
select SQL. Copy and paste the SQL text of this Query to a reply to
this message. This will enable me to understand what the query is
doing.

2. created a form based on this query. the recordsource
for this form is the query.
Excellent.

3. created a command button using a macro: "open form"
It asks the 4 parameter values, I key those in and
his is where is comes up a blank form.

How does the macro "ask for" parameters? Normally the QUERY ITSELF
would prompt for the parameters, either by using a parameter such as

[Enter ID#:]

or (better) by using a small unbound Form, named perhaps frmCrit; this
would have controls such as a combo box named cboEmployee and you'ld
use a criterion of

=[Forms]!frmCrit!cboEmployee

In either case, your Macro would open the Form, which would invoke the
Query, which would either prompt for the parameters (the first method)
or read them directly from the Form (the second). There would be no
need to deal with the parameters in the Macro at all, and in fact I
can't think of a good way to do so!

The design view
looks fine. click to form view and it is blank.
If I do not use the parameters, it works fine, but brings
all 800 employees up.

It's clearly a problem with the Query, and if you could post the SQL
view of the query (as I've requested a couple of times), we should be
able to solve that problem.

I apologize to all for any inconvience this is causing
the newsgroup.

I realize it takes a while to figure out the jargon - obviously if the
term "SQL view" is new to you, it'll be hard to figure out what I'm
asking! Hope the instructions above help.


.

 

[John Vinson]

Good Afternoon Gentlemen,
Thank you again for your response Below is the SQL view
of the query.

SELECT [DAILY ATTEND].YEAR, [DAILY ATTEND].MONTH, [DAILY
ATTEND].DAY, [DAILY ATTEND].[FILE ID], [DAILY
ATTEND].DESCRIP, [DAILY ATTEND].CODE
FROM [DAILY ATTEND]
WHERE ((([DAILY ATTEND].YEAR)=[Enter Year]) AND (([DAILY
ATTEND].MONTH)=[Enter Month]) AND (([DAILY ATTEND].DAY)=
[Enter Day]) AND (([DAILY ATTEND].[FILE ID])=[Enter File
ID]));

Thanks Susan. This should work if your macro just opens the Form, IF
the user responds to every one of the prompts with a valid value and
if the four values entered all match records in the table. If any one
of the prompts is left blank, or there is a mismatch in any of the
fields, you'll get what you're seeing - a blank form.

One concern I'd have is that the words YEAR and MONTH and DAY are all
reserved words (for the date/time functions Year(), Month() and Day()
- you may want to either change the fieldnames in your table, or
consider storing the attendance date in a Date/Time field instead of
"rolling your own" three fields!