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-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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?
I am thinking that I need perhaps two additional tables; one that is the Templates table and one that is the Template details table. The template table would contain the sample identification and the details table would contain the analyses associated with that sample Template?
I will create those tables and then come back with hopefully a more focused question.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David=
————————————————
You appear to be replying on the web. When you do that, you need to be sure to click Show Message History so that those of us on email see the original thread.
Because you have one row per test in table 5 in your original post, you should have one row per test for each analysis for the "standard" tests. When the user picks an analysis type, you need code in After Update of the combo box that picks the analysis to populate table 5. Come to think of it, you need an extra table between 4 and 5 that lists for each sample the type of analysis required.
Fix that and come back with a complete list of the fields in each of your tables, and I'll try to help you with the code.
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 6, 2016, at 9:21 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
My question may not be too vague, but apparently my skill set is too low to understand the advice.
I think I want the user to pick a sample template, not an analysis; e.g. "weekly raw water testing", which would be the description of a template record. But I don't know what fields to include in each template record. Every template will not have the same number of analyses. Should I just put in more fields for analyses than would ever be required? And thus some fields would remain null and not get inserted? Or is this leading me to another join table of some design.
And I am not experienced in this at all. Could you give me some example coding of the insert query?
I apologize for being "challenged" and I promise I will try to do as much work on my own as I can and not just ask this group for everything.
————————————————
David-
The template table sounds like the way to go. When the user selects an analysis, pre-fill the lab reports table with the template rows. You would need a bit of code to do that - run an INSERT query to add the appropriate rows from the template. The user is then free to delete or add rows and record the results.
No, your question is not to vague or complex!
__._,_.___
Posted by: david.pratt@outlook.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (14) |
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