If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Table design problem?
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.. "Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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 . "Ken Snell [MVP]" wrote: Comments inline... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... 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 |
#22
|
|||
|
|||
Table design problem?
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 "Melissa" wrote in message ... 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.. "Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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 . "Ken Snell [MVP]" wrote: Comments inline... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... 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 |
#23
|
|||
|
|||
Table design problem?
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; "Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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.. "Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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 . "Ken Snell [MVP]" wrote: Comments inline... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... 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 |
#24
|
|||
|
|||
Table design problem?
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 "Melissa" wrote in message ... 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; "Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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.. "Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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 . "Ken Snell [MVP]" wrote: Comments inline... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... 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 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 |
#25
|
|||
|
|||
Table design problem?
It worked!! Thank you so much for all your help.
"Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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; "Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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.. "Ken Snell [MVP]" wrote: 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 "Melissa" wrote in message ... 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 . "Ken Snell [MVP]" wrote: Comments inline... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... 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 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 |
#26
|
|||
|
|||
Table design problem?
Glad to hear it... good luck, and you're welcome.
-- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... It worked!! Thank you so much for all your help. "Ken Snell [MVP]" wrote: OK - try this: DoCmd.OpenReport "Agent Production Report", , , _ "[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _ " And [DateCompleted] = #" & _ Format(Date, "mm\/dd\/yyyy") & "#" -- |
#27
|
|||
|
|||
Table design problem?
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 a 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? "Ken Snell [MVP]" wrote: Glad to hear it... good luck, and you're welcome. -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... It worked!! Thank you so much for all your help. "Ken Snell [MVP]" wrote: OK - try this: DoCmd.OpenReport "Agent Production Report", , , _ "[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _ " And [DateCompleted] = #" & _ Format(Date, "mm\/dd\/yyyy") & "#" -- |
#28
|
|||
|
|||
Table design problem?
Answers inline...
-- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... 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 a 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! "Ken Snell [MVP]" wrote: Glad to hear it... good luck, and you're welcome. -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... It worked!! Thank you so much for all your help. "Ken Snell [MVP]" wrote: OK - try this: DoCmd.OpenReport "Agent Production Report", , , _ "[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _ " And [DateCompleted] = #" & _ Format(Date, "mm\/dd\/yyyy") & "#" -- |
#29
|
|||
|
|||
Table design problem?
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. "Ken Snell [MVP]" wrote: Answers inline... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... 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 a 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! "Ken Snell [MVP]" wrote: Glad to hear it... good luck, and you're welcome. -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... It worked!! Thank you so much for all your help. "Ken Snell [MVP]" wrote: OK - try this: DoCmd.OpenReport "Agent Production Report", , , _ "[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _ " And [DateCompleted] = #" & _ Format(Date, "mm\/dd\/yyyy") & "#" -- |
#30
|
|||
|
|||
Table design problem?
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 "Melissa" wrote in message ... 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. "Ken Snell [MVP]" wrote: Answers inline... -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... 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 a 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! "Ken Snell [MVP]" wrote: Glad to hear it... good luck, and you're welcome. -- Ken Snell MS ACCESS MVP "Melissa" wrote in message ... It worked!! Thank you so much for all your help. "Ken Snell [MVP]" wrote: OK - try this: DoCmd.OpenReport "Agent Production Report", , , _ "[EmployeeHrs].[EmpID] = " & Me.EmpID.Value & _ " And [DateCompleted] = #" & _ Format(Date, "mm\/dd\/yyyy") & "#" -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |