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
|
|||
|
|||
Hi Tina...Thanks again.
I believe my tables are structured ok, i.e. as per the following: Contacts - PK=ContactID, Field=Name | |__Estimate - PK=EstimateID, FK=ContactID, Field=EstimateValue | |__Materials - PK=MaterialsID, FK=EstimateID, Field=Materials I understand that there's a natural flow, but in my case anyway, there are times when the EstimateValue field would not be entered, in which case, the EstimateID would not be created, and then the syncronization of the materials table is lost. I guess there's no way around this problem, other than two things I can think of, 1) is force the user to enter "something" in the EstimateValue field, regardless if they have one or not, and 2) have a date field in the Estimate table that is filled automatically when the Estimate tab is selected. Am I incorrect in thinking this would work? Thanks, Darren -- Message posted via http://www.accessmonster.com |
#22
|
|||
|
|||
well, the solutions you suggested should work, but you might prefer to
*programmatically* force the creation of the parent record, rather than making the user do it. you can do this without adding an extra field in the parent table that you wouldn't otherwise need. in the *subform's* module, try adding Private Sub Form_BeforeInsert(Cancel As Integer) If Me.Parent.NewRecord Then Me.Parent!EstimateValue = 0 Me.Parent.Dirty = False End If End Sub as soon as the user begins entering data in the first subform record, the subform event fires. if the parent record is a New Record, which it will be *only if* the user did not already enter an EstimateValue manually, then the code sets an EstimateValue of zero, just to dirty the record. then setting the Dirty property to False saves the record, creating an EstimateID primary key (if the key field is not Autonumber, you can create the primary key value before setting Dirty to False - rather than setting the EstimateValue field's value). the primary key value then automatically populates the foreign key field in the subform, so it's already there when the subform record saves to the table, so there should be no error on the save. if you really want the EstimateValue to be Null until the user enters a "real" value, just reset the value, as Private Sub Form_BeforeInsert(Cancel As Integer) If Me.Parent.NewRecord Then Me.Parent!EstimateValue = 0 Me.Parent.Dirty = False Me.Parent!FieldB = Null Me.Parent.Dirty = False End If End Sub hth "Darren Kozey via AccessMonster.com" wrote in message news:f32a58f165b844ea8e3ffc124607047a@AccessMonste r.com... Hi Tina...Thanks again. I believe my tables are structured ok, i.e. as per the following: Contacts - PK=ContactID, Field=Name | |__Estimate - PK=EstimateID, FK=ContactID, Field=EstimateValue | |__Materials - PK=MaterialsID, FK=EstimateID, Field=Materials I understand that there's a natural flow, but in my case anyway, there are times when the EstimateValue field would not be entered, in which case, the EstimateID would not be created, and then the syncronization of the materials table is lost. I guess there's no way around this problem, other than two things I can think of, 1) is force the user to enter "something" in the EstimateValue field, regardless if they have one or not, and 2) have a date field in the Estimate table that is filled automatically when the Estimate tab is selected. Am I incorrect in thinking this would work? Thanks, Darren -- Message posted via http://www.accessmonster.com |
#23
|
|||
|
|||
Wow, Tina
That's great! I'm not at the level of Access db design that I would have come up with that myself. I really appreciate your input. Thanks! Darren -- Message posted via http://www.accessmonster.com |
#24
|
|||
|
|||
you're very welcome, glad to help! the code itself is minor; the trick is in
understanding how subforms relate to main forms and the sequence of events firing in both. subforms are an odd duck, hard to handle at first; but the more you use them, the easier it becomes to manipulate them. btw, i noticed i made an error in the "alternate" code i posted, used the wrong field name in one line. hope i didn't confuse you (i confused myself when i first re-read it g)! here it is again, fixed, just in case you need it: Private Sub Form_BeforeInsert(Cancel As Integer) If Me.Parent.NewRecord Then Me.Parent!EstimateValue = 0 Me.Parent.Dirty = False Me.Parent!EstimateValue = Null Me.Parent.Dirty = False End If End Sub hth "Darren Kozey via AccessMonster.com" wrote in message news:175c52ba40204abf9ee82176d1bca720@AccessMonste r.com... Wow, Tina That's great! I'm not at the level of Access db design that I would have come up with that myself. I really appreciate your input. Thanks! Darren -- Message posted via http://www.accessmonster.com |
#25
|
|||
|
|||
Hi Tina.
I feel a little silly asking this question, but I now have two working versions of my test database, one with only 1:n relationships and one with a n:n relationship as you noted above where I have an EstimateDetails table acting as the junction table between Estimates and Materials. My question is how do I test to see that my n:n version is working...that is how can I test that one estimate can have many materials, and each material can be on many estimates? Thanks, Darren -- Message posted via http://www.accessmonster.com |
#26
|
|||
|
|||
no question is silly, Darren - if you don't know something, the silly thing
is to *not* ask. sometimes the person you ask is silly g and doesn't get the point right away, though. the obvious answer to your question is to enter some dummy data into your tables to see if the linking table accepts them. i would throw together a form (Estimates)/subform (EstimateDetails)/combo box (*Row* Source based on Materials) setup to test this. the only other thing is to double check your table relationships in the Relationships window - make sure each parent-to-child link is identified by the system as one-to-many, not one-to-one or indeterminate. if i'm not getting the point of what you're asking, give it to me in words of one syllable, and i'll try again. hth "Darren Kozey via AccessMonster.com" wrote in message news:c3956162a9334e49ad0b0b217158a261@AccessMonste r.com... Hi Tina. I feel a little silly asking this question, but I now have two working versions of my test database, one with only 1:n relationships and one with a n:n relationship as you noted above where I have an EstimateDetails table acting as the junction table between Estimates and Materials. My question is how do I test to see that my n:n version is working...that is how can I test that one estimate can have many materials, and each material can be on many estimates? Thanks, Darren -- Message posted via http://www.accessmonster.com |
#27
|
|||
|
|||
Hi Tina.
I think I already have essentially what you described, as far as forms. I have a main form with 2 tabs, ContactInfo, and Estimate. The contactInfo tab only has the Name control, and the Estimate tab contains the sfrmEstimate, which is also a tab form with 2 tabs, Value which has the EstimateValue control, and Materials which has the ssfrmMaterials subform in datasheet view. I have navigation buttons on both the main form, and the sfrmEstimate, so that I can add more than one estimate for each contact. The only thing is my Materials ssfrm uses a textbox, and is in datasheet view. Is this going to make a difference? If so, why? My thinking of how to test that the n:n relationship was working, was to enter "screws" for example in the materials ssfrm for the first estimate, then enter "screws" again for the second estimate (both under the same contactID). Is this the right logic? It must be flawed, because both the simpler 3-table 1:n-only database, and the 4-table n:n version will allow me to do this. Where have I gone wrong? Thanks again for your help. (I think I'll be off and running once I understand what's going on behind the scenes in regards to the n:n relationship). Darren -- Message posted via http://www.accessmonster.com |
#28
|
|||
|
|||
I have an EstimateDetails table acting as the junction
table between Estimates and Materials if EstimateDetails is the junction or linking table between the other two, that means that Estimates is a parent table to the EstimateDetails child table, *and* Materials is also a parent table to the EstimateDetails child table. a linked subform's underlying table is always the child table to the parent table bound to the main form. you can't link a subform based on Materials to a main form bound to EstimateDetails - that's putting the parent/child relationship backward. as i said several posts earlier, the standard way to express a parent-linking-parent relationship between three tables is with one parent bound to the main form, the linking or child table bound to the subform, and the other parent table used as the RowSource in a combo box in the subform, with the combo box's ControlSource set to the matching foreign key field in the linking table. if you're still trying to use your Materials table to hold data related to specific estimates records and/or estimate details records, rather than as a list of materials complete in itself - then you need to review your tables structure and relationships, and probably alter them. hth "Darren Kozey via AccessMonster.com" wrote in message news:cc1e17ac51194260b6afc666d52b2e52@AccessMonste r.com... Hi Tina. I think I already have essentially what you described, as far as forms. I have a main form with 2 tabs, ContactInfo, and Estimate. The contactInfo tab only has the Name control, and the Estimate tab contains the sfrmEstimate, which is also a tab form with 2 tabs, Value which has the EstimateValue control, and Materials which has the ssfrmMaterials subform in datasheet view. I have navigation buttons on both the main form, and the sfrmEstimate, so that I can add more than one estimate for each contact. The only thing is my Materials ssfrm uses a textbox, and is in datasheet view. Is this going to make a difference? If so, why? My thinking of how to test that the n:n relationship was working, was to enter "screws" for example in the materials ssfrm for the first estimate, then enter "screws" again for the second estimate (both under the same contactID). Is this the right logic? It must be flawed, because both the simpler 3-table 1:n-only database, and the 4-table n:n version will allow me to do this. Where have I gone wrong? Thanks again for your help. (I think I'll be off and running once I understand what's going on behind the scenes in regards to the n:n relationship). Darren -- Message posted via http://www.accessmonster.com |
#29
|
|||
|
|||
Hi Tina.
I wish there was a standard way to express graphically and in text, both the table relationships AND the forms that are bound to them. Looking at a nice table relationships diagram is great, it allows one to see that everything can be put back together properly, but it does little to show how the data will get there, and how it will be synchronized. Not to mention, it would be tons easier to illustrate in these forums, what exactly one has done in their db. I've tried to carefully analyse what you have said. I understand para 1, and I believe what I have is already correct. Para 2: you can't link a subform [ssfrmMaterials] based on Materials to a main form [sfrmEstimate] bound to EstimateDetails If you are saying that ssfrmMaterials is the subform, and sfrmEstimate is the main form, sfrmEstimate is not bound to the EstimateDetails linking table, I have it bound to the Estimates table. a linked subform's underlying table [Materials] is always the child table [EstimateDetails] to the parent table [Estimates] bound to the main form [sfrmEstimate]. Para 3: as i said several posts earlier, the standard way to express a parent-linking-parent relationship between three tables is with one parent [Estimates] bound to the main form [sfrmEstimate], the linking or child table [EstimateDetails] bound to the subform [ssfrmMaterials], and the other parent table [Materials] used as the RowSource in a combo box in the subform [ssfrmMaterials], with the combo box's ControlSource set to the matching foreign key field [MaterialsID] in the linking table [EstimateDetails]. Are the above in [] correct? What I have done is bound ssfrmMaterials to both the EstimateDetails and Materials tables with a query. The query selects all the fields in both tables i.e. EstimateDetails.EstimateID, EstimateDetails.MaterialsID, Materials.MaterialsID, Materials.Materials. Doesn't this accomplish the same thing as having a "Materials" combobox on ssfrmMaterials (which is bound to EstimateDetails) with its RowSource set to Materials.* and its ControlSource set to EstimateDetails.MaterialsID ? The table/form structure "seems" to be working, I just have no real idea how to test the n:n relationship. To be honest, I really don't understand what "each estimate can contain many MATERIAL TYPES, and each MATERIAL TYPE can be on many estimates" means in terms of what's going on inside the database, and how it affects the user interface. I guess I'm searching for the literal definition of a n:n realtionship. Does it mean Estimate A can contain Material Types A and B, AND, Estimate B can contain Material types A and B also? Or does it mean something else? Darren -- Message posted via http://www.accessmonster.com |
#30
|
|||
|
|||
you're right, Darren. it's a lot easier to do "show and tell" rather than
just "tell". right now we're going around in circles, i think. if you want to email me a copy of your database - tables only - i will look at your tables/relationships and create a "quick and dirty" form setup to illustrate at least one common way to handle data entry in forms. i have A2003, so i can handle any db A97 or newer. if you want go this route, do the following: 1. make a copy of your db. 2. in the COPY, delete any proprietary data. replace with a few representative fake records so i have something to work with. 3. compact the db. 4. zip, if necessary, to under 1 MB. i have WinZip to unzip at this end. 5. email to tt2ac7cess188aty5ahood483otco9m. remove all the numeric characters EXCEPT the number 1, to get my correct address. refer to the newsgroups in the subject line, so i don't delete the email as spam. "Darren Kozey via AccessMonster.com" wrote in message news:304f148a023848338c7caf87a717e697@AccessMonste r.com... Hi Tina. I wish there was a standard way to express graphically and in text, both the table relationships AND the forms that are bound to them. Looking at a nice table relationships diagram is great, it allows one to see that everything can be put back together properly, but it does little to show how the data will get there, and how it will be synchronized. Not to mention, it would be tons easier to illustrate in these forums, what exactly one has done in their db. I've tried to carefully analyse what you have said. I understand para 1, and I believe what I have is already correct. Para 2: you can't link a subform [ssfrmMaterials] based on Materials to a main form [sfrmEstimate] bound to EstimateDetails If you are saying that ssfrmMaterials is the subform, and sfrmEstimate is the main form, sfrmEstimate is not bound to the EstimateDetails linking table, I have it bound to the Estimates table. a linked subform's underlying table [Materials] is always the child table [EstimateDetails] to the parent table [Estimates] bound to the main form [sfrmEstimate]. Para 3: as i said several posts earlier, the standard way to express a parent-linking-parent relationship between three tables is with one parent [Estimates] bound to the main form [sfrmEstimate], the linking or child table [EstimateDetails] bound to the subform [ssfrmMaterials], and the other parent table [Materials] used as the RowSource in a combo box in the subform [ssfrmMaterials], with the combo box's ControlSource set to the matching foreign key field [MaterialsID] in the linking table [EstimateDetails]. Are the above in [] correct? What I have done is bound ssfrmMaterials to both the EstimateDetails and Materials tables with a query. The query selects all the fields in both tables i.e. EstimateDetails.EstimateID, EstimateDetails.MaterialsID, Materials.MaterialsID, Materials.Materials. Doesn't this accomplish the same thing as having a "Materials" combobox on ssfrmMaterials (which is bound to EstimateDetails) with its RowSource set to Materials.* and its ControlSource set to EstimateDetails.MaterialsID ? The table/form structure "seems" to be working, I just have no real idea how to test the n:n relationship. To be honest, I really don't understand what "each estimate can contain many MATERIAL TYPES, and each MATERIAL TYPE can be on many estimates" means in terms of what's going on inside the database, and how it affects the user interface. I guess I'm searching for the literal definition of a n:n realtionship. Does it mean Estimate A can contain Material Types A and B, AND, Estimate B can contain Material types A and B also? Or does it mean something else? Darren -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
COMBOBOX - RECORDS IN TABLE | Samora | New Users | 5 | March 3rd, 2005 01:41 PM |
Subforms crash using ODBC | Dick Kusleika | Using Forms | 0 | January 11th, 2005 08:35 PM |
Are three primary keys less effecient than two? | Dale | Database Design | 4 | October 5th, 2004 05:33 AM |
Muliple primary key | Billy K | Database Design | 4 | May 31st, 2004 02:50 PM |