Proper Defining of Master/Child Relationships

[drow]

Hello Fellow Accessers,



I am currently stumped on a form/subform master/child relationship problem that likely has a simple solution. The project is one where the user has a drawing of a item to build, and based on what is shown on the drawing they must add multiple "tasks" for each drawing to define all the work that is required for that drawing. Each drawing is tracked by a autonumber primary key DrawingID.

I have a form that has cascading drop down boxes that filter a master pricing list (see attached image) so that the user can determine a price for each task that is required. The pricing for each task is dependent on a combination of variables which is why the drop down boxes are required. Each task along with the quantity of that item etc. is defined to a unique record in a table which has a autonumber primary key DrawingLineItemID. In the form, there is a subform, in continuous form view, which lists all of the drawing line items for that drawing. The purpose of this subform is to serve as a confirmation/summary to the user of all of the items that have already been added for that drawing. The master/child relationship is currently linked by the DrawingID, which allows the subform to show all of the drawing line items for that drawing.

The part I am struggling with, is I want to allow a "edit task" functionality (you can kind of see this on the far RHS of the attached screenshot, it is at the boundaries of my screen size). The purpose of the button would be to edit the details for that particular line item. The idea would be that the values in the comboboxes would be set to the existing respective values for that drawing line item, and then the user could change them as required.

So far I have tried using either the GoToRecord macro or the SetProperty macro to set the value of each combo box based on the subform values, but I have not had any success. I thought I might be able to change the master/child link to be the DrawingLineItemID, but then this would defeat the purpose of the subform. Can anyone provide any suggestions or similar examples for this type of scenario? More information on this database and the background table relationships is available at a previous post (located here: https://www.accessforums.net/forms/u...ble-42751.html).

Thanks,

Not So Advanced Beginner

[June7]

Appear to be describing a split form. Have you explored that form object?

[drow]

Hi June7,

As usual, dumb questions typically have easy answers. No, I had not investigated split forms and it does look like they will accomplish what I am trying to do. However, from some of the posts I was reading, split forms were getting some negative press based on some limitations they have. I will play around with the split forms and see if they can give all of the functionality I require. I may also see if I can there is a way to see what the coding is on the split form behind the scenes is and see if I can just modify my continuous subform to replicate that.

Thanks for the quick reply (as always).

[June7]

This might be another option for you http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

[drow]

Hi June7,

Thanks for the suggestion. I tried using a variant of the design suggested on the link you provided where the master field for the continuous subform on the screenshot of the form I posted was the DrawingID. This was all tasks were listed. I then created the cascading combo boxes as a second subform and made their master field a textbox, whos value is set to the current row on the other subform (this defines the drawinglineitemID) when the user clicks the "edit tasks" button. In theory I think this might work... however in practice is another story. The cascading combo boxes subform doesnt seem to like the text box as the master link, even if I hard code in a known, stagnant, value for a drawinglineitemID into the textbox. I also need to figure out what value can be placed in the text box (null or something else) if I want to write a new record rather than edit an existing one. I will give it another go tomorrow with fresher eyes.

Thanks for the suggestion, and I will let you know if anything productive comes out of it.

[drow]

Hi June7,

After looking into split forms a little further, I think the second link that you posted is actually the better approach as there is effectively two different master fields required for two seperate subforms. The first is DrawingID, which is the record source for the main form. This would also be the master for the "Tasks for this drawing" subform. Each line item within this subform would have both a DrawingID (foreign key) and a DrawingLineItemID (primary key).

The cascading combo boxes would then become a second subform, with the master for this field being a text box on the main form. My hope was that the default value for this textbox could be set such that it would default to a new record value, and in the case where the user clicks the "edit tasks" button on the "Tasks for this drawing" subform, the value of the text box would be set to the DrawingLineItemsID for that row.

Can you please let me know if you think this approach makes sense and might be successful? After spending about 2 hours on this last night I cant seem to get access to recognize the link between the text box with the DrawingLineItemsID value on the main form and the DrawingLineItemsID control on the subform. I have played around with supporting database provided with the link you sent and have had success manipulating that, but no such luck with my own project.

If you have any advice that would be greatly appreciated. The health of my computer may depend on it.

Thanks

[June7]

So the hierarchy of records is:

Drawing

Drawing line items

Drawing line item tasks

You want the subform for DrawingLineItems to be continuous/datasheet? If it were single, this entire arrangement could be form/subform/subsubform. But if you want both subforms to be continuous/datasheet the only approach I know is the side-by-side synchronization.

I don't know why your db isn't working - would have to review if you want to provide.

[drow]

Hi June7,

Thanks for getting back to me. It is easy to gloss over important details when you are familiar with a project and I didnt do a great job of explaining. I have considered each of the line records in DrawingLineItems as a task and have used these terms interchangeably. Therefore, to be explicitly clear, the table structure is as follows:

tblDrawings

DrawingID (pk)

ISO#

LineClass

DrawingComments

QuoteID (fk)

tblDrawingLineItems

DrawingLineItemID (pk)

DrawingID (fk)

ProductID (fk)

UnitPrice

Quantity

Subtotal

Comments

The complete structure of the database can be found here: https://www.accessforums.net/forms/u...ble-42751.html. QuoteItemsID has been replaced (in name) by DrawingLineItemsID.

My hope was that the lower half of the form, (a subform in continuous view) which has a record source of a query that is based on the fields in DrawingLineItems (plus a few other related field in other tables) display all of the records in tblDrawingLineItems that have a DrawingID equal to the current drawing ID (this is assigned to a tempvar from another form). Therefore I believe that DrawingID should be the master for this subform.

The cascading drop down boxes will be the second subform and need to either edit an existing record in tblDrawinglineItems or create a new record to add to this table. Therefore, I think the master for this subform should be DrawingLineItemID.

I will give it another shot and if I cant figure it out, a bit of work will need to be done to de-confidentialize the database and then I will post it here. In the meantime if you have any guidance, I am all ears!

Thanks

[June7]

So tblDrawingLineItems is a junction table for a many-to-many relationship.

Each drawing has many products and each product can be associated with multiple drawings.

I am familiar with TempVars but never used. Are you using macro code? I use only VBA.

[drow]

Hi June7,

Yes, your statements about the relationships are correct.

As for coding, yes I am using Macro Builder. I think VBA is technically the better solution but macro builder seems like it has a lower "entry-level" if someone besides myself has to modify/perform upkeep on the database. This link provides a quick summary on TempVars if you are interested: http://blogs.office.com/2010/09/27/p...2007-and-2010/

Quick question for you: are you able to advise if the master/child structure for the two subforms that I have proposed makes sense to you? I am going to give it one more shot tonight.

Thanks

[June7]

Not really understanding need for TempVar. Master/Child Links properties of subform container will synchronize related records.

Conventional form/subform options for many-to-many would be:

1. main form bound to tblDrawings and subform bound to tblDrawingLineItems with combobox to select product

2. main form bound tblProducts and subform bound to tblDrawingLineItems with combobox to select drawing

What are the cascading comboboxes for?

What do you mean by 'master'? Main form has the master link and subform has the child link.

[drow]

Hi June7,

The TempVar code (roughly) is SetTempVar, [DrawingID]=[TempVars]![tmpDrawingID] and it used from another form to load the form that is the discussion of this post and set the filter criteria for DrawingID. This allows the user to add of the "DrawingLineItems" for a single drawing.

The combo boxes are required because there is about 17,000 rows in tblProducts, with each of the rows being a unique combination of multiple criteria. The user uses the combo boxes to set the various criteria in order to select a single product. I have attached a screenshot to give you a sense of the data structure for this table.

By Master I just meant the field on the main form that provides the link to the child field. Not sure if my terminology is correct.

Thanks

[June7]

I use the WHERE CONDITION argument of OpenForm or OpenReport to filter form on opening. But as stated, I use only VBA and never needed TempVar for this. I am thinking of a couple places where I use global variable that TempVar might be a benefit.

[drow]

Hi June7,

The form in question is using the WHERE condition, with the TempVar functionality being used to pass this value.

I took your advice and uploaded the database to box. The link to download is below. If you are still game to help me with this issue, looking at the entire database and form structure will probably be more helpful.

https://app.box.com/s/vjvsauemhyjwr44tcjjp

Thanks

[drow]

Hi June7,

I just realized I probably should have added a bit more context. The form in question is called frmDrawingLineItems. This form is loaded from the continuous subform within the form frmQuotes.

The best way to access this form is to click on the "View/Modify Quotes" button on the main form, scroll down to the continuous subform that lists the drawings for that quote, and on the far right click the button "add tasks".

Please let me know if you encounter any issues.

Thanks