Table design problem?

[Melissa]

I put a command button on my form. I then placed the code you provided in the
code builder window using the on click event, only entering the name of the
report. Code as follow.


DoCmd.OpenReport "Agent Production Report", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

The error I am getting is "The specified field '[EmpID]' could refer to more
than one table listed in the FROM clause of your SQL Statement.

Now I do understand this because there is a main form with a subform within
this form and both tables have EmpID.

I have made a work around and put a command button on the form to preview
the report. But the query will prompt them to enter their name. On the date
completed I put =Date() so that part worked. I just need to eliminate the
need for them to enter thieir name.

Any suggestions.

thanks again..

Yes, you have properly related the Employeetbl to EmployeeHrs, and
Employeetbl linked to Corresp Reced Completed through the EmpID fields.

Yes, the code I posted would be used "as is" except change ReportName to the
actual name of the report.
--

Ken Snell
<MS ACCESS MVP>

Ok, I understand your answer on both but, I have follow ups.
On how I have my relationships set up currently,
Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to many).
Both by EmpID fields. Is this good? I just don't want to run into any
problems in the future.

Also, the button I could put on the form to run the report. Will the only
area I need to for the line of code is the Report Name?

Thanks again .

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I think I have everything in place. I have three tbls. I am only giving
the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the
composite
keys by the way.

I would think that you also would want a tblAuditTypes table to hold the
AuditType values, and its primary key would be the foreign key
"AuditType"
in the Correpondence Received Completed tbl.



My question is, I have my Employeetbl linked to EmployeeHrs (one to
many)
and also the Employeetbl linked to Corresp Reced Completed (one to
many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl (one
to
many)? When I try to do this I get a relationship type of
"Indeterminate",
what is that? I will begin to work on my reports next week and I do not
want
to run into trouble.

No, there is no need to have any type of link between EmployeeHrs and
Correpondence Received Completed tbl. The EmployeeHrs contains no field
that
would connect to the purpose of the Correpondence Received Completed tbl.
The reason for the "Indeterminate" error (when, I assume, you try to
create
a relationship between EmpID field in both tables) is because neither
table
uses EmpID as a primary key (by itself), so ACCESS would see this join as
a
"many-to-many", which it does not support directly for a relationship
setting.



Second Question:
I have my Data Entry form set up and is running great. If you recall,
My
main form is Employee Hrs (which can only have one record per day per
agent)
and the Subform is Correp Recd Completed (which can have multi entries
per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production that they
entered that day, how can I set this up with a button? If they print
record
it comes up blank because of the form being set to data entry only.
Also,
if
that is change only the main form can be printed?

You'd want a report that is based on a query that uses the Employee tbl
and
the Correpondence Received Completed tbl, joined by the EmpID fields.
Then
you could code a button on the form that would open the report, which you
would filter based on the current date and the EmpID:


DoCmd.OpenReport "ReportName", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date(), "mm\/dd\/yyyy") & "#"



Thank you so much for all your guidance. You have been a great help to
me.

You're welcome.





< snipped >

 

[Ken Snell [MVP]]

Not knowing what the SQL statement of the report's RecordSource query is,
I'm guessing that it contains at least two fields with EmpID as the
identifier name. That means that the report will need to be given the table
name and the field name in the fourth argument of the DoCmd.OpenReport step.

But to advise further, I'll need the report's SQL statement for its
recordsource.
--

Ken Snell
<MS ACCESS MVP>

I put a command button on my form. I then placed the code you provided in
the
code builder window using the on click event, only entering the name of
the
report. Code as follow.


DoCmd.OpenReport "Agent Production Report", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

The error I am getting is "The specified field '[EmpID]' could refer to
more
than one table listed in the FROM clause of your SQL Statement.

Now I do understand this because there is a main form with a subform
within
this form and both tables have EmpID.

I have made a work around and put a command button on the form to preview
the report. But the query will prompt them to enter their name. On the
date
completed I put =Date() so that part worked. I just need to eliminate the
need for them to enter thieir name.

Any suggestions.

thanks again..

Yes, you have properly related the Employeetbl to EmployeeHrs, and
Employeetbl linked to Corresp Reced Completed through the EmpID fields.

Yes, the code I posted would be used "as is" except change ReportName to
the
actual name of the report.
--

Ken Snell
<MS ACCESS MVP>

Ok, I understand your answer on both but, I have follow ups.
On how I have my relationships set up currently,
Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to
many).
Both by EmpID fields. Is this good? I just don't want to run into any
problems in the future.

