David-
Yes, static templates, and your original design without result fields is correct.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 10, 2016, at 4:11 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John, your first statement is exactly what I want to do. We both have a similar idea about the use of the template. However, I THINK you are saying that the template detail records should NOT include the RESULTS field that I have in the template details records? The RESULTS field for each template detail record is the only field that I was saying would be emptied and then modified each time it was used.
I was looking at the Template as kind of a "scratch pad" which comes up with a suggested list of sample points and parameters. I was looking at the user being able to add Parameters, delete Parameters, enter data, evaluate it, change it where it looks questionable, and then when satisfied, save it into the Service Report records. However, I suppose that the user can do those same things in the actual Service Report records just as easily.
If after understanding the above, you still feel that having "static templates" is the better choice I will follow your advice.
Thank you again for your patience.
David
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
No, that wasn't a typo. You need to fill in the template tables, then use them to assist the user in entering the Service Report data. Why do you want to try to edit the templates and then have code fill in a service record? Templates should be relatively static for a given customer and equipment. The templates merely exist to aid the user in filling out a new service record.
I suppose you should have a form / subform to make it easy to fill in the template data. If you link the form / subform correctly (via the Link Master Fields and Link Child Fields properties of the subform control), choosing a record in tblServiceTemplates should automatically populate the subform with the related detail records.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 9, 2016, at 11:11 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I want to make sure I am understanding and hoping you made a typo. You said:
"Then that code should work. In the outer form, the user starts a new record in tblServiceReports,.."
I am hoping you meant to say tblServiceTemplates, except that I don't want to start a new record in tblServiceTemplates, I just want to select and existing template record.
Here is what I am hoping to happen:
- In the main form for the templates file, I am wanting to select an existing record in tblServiceTemplates using a combo box (this is a parent record to the template details file)
- Change the date field in the selected tblServiceTemplates parent record (the AfterUpdate Event from the above combo box should clear the date field in the chosen template-parent record)
- modify the Account Manager field in the parent record if necessary (combo box)
- Selecting the parent template record also brings up the related tblServiceTemplateDetails records into my subform, and the AfterUpdate event for the first combo should change all the detail records result fields to null
- Enter new result data into the Result fields of tblServiceDetails using the subform based on tblServiceDetails,
- press a button and have the data from the main form tblServiceTemplates, saved into tblServiceReports, creating a new record in tblServiceReports
- Have the detail data from the tblServiceTemplatesDetails subform saved into the tblServiceDetails, creating new detail records, all associated with the newly created parent record in tblServiceReports.
So, are you saying that the code will work for this?
Again, I appreciate greatly your help.
David
BTW, could I have used some better name choices for these tables that would have made them easier for others to understand how they relate to each other?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Then that code should work. In the outer form, the user starts a new record in tblServiceReports, enters the CustomerID and the EquipmentID (perhaps from a combo box), enters a SampleDate and a ReportDate and an AccountManagerID (perhaps from another combo box), then presses Enter or clicks a Save button. The code then populates the related table and the subform.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 9, 2016, at 5:50 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
The table of service reports Master records is the table I described earlier (perhaps I am confusing you with my choice of terms)
tblServiceReports = master records file
and the details table is the
tblServiceReportDetails
I have the same Master-Detail relationship with my Template files
tblServiceTemplates = master records for the templates
and the details table for the templates are in
tblServiceTemplateDetails
The template form has tblServiceTemplates as the record source, and the subform has the tblServiceTemplateDetails as the record source. I wish to place a button on this form that will be used to create a new record in the tblServiceReports file from the data in the tblServiceTemplate fields, and at the same time create several new detail records in the tblServiceDetails, all of which should be associated with the newly created record in the tblServiceReports (what I am referring to as a master record).
You must be a man of great patience to put up this kind of poor communication on my part!
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Yes, the idea is the code auto-fills the detail record from the template after the user selects a customer and equipment on a new record.
Yes, just about anything is possible. Please describe the Service Report master record and what it contains. How will the user create that?
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 9, 2016, at 4:42 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
OK, I will need to "digest" the code for a while.
But regarding where this coding takes place, I have a question. If I am understanding you correctly, the user will have to create a new ServiceReport master record, and then the AfterUpdate event will create all of the ServiceDetail records based on what is in the Template detail records?
I was hoping that I could create both the ServiceReport master record and all of the details records by executing some code via a command button on the MasterTemplate main form. That seems to me to be a more "user friendly" approach. Can that be done?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
That looks pretty good. It looks like your service templates are keyed on CustomerID and EquipmentID. I think the best place to do this is in AfterUpdate of the outer form that edits tblServiceReports. (You should have a linked subform for the tblServiceReportDetails table.) It will look something like this:
Private Sub Form_AfterUpdate()
Dim db As DAO.Database, strSQL As String
' Set an error trap
On Error GoGo Update_Error
' Copy the template only if we've just saved a new record
If Not Me.NewRecord Then Exit Sub
' Get a pointer to the current database
Set db = CurrentDB()
' Set up the INSERT command
strSQL = "INSERT INTO tblServiceReportDetails (ServiceReportID, " & _
" SamplePointerID, ParameterID) " & _
"SELECT " & Me.ServiceReportID & ", SamplePointID, ParameterID " & _
"FROM tblServiceTemplates INNER JOIN telServiceTemplateDetails " & _
"ON tblServiceTemplates.ServiceTemplateID = tblServiceTemplateDetails.ServiceTemplateID " & _
"WHERE tblServiceTemplates.CustomerID = " & Me.CustomerID & _
"AND tblServiceTemplates.EquipmentID = " & Me.EquipmentID"
' Execute the command to copy the template rows
db.Execute strSQL, dbFailOnError
' Rows copied - now requery the subform
Me.fsubServiceDetailReports.Requery
Done:
Set db = Nothing
Exit Sub
Update_Error:
MsgBox "Unexpected error: " & Err & ", " & Error
Resume Done
End Sub
The code assumes that the name of your subform control will be "fsubServiceDetailReports".
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 9, 2016, at 4:07 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Not sure what I am doing wrong but I tried to reply via email and apparently it did not go through. It appears that when I hit reply in my email program the recipient is NR (no reply). But I know earlier in the thread I did reply via email. Maybe I messed the thread up by replying first via email and then on the web? And now I can only reply via the Web?
Anyway, let me first apologize for giving initial misinformation. Crystal, thank you for the reply. Those were not actually my field names, rather descriptive terms. I will use actual field names in the future. John, I apologize profusely to you. I do not have a join table. I have master and detail tables. I confused myself because in the details table there are many sample points and many parameters, and any parameter may be associated with any sample point and vice versa.
I have redesigned the tables and fields, and here is what I have now:
tblServiceReports:
ServiceReportID
CustomerID
EquipmentID (equipment which will have one or more water sampling points)
SampleDate
ReportDate
AccountManagerID
tblServiceReportDetails
ServiceReportDetailID
ServiceReportID
SamplePointID (will be one or more sample points)
ParameterID (will be several parameters for each of the sample points)
Result
Thinking that a Template table is the way to go, I created template master and template detail files:
tblServiceTemplates
ServiceTemplateID
CustomerID
EquipmentID
TemplateDescription (e.g. "template for weekly servicing of ABC's raw water clarifier)
AccountManagerID
tblServiceTemplateDetails
ServiceTemplateDetailID
ServiceTemplateID
SamplePointID
ParameterID
Result
So instead of having to create each individual ServiceReportDetail record by selecting a SamplePoint, then selecting a Parameter, and then entering a result, I want to have all of the SamplePoints and Parameters already available so that I only have to enter a Result.
I think I need Templates to do this, but I do not know how to get the data from the template into the related ServiceReport master and ServiceReport details files. I envision the Result field in the ServiceTemplateDetails to be a temporary place holder for the result. That is, after each time the template is used to collect the results and the results are moved to the tblServiceReportDetails, the Results field will be cleared from all of the Template details records. That is to say the TemplateDetail records will be used over and over.
I have a Templates form that contains the Template master fields and with a subform that contains all the Template detail fields. I can choose the Template master record and then enter the results in the Results field. I put a couple of Command buttons on the form and assume I will need to code some actions to those Command buttons.
Can you help me with that or am I going the wrong direction?
Thanks, David
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
Now that I think more about it in the cold light of day, you don't need a template table at all. In your linking table between Analyses and Sample Points, add a column (can be a simple Yes/No) that you use to mark (with Yes) which samples are the "standard" or "default" for a given analysis. When the user picks an analysis, populate the lab report details with all those rows in the linking table that are marked yes. If you give me a layout of all your tables, I can help you write the code to do that.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 7, 2016, at 4:34 AM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
OK. Thanks for telling me about the message history. this is my first time and I did not know the difference.
I apologize but I am not following about the table design. Isn't the "extra table" between 4 and 5 the Templates table that I originally asked about?
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (20) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar