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 |
#11
|
|||
|
|||
Inspections and Treatments
i agree - since treatments may exist independent of inspections, then the
tables design laid out earlier sounds correct. and i agree, too, that process analysis, both from the broader view of business needs and the narrower view of user work flow, skill level, etc, is quite an eye-opener. what i've often found is that even the people who perform the work don't realize *everything* that goes into it (what i call "where did it come from and where is it going") - and management almost never does! g "jamesfreddyc" wrote in message ... "does your company sometimes do treatments after an inspection, but also sometimes do treatments without a preceding inspection? if yes, then the current setup probably makes sense. but if a treatment is *always* preceded by an inspection, i wonder if tblTreatments should be a child table as the "many" side of a one-to-many relationship with tblInspections. if the latter is the case, then the forms design above would have to be revamped to handle that tables relationship." Tina, Thanks for the tip. While I fully understand the options here, I believe the existing workflow will work just fine. "Work" *could be* either Inspections or Treatments, but a treatment record is not always preceded by an inspection --- there may be a moment where a treatment is simply done based upon an existing location and not dependent upon inspecting the location first. I certainly can appreciate the thought. I'm acting as developer and dba here, so trying to consider all of the what-if's to get this setup correctly has been quite interesting. What I am finding out is that doing both app-dev and db-dev is actually making me a better developer overall. Thanks again, j "tina" wrote: i basically agree with Fred's tables/relationships analysis, and have comments on form design. see below. "Fred" wrote in message ... Your main foundation is understanding your data items and the relationship between them and then your tables and the relationships between them / their record. . Your only partially describing these makes me guess that you are not giving that part of the job sufficient importance and work. Here's a mixture of confiriming my guesses, things that your structure implies / hints at, plus making suggestions. -A "Work" item is for one and only one customer -A "Work" item is done by one and only one employee -Your intent is to record occurences of inspections and occurences of treatments. -You are not trying to create any standardized "Picklist"of inspections or treatments. You intend to just free-form describe the treatment (If this is wrong, and youARE trying to do this, then you would, for example, need 2 "inspection" tables, one is occurences of inspecitons, the other the Picklist" of standardized inspections.) A "Work" item may include many instances of inspections. But that occurence of an inspection relates only to that work item A "Work" item may include many occurences of treatments. But that occurence of a treatrment relates only to that "Work" item. IF all of the above is true, then, to the extent that you described it, what you listed is OK. But here's what you forgot to do or say: IMPORTANT!: The namesake ID of each table is set to be it's Primary Key. E.G. "WorkID" is set as the PK of the "Work" table etc. Do "Show only when they are equal" joins from Work table to Customers table and Work table to Employee Table. if Fred is referring here to the Join Type in the Edit Relationships dialog of the Relationships window....well, frankly, i never bother with that. it isn't necessary at the table level, and when i write queries i set the joins as needed at the time. Do a "Show all Work items......" join from work table to inspections table. Do a "Show all Work items......" join from work table to treatments table. ditto above. Create datasheet style forms (which will be used as subforms) for inspections and treatments. datasheet style not a problem here, but not a requirement, either. I'm good at organizing these things and data structures, but other people who read these know Access 10 times better than me and I welcome them to comment on or add to my Access stuff. Create a query which shows all , customer and employee fields. Use it as a record source for a "Work" form which show all desired fields from those 3 tables. this isn't necessary, and is often counterproductive, but it's a common mistake made by inexperienced Access developers. for a *data entry* form, it's rarely necessary to use more than one table in the RecordSource, and on those occasions it's usually two tables with a one-to-one relationship. first, before forms, make sure the table relationships are set up in the Relationships window, with the links going *from* the parent table *to* the child table. for example: from tblCustomers to tblWork. from tblWork to tblInspections. and be sure to checkmark the Enforce Referential Integrity option in the Edit Relationships dialog of each link. now you're ready to move on to forms. from your post, your process flow seems to be to start with a new customer, or with an existing customer record, and then add a work record and its' related inspections and treatments. suggest the following setup - and, btw, don't bother trying to do this using a form wizard. just set it up yourself. create a form bound to tblCustomers, and call it frmCustomers. in Design view, open the Properties box (click View | Properties on the menu bar), and set the FormView property to SingleForm. create a form bound to tblWork, and call it frmWork. in Design view, include a combobox control bound to the EmpID foreign key field, with its' RowSource set to tblEmployees. set the FormView property to SingleForm. create a form bound to tblInspections. you can set the FormView to SingleForm, ContinuousForms, or Datasheet, depending on what will best suit your data entry needs. create a form bound to tblTreatments. ditto above re the FormView setting. in Design view of frmWork, add a subform control (you can add it from the Toolbox toolbar, which in turn you'll find by clicking View on the menu bar). open the Properties box (View menu again) and set the subform control's Name property as ChildInspections. set the SourceObject property as frmInspections. set the LinkChildFields property as WorkID (this refers to the foreign key field in tblInspections, which is the table used as the RecordSource of frmInspections). set the LinkMasterFields as WorkID (this refers to the primary key field in tblWork, which is the table used as the RecordSource in frmWork). now you have an Inspections subform in frmWork, and linked to frmWork. easy as pie! Still in Design view of frmWork, add another subform control and name it ChildTreatments. set the SourceObject as frmTreatments, and set the LinkChildFields and LinkMasterFields to the corresponding foreign key and primary key fieldnames, as described above. then save and close frmWork. open frmCustomers in Design view and add a subform control, naming it ChildWork. set its' SourceObject to frmWork, its' LinkChildFields to CustomerID (the foreign key field in tblWork), and its' LinkMasterFields to CustomerID (the primary key field in tblCustomers). save and close the form. now when you open frmCustomers, you can add a new customer record or go to an existing customer record, then add a new work record or go to an existing one, then add or edit inspection records and/or treatment records for that work record for that customer. if you find that using nested subforms results in a large and busy, crowded form for data entry, then you might consider opening a form to add/edit customer records only. then open a separate form (not a subform) to add/edit work records and their related inspection and treatment records; in the work form, you can use a combobox control bound to foreign key field CustomerID, with RowSource as tblCustomers, to choose the customer for each new work record. and if you really want to get creative, you can add a tab control to the work form, and put the inspection subform on one tab and the treatment subform on another tab. that saves space and makes the form look simpler and cleaner. you can do this whether the work form is opened separately, or is a subform on the customer form. the only thing that raises a question for me here, is a possible relationship between inspections and treatments. does your company sometimes do treatments after an inspection, but also sometimes do treatments without a preceding inspection? if yes, then the current setup probably makes sense. but if a treatment is *always* preceded by an inspection, i wonder if tblTreatments should be a child table as the "many" side of a one-to-many relationship with tblInspections. if the latter is the case, then the forms design above would have to be revamped to handle that tables relationship. hth Add the already made inspections and treatments forms to it as subforms using subform wizard. I think that that should work. Sincerely, Fred |
#12
|
|||
|
|||
Inspections and Treatments
first key step: forget the wizard. you need to learn how comboboxes work,
and when you do, they'll be easy to work with - and they're a great tool. second key step: open a new form in Design view. add a combo box control - no wizard! - to the form. open the Properties box. combobox controls have many properties that are the same as textbox control properties. they also have properties that other controls don't have (the exception being listbox controls, which share many of these special properties). read up on the following properties in Access Help: RowSourceType RowSource ColumnCount ColumnHeads ColumnWidths BoundColumn (hint: don't confuse the bound column with the ControlSource property, which binds a control to a field in the form's RecordSource.) ListRows ListWidth LimitToList AutoExpand OnNotInList event another hint: an easy way to go to the Help topic on a property in a form (or report) is to click on the property "line" in the Properties box, and press F1. start there, Fred, experiment some more, and then come back with specific questions re anything you need help figuring out. hth "Fred" wrote in message ... Hello Tina, Thanks again. But after numerous futile attempts (with and without the wizard turned on) I could not get a combo box to show the other fields in the ancillary table. For example, to have it continue to show the employee name, not just the employee ID #. It shows the other fields only when actively making the selection; after that it only shows the stored / linked field. Could I trouble you to tell me the key steps? Sincerely, Fred |
#13
|
|||
|
|||
Inspections and Treatments
you're very welcome
"jamesfreddyc" wrote in message ... Fred and Tina, Your commentaries were incredibly insightful and have fully helped me to develop exactly what I need. It's all very much appreciated. Thank You, j "Fred" wrote: Your main foundation is understanding your data items and the relationship between them and then your tables and the relationships between them / their record. . Your only partially describing these makes me guess that you are not giving that part of the job sufficient importance and work. Here's a mixture of confiriming my guesses, things that your structure implies / hints at, plus making suggestions. -A "Work" item is for one and only one customer -A "Work" item is done by one and only one employee -Your intent is to record occurences of inspections and occurences of treatments. -You are not trying to create any standardized "Picklist"of inspections or treatments. You intend to just free-form describe the treatment (If this is wrong, and youARE trying to do this, then you would, for example, need 2 "inspection" tables, one is occurences of inspecitons, the other the Picklist" of standardized inspections.) A "Work" item may include many instances of inspections. But that occurence of an inspection relates only to that work item A "Work" item may include many occurences of treatments. But that occurence of a treatrment relates only to that "Work" item. IF all of the above is true, then, to the extent that you described it, what you listed is OK. But here's what you forgot to do or say: IMPORTANT!: The namesake ID of each table is set to be it's Primary Key. E.G. "WorkID" is set as the PK of the "Work" table etc. Do "Show only when they are equal" joins from Work table to Customers table and Work table to Employee Table. Do a "Show all Work items......" join from work table to inspections table. Do a "Show all Work items......" join from work table to treatments table. Create datasheet style forms (which will be used as subforms) for inspections and treatments. I'm good at organizing these things and data structures, but other people who read these know Access 10 times better than me and I welcome them to comment on or add to my Access stuff. Create a query which shows all , customer and employee fields. Use it as a record source for a "Work" form which show all desired fields from those 3 tables. Add the already made inspections and treatments forms to it as subforms using subform wizard. I think that that should work. Sincerely, Fred |
#14
|
|||
|
|||
Inspections and Treatments
Tina,
Thank you again. I read all of those that you recommended (plus ControlSource) and experimented with them, and it was very informative. I'm still stuck at the same place, albiet with greater knowledge. Using JamesFreddyC's inquiry as an example, let's say that the record source for a form is tblWork, and we desire to show the address of the customer that the work was for. I think that you said that a combo box is good way to do this. I found the BoundColumn and ControlSource help files confusing, I think it was because I couldn't find the "needle" of what these basically are in the general-case "haystack" of the help text. But all of those properties seem to just set what show when the user is actively using the dropdown arrow....I can get that to work fine. My problem is how to control what shows when nobody is doing anything with the combo box. The ControlSource property seems to determine this. However, it has a dropdown list which is just the fields of the RecordSource of the Form, and I can't get it to take anything related to the related table that is the source of the combo box. E.G. so how would one make the address of the customer show in the combo box when nobody is clicking on the combo box? Thanks Again. Sincerely, Fred |
#15
|
|||
|
|||
Inspections and Treatments
Fred,
My initial thought to your question is... Why would you need to display a Customer's address in a comboBox? Further, a particular "work" related record would logically only occur at ONE location, therefore, there wouldn't be a need to display many addresses (as in "filling" a comboBox with items). Perhaps I am misunderstanding your requirement here. Also: I have since expanded this db model a bit to take into account that a particular customer address could very well be different from the address of a work item. For example, in my particular model, while the customer calling to request service might be the owner of a particular residence (the address for the work to be done), they might not actually live there (perhaps it is a rental property). j "Fred" wrote: Tina, Thank you again. I read all of those that you recommended (plus ControlSource) and experimented with them, and it was very informative. I'm still stuck at the same place, albiet with greater knowledge. Using JamesFreddyC's inquiry as an example, let's say that the record source for a form is tblWork, and we desire to show the address of the customer that the work was for. I think that you said that a combo box is good way to do this. I found the BoundColumn and ControlSource help files confusing, I think it was because I couldn't find the "needle" of what these basically are in the general-case "haystack" of the help text. But all of those properties seem to just set what show when the user is actively using the dropdown arrow....I can get that to work fine. My problem is how to control what shows when nobody is doing anything with the combo box. The ControlSource property seems to determine this. However, it has a dropdown list which is just the fields of the RecordSource of the Form, and I can't get it to take anything related to the related table that is the source of the combo box. E.G. so how would one make the address of the customer show in the combo box when nobody is clicking on the combo box? Thanks Again. Sincerely, Fred |
#16
|
|||
|
|||
Inspections and Treatments
Hello jamesfreddyc
I was just using your applicaiton as an example to try to learn something. With customer being a sort of "lookup" I had recommended showing that via including it (via a query) in the record sources of the form. Tina indicated that a combo box would be a better way of doing that and I was just trying to learn how to do that. Sincerely, Fred |
#17
|
|||
|
|||
Inspections and Treatments
a control's ControlSource property setting determines which field in the
*form's* RecordSource is bound to that particular control. for example, when control Text1's ControlSource property is set to FieldA, then when you open the form, go to any particular record, or to a new record, and enter a value in control Text1, the value will be stored in FieldA in that record, in the form's underlying table. a combo box control's BoundColumn property determines which field in the control's RowSource will be used as the *value* of the control, when a droplist selection is made. in turn, that value will be stored in the field set as the control's ControlSource; or, if the control is unbound (ControlSource is blank), that value will be held by the control until it is changed by the user or the form is closed. when you use a combo box control to choose from a list of employees, for example, the control's RowSource will be fields from tblEmployees, including: 1.the primary key field - which must be the bound column of the control, normally i make it the first column (which is column zero (0), because the combobox column index is zero-based). 2. the employee name fields (usually you'll want to concatenate these into a single name field, such as EmpName: Lname & ", " & Fname 3. *any other fields that contain data you will want to display/use in the form* use the ColumnWidths property to set the width of the first column (column 0) to 0". you don't want the user to see the primary key value of the employee record chosen from the combobox droplist. set the width of the second column (column 1) to, say, 2", wide enough to display most lastname, firstname combinations. so what shows in the droplist, and what will show in the combobox control after a selection is made from the droplist, will be a name. usually, you set the column widths of any additional columns to 0". depends on what information the user needs to see when s/he is making a selection from the droplist. regardless of the column width setting, the data in all columns of the RowSource are available to be utilized in the form. the values in those columns can be selectively displayed in unbound controls in the form, or used in calculations in the form or in VBA - pretty much however you need to use them, while the form's open. so if the RowSource of cboEmployee is set to SELECT EmpID, Lname & ", " & Fname As Emp, DOB FROM tblEmployees ORDER BY Lname, Fname; and the ColumnWidths property is set the 0", 2", 0" then the user sees only the employee name in the droplist and in the control after a selection is made. and you can set the ControlSource of a textbox to = cboEmployee.Column(2) so the textbox displays the DOB value of the employee chosen in cboEmployee. and btw, i have to bow out of this thread now, i won't be back in the newsgroups for a few weeks. for further specific questions about combo box controls, suggest you google the newsgroups and/or start a new thread in microsoft.public.access.forms hth "Fred" wrote in message ... Tina, Thank you again. I read all of those that you recommended (plus ControlSource) and experimented with them, and it was very informative. I'm still stuck at the same place, albiet with greater knowledge. Using JamesFreddyC's inquiry as an example, let's say that the record source for a form is tblWork, and we desire to show the address of the customer that the work was for. I think that you said that a combo box is good way to do this. I found the BoundColumn and ControlSource help files confusing, I think it was because I couldn't find the "needle" of what these basically are in the general-case "haystack" of the help text. But all of those properties seem to just set what show when the user is actively using the dropdown arrow....I can get that to work fine. My problem is how to control what shows when nobody is doing anything with the combo box. The ControlSource property seems to determine this. However, it has a dropdown list which is just the fields of the RecordSource of the Form, and I can't get it to take anything related to the related table that is the source of the combo box. E.G. so how would one make the address of the customer show in the combo box when nobody is clicking on the combo box? Thanks Again. Sincerely, Fred |
#18
|
|||
|
|||
Inspections and Treatments
Hello Tine,
I hope that I got this quick big THANK YOU in before you bowed out of the thread. Sincerely, Fred |
|
Thread Tools | |
Display Modes | |
|
|