Also, the button I could put on the form to run the report. Will the
only
area I need to for the line of code is the Report Name?

Thanks again .

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I think I have everything in place. I have three tbls. I am only
giving
the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the
composite
keys by the way.

I would think that you also would want a tblAuditTypes table to hold
the
AuditType values, and its primary key would be the foreign key
"AuditType"
in the Correpondence Received Completed tbl.



My question is, I have my Employeetbl linked to EmployeeHrs (one to
many)
and also the Employeetbl linked to Corresp Reced Completed (one to
many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl
(one
to
many)? When I try to do this I get a relationship type of
"Indeterminate",
what is that? I will begin to work on my reports next week and I do
not
want
to run into trouble.

No, there is no need to have any type of link between EmployeeHrs and
Correpondence Received Completed tbl. The EmployeeHrs contains no
field
that
would connect to the purpose of the Correpondence Received Completed
tbl.
The reason for the "Indeterminate" error (when, I assume, you try to
create
a relationship between EmpID field in both tables) is because neither
table
uses EmpID as a primary key (by itself), so ACCESS would see this join
as
a
"many-to-many", which it does not support directly for a relationship
setting.



Second Question:
I have my Data Entry form set up and is running great. If you
recall,
My
main form is Employee Hrs (which can only have one record per day
per
agent)
and the Subform is Correp Recd Completed (which can have multi
entries
per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production that
they
entered that day, how can I set this up with a button? If they print
record
it comes up blank because of the form being set to data entry only.
Also,
if
that is change only the main form can be printed?

You'd want a report that is based on a query that uses the Employee
tbl
and
the Correpondence Received Completed tbl, joined by the EmpID fields.
Then
you could code a button on the form that would open the report, which
you
would filter based on the current date and the EmpID:


DoCmd.OpenReport "ReportName", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date(), "mm\/dd\/yyyy") & "#"



Thank you so much for all your guidance. You have been a great help
to
me.

You're welcome.





< snipped >

 

[Melissa]

Here is the SQL statement.

SELECT EmployeeHrs.*, [Correspondence Received_Completed].*, [Correspondence
Received_Completed].DateCompleted
FROM Agents INNER JOIN (EmployeeHrs INNER JOIN [Correspondence
Received_Completed] ON (EmployeeHrs.EmpID = [Correspondence
Received_Completed].EmpID) AND (EmployeeHrs.DateCompleted = [Correspondence
Received_Completed].DateCompleted)) ON (Agents.EmpID = EmployeeHrs.EmpID) AND
(Agents.EmpID = [Correspondence Received_Completed].EmpID)
WHERE ((([Correspondence Received_Completed].DateCompleted) Between [Enter
the beginning date] And [Enter the ending Date]))
WITH OWNERACCESS OPTION;

Not knowing what the SQL statement of the report's RecordSource query is,
I'm guessing that it contains at least two fields with EmpID as the
identifier name. That means that the report will need to be given the table
name and the field name in the fourth argument of the DoCmd.OpenReport step.

But to advise further, I'll need the report's SQL statement for its
recordsource.
--

Ken Snell
<MS ACCESS MVP>

I put a command button on my form. I then placed the code you provided in
the
code builder window using the on click event, only entering the name of
the
report. Code as follow.


DoCmd.OpenReport "Agent Production Report", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

The error I am getting is "The specified field '[EmpID]' could refer to
more
than one table listed in the FROM clause of your SQL Statement.

Now I do understand this because there is a main form with a subform
within
this form and both tables have EmpID.

I have made a work around and put a command button on the form to preview
the report. But the query will prompt them to enter their name. On the
date
completed I put =Date() so that part worked. I just need to eliminate the
need for them to enter thieir name.

Any suggestions.

thanks again..

Yes, you have properly related the Employeetbl to EmployeeHrs, and
Employeetbl linked to Corresp Reced Completed through the EmpID fields.

Yes, the code I posted would be used "as is" except change ReportName to
the
actual name of the report.
--

Ken Snell
<MS ACCESS MVP>



Ok, I understand your answer on both but, I have follow ups.
On how I have my relationships set up currently,
Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to
many).
Both by EmpID fields. Is this good? I just don't want to run into any
problems in the future.

Also, the button I could put on the form to run the report. Will the
only
area I need to for the line of code is the Report Name?

Thanks again .

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I think I have everything in place. I have three tbls. I am only
giving
the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the
composite
keys by the way.

I would think that you also would want a tblAuditTypes table to hold
the
AuditType values, and its primary key would be the foreign key
"AuditType"
in the Correpondence Received Completed tbl.



My question is, I have my Employeetbl linked to EmployeeHrs (one to
many)
and also the Employeetbl linked to Corresp Reced Completed (one to
many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl
(one
to
many)? When I try to do this I get a relationship type of
"Indeterminate",
what is that? I will begin to work on my reports next week and I do
not
want
to run into trouble.

No, there is no need to have any type of link between EmployeeHrs and
Correpondence Received Completed tbl. The EmployeeHrs contains no
field
that
would connect to the purpose of the Correpondence Received Completed
tbl.
The reason for the "Indeterminate" error (when, I assume, you try to
create
a relationship between EmpID field in both tables) is because neither
table
uses EmpID as a primary key (by itself), so ACCESS would see this join
as
a
"many-to-many", which it does not support directly for a relationship
setting.



Second Question:
I have my Data Entry form set up and is running great. If you
recall,
My
main form is Employee Hrs (which can only have one record per day
per
agent)
and the Subform is Correp Recd Completed (which can have multi
entries
per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production that
they
entered that day, how can I set this up with a button? If they print
record
it comes up blank because of the form being set to data entry only.
Also,
if
that is change only the main form can be printed?

You'd want a report that is based on a query that uses the Employee
tbl
and
the Correpondence Received Completed tbl, joined by the EmpID fields.
Then
you could code a button on the form that would open the report, which
you
would filter based on the current date and the EmpID:


DoCmd.OpenReport "ReportName", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date(), "mm\/dd\/yyyy") & "#"



Thank you so much for all your guidance. You have been a great help
to
me.

You're welcome.





< snipped >

 

[Ken Snell [MVP]]

OK - try this:

DoCmd.OpenReport "Agent Production Report", , , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

--

Ken Snell
<MS ACCESS MVP>

Here is the SQL statement.

SELECT EmployeeHrs.*, [Correspondence Received_Completed].*,
[Correspondence
Received_Completed].DateCompleted
FROM Agents INNER JOIN (EmployeeHrs INNER JOIN [Correspondence
Received_Completed] ON (EmployeeHrs.EmpID = [Correspondence
Received_Completed].EmpID) AND (EmployeeHrs.DateCompleted =
[Correspondence
Received_Completed].DateCompleted)) ON (Agents.EmpID = EmployeeHrs.EmpID)
AND
(Agents.EmpID = [Correspondence Received_Completed].EmpID)
WHERE ((([Correspondence Received_Completed].DateCompleted) Between [Enter
the beginning date] And [Enter the ending Date]))
WITH OWNERACCESS OPTION;

Not knowing what the SQL statement of the report's RecordSource query is,
I'm guessing that it contains at least two fields with EmpID as the
identifier name. That means that the report will need to be given the
table
name and the field name in the fourth argument of the DoCmd.OpenReport
step.

But to advise further, I'll need the report's SQL statement for its
recordsource.
--

Ken Snell
<MS ACCESS MVP>

I put a command button on my form. I then placed the code you provided
in
the
code builder window using the on click event, only entering the name of
the
report. Code as follow.


DoCmd.OpenReport "Agent Production Report", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

The error I am getting is "The specified field '[EmpID]' could refer to
more
than one table listed in the FROM clause of your SQL Statement.

Now I do understand this because there is a main form with a subform
within
this form and both tables have EmpID.

I have made a work around and put a command button on the form to
preview
the report. But the query will prompt them to enter their name. On the
date
completed I put =Date() so that part worked. I just need to eliminate
the
need for them to enter thieir name.

Any suggestions.

thanks again..


:

Yes, you have properly related the Employeetbl to EmployeeHrs, and
Employeetbl linked to Corresp Reced Completed through the EmpID
fields.

Yes, the code I posted would be used "as is" except change ReportName
to
the
actual name of the report.
--

Ken Snell
<MS ACCESS MVP>



Ok, I understand your answer on both but, I have follow ups.
On how I have my relationships set up currently,
Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to
many).
Both by EmpID fields. Is this good? I just don't want to run into
any
problems in the future.

Also, the button I could put on the form to run the report. Will the
only
area I need to for the line of code is the Report Name?

Thanks again .

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I think I have everything in place. I have three tbls. I am only
giving
the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the
composite
keys by the way.

I would think that you also would want a tblAuditTypes table to
hold
the
AuditType values, and its primary key would be the foreign key
"AuditType"
in the Correpondence Received Completed tbl.



My question is, I have my Employeetbl linked to EmployeeHrs (one
to
many)
and also the Employeetbl linked to Corresp Reced Completed (one
to
many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl
(one
to
many)? When I try to do this I get a relationship type of
"Indeterminate",
what is that? I will begin to work on my reports next week and I
do
not
want
to run into trouble.

No, there is no need to have any type of link between EmployeeHrs
and
Correpondence Received Completed tbl. The EmployeeHrs contains no
field
that
would connect to the purpose of the Correpondence Received
Completed
tbl.
The reason for the "Indeterminate" error (when, I assume, you try
to
create
a relationship between EmpID field in both tables) is because
neither
table
uses EmpID as a primary key (by itself), so ACCESS would see this
join
as
a
"many-to-many", which it does not support directly for a
relationship
setting.



Second Question:
I have my Data Entry form set up and is running great. If you
recall,
My
main form is Employee Hrs (which can only have one record per day
per
agent)
and the Subform is Correp Recd Completed (which can have multi
entries
per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production
that
they
entered that day, how can I set this up with a button? If they
print
record
it comes up blank because of the form being set to data entry
only.
Also,
if
that is change only the main form can be printed?

You'd want a report that is based on a query that uses the Employee
tbl
and
the Correpondence Received Completed tbl, joined by the EmpID
fields.
Then
you could code a button on the form that would open the report,
which
you
would filter based on the current date and the EmpID:


DoCmd.OpenReport "ReportName", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date(), "mm\/dd\/yyyy") & "#"



Thank you so much for all your guidance. You have been a great
help
to
me.

You're welcome.





< snipped >

 

[Melissa]

It worked!! Thank you so much for all your help.

OK - try this:

DoCmd.OpenReport "Agent Production Report", , , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

--

Ken Snell
<MS ACCESS MVP>

Here is the SQL statement.

SELECT EmployeeHrs.*, [Correspondence Received_Completed].*,
[Correspondence
Received_Completed].DateCompleted
FROM Agents INNER JOIN (EmployeeHrs INNER JOIN [Correspondence
Received_Completed] ON (EmployeeHrs.EmpID = [Correspondence
Received_Completed].EmpID) AND (EmployeeHrs.DateCompleted =
[Correspondence
Received_Completed].DateCompleted)) ON (Agents.EmpID = EmployeeHrs.EmpID)
AND
(Agents.EmpID = [Correspondence Received_Completed].EmpID)
WHERE ((([Correspondence Received_Completed].DateCompleted) Between [Enter
the beginning date] And [Enter the ending Date]))
WITH OWNERACCESS OPTION;

Not knowing what the SQL statement of the report's RecordSource query is,
I'm guessing that it contains at least two fields with EmpID as the
identifier name. That means that the report will need to be given the
table
name and the field name in the fourth argument of the DoCmd.OpenReport
step.

But to advise further, I'll need the report's SQL statement for its
recordsource.
--

Ken Snell
<MS ACCESS MVP>



I put a command button on my form. I then placed the code you provided
in
the
code builder window using the on click event, only entering the name of
the
report. Code as follow.


DoCmd.OpenReport "Agent Production Report", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

The error I am getting is "The specified field '[EmpID]' could refer to
more
than one table listed in the FROM clause of your SQL Statement.

Now I do understand this because there is a main form with a subform
within
this form and both tables have EmpID.

I have made a work around and put a command button on the form to
preview
the report. But the query will prompt them to enter their name. On the
date
completed I put =Date() so that part worked. I just need to eliminate
the
need for them to enter thieir name.

Any suggestions.

thanks again..


:

Yes, you have properly related the Employeetbl to EmployeeHrs, and
Employeetbl linked to Corresp Reced Completed through the EmpID
fields.

Yes, the code I posted would be used "as is" except change ReportName
to
the
actual name of the report.
--

Ken Snell
<MS ACCESS MVP>



Ok, I understand your answer on both but, I have follow ups.
On how I have my relationships set up currently,
Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to
many).
Both by EmpID fields. Is this good? I just don't want to run into
any
problems in the future.

Also, the button I could put on the form to run the report. Will the
only
area I need to for the line of code is the Report Name?

Thanks again .

:

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I think I have everything in place. I have three tbls. I am only
giving
the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the
composite
keys by the way.

I would think that you also would want a tblAuditTypes table to
hold
the
AuditType values, and its primary key would be the foreign key
"AuditType"
in the Correpondence Received Completed tbl.



My question is, I have my Employeetbl linked to EmployeeHrs (one
to
many)
and also the Employeetbl linked to Corresp Reced Completed (one
to
many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl
(one
to
many)? When I try to do this I get a relationship type of
"Indeterminate",
what is that? I will begin to work on my reports next week and I
do
not
want
to run into trouble.

No, there is no need to have any type of link between EmployeeHrs
and
Correpondence Received Completed tbl. The EmployeeHrs contains no
field
that
would connect to the purpose of the Correpondence Received
Completed
tbl.
The reason for the "Indeterminate" error (when, I assume, you try
to
create
a relationship between EmpID field in both tables) is because
neither
table
uses EmpID as a primary key (by itself), so ACCESS would see this
join
as
a
"many-to-many", which it does not support directly for a
relationship
setting.



Second Question:
I have my Data Entry form set up and is running great. If you
recall,
My
main form is Employee Hrs (which can only have one record per day
per
agent)
and the Subform is Correp Recd Completed (which can have multi
entries
per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production
that
they
entered that day, how can I set this up with a button? If they
print
record
it comes up blank because of the form being set to data entry
only.
Also,
if
that is change only the main form can be printed?

You'd want a report that is based on a query that uses the Employee
tbl
and
the Correpondence Received Completed tbl, joined by the EmpID
fields.
Then
you could code a button on the form that would open the report,
which
you
would filter based on the current date and the EmpID:


DoCmd.OpenReport "ReportName", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date(), "mm\/dd\/yyyy") & "#"



Thank you so much for all your guidance. You have been a great
help
to
me.

You're welcome.





< snipped >

 

[Ken Snell [MVP]]

Glad to hear it... good luck, and you're welcome.
--

Ken Snell
<MS ACCESS MVP>

It worked!! Thank you so much for all your help.

OK - try this:

DoCmd.OpenReport "Agent Production Report", , , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

 

[Melissa]

I hope you don't mind but, I have just two more questions.

My first question is on the Print Report button we were just working on. Is
there a way I can place within the code to open the report in Preview instead
of sending the report straight to the printer?

2nd Question:

On my form that the agents will enter their production in, it is set up with
a Mainfrm and they enter their production within the sub form.
Since my composite keys are:
EmpID, DateCompleted, the same agent cannot enter two records into the main
form on the same Date. This is the way I want it, but I forsee them entering
information within the Main form and closing the form without entering their
production within the Sub form. This will cause a problem because, the form
is set up as Data Entry, so the cannot do a lookup to bring up their record
and they cannot enter directy into the Subform because it is linked to the
main. I think a way around it is, how can I set up the form to display the
record within the form if, this rule is violated.

EXample: Message box displays saying" The changes you requested will cause
duplicate vaules within the index, primary key or releationship" After they
click OK, I would like the record that is already in there to display. Can
this be done?

Glad to hear it... good luck, and you're welcome.
--

Ken Snell
<MS ACCESS MVP>

It worked!! Thank you so much for all your help.

OK - try this:

DoCmd.OpenReport "Agent Production Report", , , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

 

[Ken Snell [MVP]]

Answers inline...

--

Ken Snell
<MS ACCESS MVP>

I hope you don't mind but, I have just two more questions.

My first question is on the Print Report button we were just working on.
Is
there a way I can place within the code to open the report in Preview
instead
of sending the report straight to the printer?

Use acViewPreview as the second argument:

DoCmd.OpenReport "Agent Production Report", acViewPreview, , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

2nd Question:

On my form that the agents will enter their production in, it is set up
with
a Mainfrm and they enter their production within the sub form.
Since my composite keys are:
EmpID, DateCompleted, the same agent cannot enter two records into the
main
form on the same Date. This is the way I want it, but I forsee them
entering
information within the Main form and closing the form without entering
their
production within the Sub form. This will cause a problem because, the
form
is set up as Data Entry, so the cannot do a lookup to bring up their
record
and they cannot enter directy into the Subform because it is linked to the
main. I think a way around it is, how can I set up the form to display the
record within the form if, this rule is violated.

EXample: Message box displays saying" The changes you requested will cause
duplicate vaules within the index, primary key or releationship" After
they
click OK, I would like the record that is already in there to display. Can
this be done?

Well, yes, it could be done, but it'll become a bit complicated with respect
to keeping people from changing data that are already in the database. You
could tell the form to go get the previous record, change the form from a
data entry mode, and so on; but then you'll need to protect the form from
being used in that mode to go back to other records. And then the form will
need to change back to "data entry" when that record is done.

It probably would be better if you just pop up a form at that point and let
the user enter the missing data. That will give you the ability to protect
other data and not have to mess around with the form's properties.

Or you might prevent people from closing the form unless the user has
entered data in the subform. This could be done on the form's Close event.
However, note that such a setup may cause people to get frustrated when
he/she cannot leave the form without entering data, and cannot cancel the
form either. That's when the "Close ACCESS button" in the upper right corner
gets clicked -- sometimes with disastrous results!

Glad to hear it... good luck, and you're welcome.
--

Ken Snell
<MS ACCESS MVP>

It worked!! Thank you so much for all your help.

:

OK - try this:

DoCmd.OpenReport "Agent Production Report", , , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

 

[Melissa]

How would I go about doing your second suggestion without having duplicate
records within the main form?

It probably would be better if you just pop up a form at that point and let

the user enter the missing data. That will give you the ability to protect
other data and not have to mess around with the form's properties.

Answers inline...

--

Ken Snell
<MS ACCESS MVP>

I hope you don't mind but, I have just two more questions.

My first question is on the Print Report button we were just working on.
Is
there a way I can place within the code to open the report in Preview
instead
of sending the report straight to the printer?

Use acViewPreview as the second argument:

DoCmd.OpenReport "Agent Production Report", acViewPreview, , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

2nd Question:

On my form that the agents will enter their production in, it is set up
with
a Mainfrm and they enter their production within the sub form.
Since my composite keys are:
EmpID, DateCompleted, the same agent cannot enter two records into the
main
form on the same Date. This is the way I want it, but I forsee them
entering
information within the Main form and closing the form without entering
their
production within the Sub form. This will cause a problem because, the
form
is set up as Data Entry, so the cannot do a lookup to bring up their
record
and they cannot enter directy into the Subform because it is linked to the
main. I think a way around it is, how can I set up the form to display the
record within the form if, this rule is violated.

EXample: Message box displays saying" The changes you requested will cause
duplicate vaules within the index, primary key or releationship" After
they
click OK, I would like the record that is already in there to display. Can
this be done?

Well, yes, it could be done, but it'll become a bit complicated with respect
to keeping people from changing data that are already in the database. You
could tell the form to go get the previous record, change the form from a
data entry mode, and so on; but then you'll need to protect the form from
being used in that mode to go back to other records. And then the form will
need to change back to "data entry" when that record is done.

It probably would be better if you just pop up a form at that point and let
the user enter the missing data. That will give you the ability to protect
other data and not have to mess around with the form's properties.

Or you might prevent people from closing the form unless the user has
entered data in the subform. This could be done on the form's Close event.
However, note that such a setup may cause people to get frustrated when
he/she cannot leave the form without entering data, and cannot cancel the
form either. That's when the "Close ACCESS button" in the upper right corner
gets clicked -- sometimes with disastrous results!

Glad to hear it... good luck, and you're welcome.
--

Ken Snell
<MS ACCESS MVP>


It worked!! Thank you so much for all your help.

:

OK - try this:

DoCmd.OpenReport "Agent Production Report", , , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

 

[Ken Snell [MVP]]

The popup form would be essentially the same as the current form (would have
same layout and same recordsource for main form and for its subform), with
two exceptions:

1) set the main form's Data Entry property to No and AllowAdditions
property to No.
2) filter the form (using the WhereCondition argument of the
DoCmd.OpenForm step, which also would open the popup form in Dialog mode) to
just the main form record that already exists (I don't recall the specifics
off the top of my head, but I recall that the "primary key" for the main
form is the EmpID, CorrespondID (?), and WorkDate(?).

This will allow use of the existing record, will prevent the creation of new
main form record and avoids having to change the properties back and forth
of the current form.

--

Ken Snell
<MS ACCESS MVP>

How would I go about doing your second suggestion without having duplicate
records within the main form?

It probably would be better if you just pop up a form at that point and
let

the user enter the missing data. That will give you the ability to
protect
other data and not have to mess around with the form's properties.

Answers inline...

--

Ken Snell
<MS ACCESS MVP>

I hope you don't mind but, I have just two more questions.

My first question is on the Print Report button we were just working
on.
Is
there a way I can place within the code to open the report in Preview
instead
of sending the report straight to the printer?

Use acViewPreview as the second argument:

DoCmd.OpenReport "Agent Production Report", acViewPreview, , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"

2nd Question:

On my form that the agents will enter their production in, it is set up
with
a Mainfrm and they enter their production within the sub form.
Since my composite keys are:
EmpID, DateCompleted, the same agent cannot enter two records into the
main
form on the same Date. This is the way I want it, but I forsee them
entering
information within the Main form and closing the form without entering
their
production within the Sub form. This will cause a problem because, the
form
is set up as Data Entry, so the cannot do a lookup to bring up their
record
and they cannot enter directy into the Subform because it is linked to
the
main. I think a way around it is, how can I set up the form to display
the
record within the form if, this rule is violated.

EXample: Message box displays saying" The changes you requested will
cause
duplicate vaules within the index, primary key or releationship" After
they
click OK, I would like the record that is already in there to display.
Can
this be done?

Well, yes, it could be done, but it'll become a bit complicated with
respect
to keeping people from changing data that are already in the database.
You
could tell the form to go get the previous record, change the form from a
data entry mode, and so on; but then you'll need to protect the form from
being used in that mode to go back to other records. And then the form
will
need to change back to "data entry" when that record is done.

It probably would be better if you just pop up a form at that point and
let
the user enter the missing data. That will give you the ability to
protect
other data and not have to mess around with the form's properties.

Or you might prevent people from closing the form unless the user has
entered data in the subform. This could be done on the form's Close
event.
However, note that such a setup may cause people to get frustrated when
he/she cannot leave the form without entering data, and cannot cancel the
form either. That's when the "Close ACCESS button" in the upper right
corner
gets clicked -- sometimes with disastrous results!

:

Glad to hear it... good luck, and you're welcome.
--

Ken Snell
<MS ACCESS MVP>


It worked!! Thank you so much for all your help.

:

OK - try this:

DoCmd.OpenReport "Agent Production Report", , , _
"[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date, "mm\/dd\/yyyy") & "#"