David-
Apologies for ignoring this thread, but my wife went in the hospital last week. She's coming home tomorrow, so I may have some time later this week to look at this. Or maybe someone else can pick it up.
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 31, 2016, at 5:12 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
ubc1 is based on the customer table with bound column CustomerID
ubc2 is based on an Area table which defines an Area within the Customer facility; AreaID
ubc3 is based on an Equipment table, filtered by the AreaID; EquipmentID
bcb is based on a qry of the SampleGroup table, filtered by ubc3 EquipmentID, bound to SampleGroupID
sfbcb1 is based on a qry of the SamplePoints table, bound column SamplePointID, filtered by bcb in the main form
sfbcb2 is based on a qry of the Parameters table, bound column ParameterID, filtered on the Analytical yes/no in the Main form.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
OK, now we're getting somewhere.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
What are the Row Sources for ubc1, ubc2, ubc3, and bbc1? Also, what's the Control Source and the Bound Column of bbc1?
What are the Row Sources and the Control Sources for sfbcb1 and sfbcb2?
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 30, 2016, at 10:08 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Templates table
TemplateID
CustomerID
TemplateDate
SampleGroup
TemplateName
TemplateDescription
Analytical (a yes/no field which is used to determine which analytical parameters can be used)
UserID (who created the template and will be the primary user)
TemplateDetails
TEmplateDetailID
TemplateID
SamplePointID
ParameterID
SortOrder (used to determine the sort order that the samplepoint/Parameter pair shows up in)
The form is used to create a template including the header information and the detail information. Later this template is used to create an analytical data collection form into which live results are entered.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
The Template and Template details relationship is just like an Orders/orders details relationship. In the Template record there is basic info like the customer name, the system being monitored, the date, etc. The template details include multiple records of sample points and analysis. No analytical results are in this template detail file; just a list of various sample points and the analyses which are to be performed on each of the samples. Typically 2-4 sample points with 3-8 analyses performed on each sample.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Give me the full layout of the two template tables and explain what you're trying to do with this form.
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 30, 2016, at 9:05 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
The Template and Template details relationship is just like an Orders/orders details relationship. In the Template record there is basic info like the customer name, the system being monitored, the date, etc. The template details include multiple records of sample points and analysis. No analytical results are in this template detail file; just a list of various sample points and the analyses which are to be performed on each of the samples. Typically 2-4 sample points with 3-8 analyses performed on each sample.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
What are you editing in this form? The template tables or live data?
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 30, 2016, at 8:41 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I am pretty certain of my table design and I believe it is fundamentally good and third normal. I have invested a lot of time in understanding basic table relationships. That doesn't mean that I should not have a different set of tables; it just means I am pretty sure that what I have is relationally correct and no repeating data in any tables.
Part of my project includes performing analyses on samples and refers back to my original question when you helped me set up Template files a few weeks ago. There can be literally hundreds of potential sample points and a hundred or more potential analyses that can be performed on any sample. To make a Template I have a Template record and associated multiple TemplateDetails records. The detail records include the SamplePoint and the Analysis to be performed on that sample.
The unbound combos in the header are used to reduce the number of sample choices down to a SampleGroup, which contains maybe 3-10 samples. The sample group is in the Main Template record form. I suppose the SampleGroup does not have to be in the main Template file and I could use an unbound combo for that also. I don't think that makes things any simpler.
The template details are in the subform and include the specific sample point and the analysis.
I am filtering the sample point combo in the details subform based on the Sample Group in the main form. I am filtering the available analyses in the details subform based on a TemplateType field in the Main form. This is just to make it easier for the user to make choices from short lists instead of very long lists.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
Rather than try to figure out a "best" answer to your scenario, I think it'll be a better approach to challenge your original premise. Why do you need so many combo boxes filtered on each other? Why are there combos in the subform that depend on changing values in the outer form? Neither of those are normal or good practices.
Basically, what's the business problem you're trying to solve? I suspect that you have a fundamental table design problem that is making this much more difficult than it needs to be.
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 30, 2016, at 4:17 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I should have asked this question many, many hours ago and saved myself a lot of time. Now that I have a learned a little more via the school of hard knocks, I see that I need a "standard process" to replace my hit and miss practice of Requery form, Requery combo, set to nothing, etc. I have just been guessing at what to do based on the Monkey see, Monkey do practice.
I am not asking for the code at this time. Focusing on the code has added to my confusion to this point. I think I can find that later (but I may have to come back and ask some specific question). For now I am looking for general practice guidelines in plain English so that I can understand what the purpose of the code is.
Here is the general situation I am hoping to establish a standard practice for. I have the following:
Mainform = MF
unbound combo box controls in the MF header, ubc1, ubc2 and ubc3
ubc2 is filtered on the value of ubc1, and ubc3 is filtered on the value in ubc2
Mainform details section contains a bound combo (bcb1) which is filtered on the value in ubc2
Subform = SF with control name the same as the subform name, which is linked to the main form via Link fields, so the SF is requeried each time the main form record pointer is changed. However, the subform contains bound combo boxes sfbcb1 and sfbcb2, which need to be refreshed based on changing values in main form detail fields. sfbcb1 is filtered based on the value in MF bcb1 and sfbcb2 is filtered based on the value of MF txt1.
In this scenario, to improve user experience, when and where should I set the various cbo's to "nothing"?
Which events do I use, and which controls do I use to Requery the forms and combo boxes?
Do I ever need to force a Requery of the subform, or just leave that process to Access and the link fields?
To make take this less time for someone to answer, see the following format (If I could insert a table in this message this would be easier)
Main Form
Event = (OnCurrent, etc)
Action = (set record pointer, Requery something, set cbo's in main form header to nothing, etc)
ubc1
Event =
Action =
ubc2
Event =
Action =
ubc3
Event =
Action =
MF bcb1
Event =
Action =
MF txt1
Event =
Action =
Or answer in any format you choose, I am just trying to make it easy for someone to help me.
Thank you.
David
__._,_.___
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 (11) |
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