How to uniquely link subforms to main form (1 Viewer)

[quicksilver1024]

Hi,

I have a bit of a problem. I have 3 buttons that will launch 3 different subforms. These subforms are all children of the form. In my database, it is possible for all 3 subforms to be filled and created, but the problem is giving each subform an unique ID so I can link it (uniquely) to the main form.

I have the buttons to do a hide/unhide command right now, but when I click on the other subforms the data from the previous subform appears and they have the same subform ID.



Thanks.

 

[Tim L]

You are looking for a unique subform ID, but each subform is different? How can they have the same ID if they are different? I can't see how the name of the subform controls on the main form could end up with the same name, so I don't think its that. Likewise, if you do have three physically different subforms then it should be impossible for them to have the same names (Access won't allow either case). I'm therefore guessing that you are referring to the ID in the record source related to each of the subforms?

Have you checked to ensure that the record source for each subform is different from the record source of both of the other subforms?

I'm really not sure about what question you are asking, but I'm going to hazard a guess at what you are trying to do and talk about the aspects that I think you should have covered when designing the tables and relationships.

If I think I have what you are trying to do correct in my head then the following should be close to the truth (with my made up names):

A single parent form, frmMain.
Three subforms: sfmOne, sfmTwo and sfmThree.
The record sources are:
frmMain: tblMain
sfmOne: tblOne
sfmTwo: tblTwo
sfmThree: tblThree (let's call these three 'the subform tables' for ease of typing)

If this assumption is accurate then:

tblMain should have a primary key field, say, pkID.
The subform tables should all have a foreign key field, say pkID, which holds the value that represents the record in tblMain to which the record in the subform tables are linked. (Note: The subform tables do not have to have their own primary key field but would be frowned upon - it would be very poor practice for them to not have one). Because each of the subform tables is a separate entity from tblMain each subform table may or may not have values of pkID that match values of pkID in one or both of the other subform tables (so it would appear they they don't have unique IDs).

Ideally these relationships should have been recorded in the relationship editor with suitable referential integrity. This would mean that tblMain would have three relationships from pkId to each of the pkID fields in the subform tables. I've attached a jpeg of what this could look like.

If this is all set up correctly then it should just be a case of ensuring that the record source for each subform is correctly set to its relevant table and adding the subforms to the main form. With the main form in edit mode and dragging the subforms onto it one at a time, Access then should bring up a wizard each time to help you set the relationships between the master and child. You can check the values for the Link Child Fields and Link Master Fields in the data properties of the subform control are correctly set.

Incidentally, have you considered using a Tab control to switch between your subforms, rather than implementing the hide/unhide method with buttons?

HTH

Tim

 

[quicksilver1024]

Thanks for your long and detailed response Tim!
I really appreciate it.

I think the confusion is the subforms part. I forgot to say that I have a subform inside a subform. I'll try to give you a picture of it:

Mainfrm
subfrm1/subsubfrm1
subfrm2/subsubfrm2
subfrm3/subsubfrm3

The subfrm's is kind of like the categories for the data contained within the subforms. Subsubfrm's contains the data.

Right now, each subfrm generates an Autonumber which is its primary key. They are also linked to the Mainfrm via Mainfrm's PK. The subsubfrm's are the subforms of the numbered subfrms.

I can see where there problem may be. I only have a single Table of which to house all of the 3 subfrm's data. Do I have to create a table for each of the subfrm?
The reason why I have only one table is because all of the subfrm's data regardless of their categories are very very alike. I think it makes more sense for them to be in a single table.

You can see the relationship I have now in the attached pic.

 

[Tim L]

I can see where there problem may be. I only have a single Table of which to house all of the 3 subfrm's data. Do I have to create a table for each of the subfrm?

The reason why I have only one table is because all of the subfrm's data regardless of their categories are very very alike. I think it makes more sense for them to be in a single table.

The short answer is YES. The longer answer is that you should resolve all of the common data into a single table and only record the different data in the three (now much smaller) tables, for the subforms.

You can see the relationship I have now in the attached pic.

Yikes!

I don't have much time to go through this but my first impression is that you have not ensured referential integrity throughout the database. Note that in the picture I posted with my earlier reply, there are 1s and infinity symobls (the sideways 8s) at the ends of the relationships. Telling Access to ensure referential integrity is the way to ensure that you have consistent data.

Also, with regards to the tables Machine Category and Category. Each table has two relationships coming away from it, but only one of each of those relationships is linked to the primary key of each of those tables (the primary key being the attribute (field) shown in bold. If you are going to actually use the relational aspects of the database properly you should make sure that (apart from ensuring relational integrity) the links between these two tables and the reliant tables are based on the primary key. Yes this means creating multiple relationships from one table to different tables (in this case from two different table each to two (or more) further tables) but as you can see from the picture I posted earlier this is not a problem for access.

To clarify, everywhere you have Machine_Category other than in the parent table it should (probably) say Machine_Category_ID and be related to the table MachineCategory accordingly. The same goes for Cat_Name and Category_ID to the table Category.

HOWEVER, before you do all of that you need to have a look at what is being used where. Note that the table SheetMachine has two fields, MachineName and MachineCategory. One of these is redundant because you have effectively recorded the same data twice! In addition to this you have linked SheetMachine to Machine via a non-primary key field (Machine_Name) (actually you have linked it in both case via a non-primary key field). The correct field that you should (probably) be using is Machine_ID->Machine. Get rid of Machine_Name and Machine_Category from SheetMachine entirely; if you need to actually see these then you create a query that links back to them and use that as the record source for your form; Access should handle the mechanics of updating the underlying tables automatically - provided relational integrity has been enforced!

SheetDetail has a similar issue related to Cat_Name and Prob_Name.

Of course, I may be a bit wrong about what you are trying to achieve as I don't know the full story behind the concept of the database, but I think I've made a reasonable stab at it given the information I currently have.

Gotta go to work (therefore please forgive any typos or spelling mistakes). I'll check your reply later.

Tim

 

[quicksilver1024]

But, if I have separate tables for each of the categories then the relationships will be even more complex! I do not know if we are on the same page, but please see the attached picture. Is this what you mean? The arrows should obviously point to the PK's. I didn't bother pointing them to the right place.

The reason why I have Machine_name and Machine_category twice is because in those tables I have a complete list of the machines and their respective categories. So that in the SheetMachine, they can specify via a combo box which Machine/Category they are using provided in the list.

 

[Tim L]

quicksilver,

Personally, I believe that despite the duplicated 'tables' shown in your most recently posted jpg, it is much easier to understand than the previously posted one.

If I have understood what you are trying to do, from looking at both of the pictures, then the 'relationships' jpg that I have attached is more like what I would have expected to see for your db.

The 'querystart' jpg is what you get when you start to create a new query and add all of the tables at the same time - although you have to move things around a bit to tidy up the view.

The 'querymachineinfo' jpg shows all but two of the tables removed from the querystart jpg. Note the table that each of the fields comes from. If you used this as the row source for your combo box in the form where you need to select the machine (presumably the form based on what I have called REW_WRAP1_WRAP2, from your second jpg, but I now think should have been called SheetMachine but I'm not going to change right now) the user would be presented with a list including the columns Machine_ID, Machine_Name and Machine Category. If you set the column widths to "0;3;3" (adjust to suit your requirements, but keep the first one = 0) the Machine_ID column would not be visible at all and once selected the combo would show just the Machine_Name. If needed you could add a locked text box to the form to show the Machine_Category by referencing the combo.column(2). All the while, in the background, Access is maintaining the connections via the primary keys. The same technique should be used for 'problem'. Note, I am presuming here that you maintain a list of the Machines and their Category in the table Machine.

With regards to having complex relationships. Sometimes it is better to have a more complex relationship, because if you don't then you create more work for yourself.

Now, I may have totally misunderstood the way that your db is supposed to work, I don't, for instance, have suitable sample data to work with; consequently I may have made some false assumptions about how it's all supposed to fit together. However, I hope that what I have done will be useful to you.

Tim

 

[quicksilver1024]

Hi Tim,

Once again, thank you for your insightful reply.

In your QueryStart.jpg I see that Rew, Wrap1 and Wrap2 are all in one table. You meant to create a table for each right? Therefore, 3 tables in total linked in the same way in your pic. Similar to the last picture I had posted: relationships_are_very_messy.jpg

Rew, Wrap1 and Wrap2 are different machine categories. There can be all 3 machine categories for each SheetMaster.

 

[quicksilver1024]

UPDATE:

I have created separate tables and have created ref integrity links.

However, currently I see a problem. When I click on each of the 3 buttons (category buttons) a new record is generated. How can I limit the amount of new records for Rew, Wrap1 and Wrap2 to simply a single record each? I have made the Detail_ID an Autonumber field for Rew, Wrap1 and Wrap2, therefore it will generate a new ID and thus a new record each time I click on the buttons.

Please see the attached images.

Thanks,
Peter

 

[Tim L]

In your QueryStart.jpg I see that Rew, Wrap1 and Wrap2 are all in one table. You meant to create a table for each right?

Noooooo! Stop, Stop, Stop. <gets back breath> Right, <composes self>. Using a single table was deliberate.

I must admit that I've not looked at your jpg's yet (time/work constraints and poor Internet access), but I think that you need to go back and redesign using the 'concept' which I showed you.

If I remember correctly the 'tripple-named' table ("RAW_WRAP1_WRAP2")should have been called SheetMaster and was also shown in the 'relationships' jpeg.

As the data recorded in this table has the same attributes no matter which process (Raw, Wrap1, Wrap2) is the data is being recordd for, it should be capable of recording all of the information that you need.

Gotta go to work. Will check in later.

Tim

 

[Tim L]

I have created separate tables and have created ref integrity links.

Excellent, although I think that there may still be some way to go.

As I said in my previous post, using a single table was deliberate. I have adjusted the concept and the relationships. Please take a look at the attached relationships jpg. Note that the table previously called REW_WRAP1_WRAP2 is now (hopefully correctly) called SheetMaster. Note also that there is a related table, tblCO_Detail to hold the CO_Details when the record relates to a 'REW'.

I've arranged the relationships in my jpg in the best waterfall style that I could. An explantion, however, may help. The way I see it: You have machine categories, for which you have machines and for which you record SheetMachine details. You have SheetMaster information, again for which you record SheetMachine details. From the outer right to the middle: You have Problem Categories, about which you record Problems. You also record the SheetDetail about problems. Finally, each SheetMachine may have multiple SheetDetails (and hence problems) recorded about it.

I think that the design I put forward would only have issues if you recorded different information (needed different fields) about REW, WRAP1 and WRAP2 records (processes). If the machines (or other details) change dependant on the process to which the record is related then I would have a query for the Row Source for the combo which is used to select the machine, and ensure that it is filtered for the appropriate process.

The attached 'query' jpg shows how you could construct the query to use at the Record Source for the table into which you enter the REW details. Note that although the join from tblCO_Details to SheetMachine is shown in the Relationships editor as 1:many in reality you would only have an entry in tblCO_Detail when storing a REW record, and each REW record would only have one entry. Note also, that because the CO Details are recorded in a seperate table, a record will only be created if/when the user enters data in the CO_FROM or CO_TO product fields.

I've added a new field to the SheetMaster table, 'REW_WRAP_Indicator'. You should use this to store the fact about which process the record is related to. You could have this chosen by the user, perhaps from a drop-down, or you could have it entered automatically. To do it automatically you could place the data field on each of your REW, WRAP1 and WRAP2 forms, but hidden, and set the default value for the control according to the use of the form. Personally, if I didn't use a drop down (to select the REW/WRAP1/WRAP2 process), I would use the same form for both WRAP1 and WRAP2 and set the default value according to the button press, when I make the form visible, this would mean one less form to worry about. The Record Source for both/all three forms would be pretty much the same, as noted above, only without the join to the CO_Detail table for the two WRAP forms.

However, currently I see a problem. When I click on each of the 3 buttons (category buttons) a new record is generated. How can I limit the amount of new records for Rew, Wrap1 and Wrap2 to simply a single record each? I have made the Detail_ID an Autonumber field for Rew, Wrap1 and Wrap2, therefore it will generate a new ID and thus a new record each time I click on the buttons.

You don't need to have your buttons create a new record. Access will automatically create a new record as soon as some data is entered into any of the controls for a new record (or selected from a drop-down). This will not be saved until the form is closed, the user navigates away from the record or a manual 'save' action is instigated.

If you do not want the user to see previously entered detail then set the Data Entry property (Forms Properties\Data tab) to YES and the user will only be able to enter new records on the form. If you only want one record displayed at a time change the Default View from Continuous Forms to Single Form.

If there will only ever be one SheetMachine record for any combination of the pair 'Sheet Master' and 'Machine' you should consider setting SHEET_ID and MACHINE_ID, in SheetMachine, to be the Primary Key (as the pair), thus preventing duplicates.

Please see the attached images.

With regards to these, the first two images, about 90kb, seem to be almost identical in detail, did another picture go AWOL?

My apologies for the delay in getting back to you, been rather busy old chap.

I hope that you find this post useful. Please let us know how you get on.

Tim

 

[quicksilver1024]

Hi Tim,

Sorry for the late reply. My boss had my working on another project that took a considerable amount of time.

I have fixed up the forms and tables in the same way you have indicated. However, now I am getting a problem I originally had. All 3 subforms are writing to a single table (SheetMachine) therefore, when I enter data into the first form the data will appear when I click on the second (and different form). Before when I had 3 separate tables for each of the subforms, it worked nicely since the subform would autogenerate an unique ID for each category and will not generate more than one. I know I can get the subform to create a new entry by coding it in VBA (via the "New Record" macro) but this will not prevent me from being able to enter multiple records for a single subform (something I do not want).

One brute way I have in my mind right now is to create a 'status' holder in VBA. The value of the status will change after the category button is pushed indicating the system that the button has been pushed and feed this value into a If..Else statement to disable the ability to create new records. This is to allow it to create a new record on the first run but not afterwards.

Please advise.

Thanks,
Peter

 

[Tim L]

Hi Peter,

no problem, I 'm sure that we all get things like that happening all of the time

Going back to your second post in this thread, you said that you have this:

Mainfrm
subfrm1/subsubfrm1
subfrm2/subsubfrm2
subfrm3/subsubfrm3

Would you please elaborate about which Record Source each of the above is based?

Tim

 

[quicksilver1024]

Sure thing. I will even give you the actual names of the forms/subforms

Manual_Entry_Master | Rec.Source: SheetMaster | Mainform

Manual_Entry_Master_Machine_Rew | Rec.Source: SheetMachine | Subform
Manual_Entry_Master_Machine_Wrap1| Rec.Source: SheetMachine | Subform
Manual_Entry_Master_Machine_Wrap2 | Rec.Source: SheetMachine | Subform

Manual_Entry_Master_Detail_Rew | Rec.Source: SheetDetail | SubSubform
Manual_Entry_Master_Detail_Wrap1 | Rec.Source: SheetDetail | SubSubform
Manual_Entry_Master_Detail_Wrap2 | Rec.Source: SheetDetail | SubSubform


Right now I have setup the three buttons labelled Rew, Wrap1 and Wrap2 to launch the appropriate subform (ex. Manual_Entry_Master_Machine_Rew). Inside this subform is the subsubform (ex. Manual_Entry_Master_Detail_Rew)

 

[Tim L]

Okay, had a look at things. Hopefully the relationships in the attached as similar enough for you to be able to adjust as required. I've only adjusted from the concept I was working on before, rather than trying to get it like the one you posted. Also, all of the format for the fields are probably totally inconsistent with your db.

There is one form and two subforms, open the form....

Note, that on the sheetMachine subform there is a text box for the REW/WRAP1/WRAP2 indication, in use you would hide this, I've just left if visible so that you can find it easily.

The code to control the classification (that's about the best description I could come up with for 'REW', 'WRAP1' and 'WRAP2') selection is in the After Update event of the frame. The before update event fills in the correct detail for the classification; I'd thought about making it the On Change event, but if the user changed their mind about the classification before saving the record then the value would have changed since the On Change event, so we'd either need to use both the On Change and Before Update events, or include setting the value for the REW/WRAP indicator in the After Update event of the frame as well.

I'm still very concerned that you still have MACHINE ID and MACHINE CATEGORY in both SheetMachine and SheetMaster, when, if they are related, the information is already being stored. I trust that you have a *very* good reason for storing the information twice! Consider, because the information has to be entered twice a user could accidentally choose differing values for the two tables, if you don't want this then you will need to impose constraint checks to ensure that the values match. You will also need to ensure that if either value is changed in the future then the other value will be updated accordingly, or prevent the change from happening at all. Otherwise there is a risk of inconsistent data being stored. Still, as I say, if you have a good reason and actually need to store differing values, then keep it the way you have it. I would, however, be grateful if you would at least explain the reasoning.

I hope that this comes close to what you are looking for.

Tim

 

[quicksilver1024]

I've removed the duplicates. I have made a lot of changes and didn't bother removing them.

The key difference I see in your program compared to mine is the following code:

Private Sub frmClassificationChoice_AfterUpdate()

    Select Case Me.frmClassificationChoice
        Case Is = 1 ' REW
            Me.RecordSource = "SELECT SheetMachine.* FROM SheetMachine WHERE (((SheetMachine.REW_WRAP_Indicator)='REW'));"
        Case Is = 2 ' WRAP1
            Me.RecordSource = "SELECT SheetMachine.* FROM SheetMachine WHERE (((SheetMachine.REW_WRAP_Indicator)='WRAP1'));"
        Case Is = 3 ' WRAP2
            Me.RecordSource = "SELECT SheetMachine.* FROM SheetMachine WHERE (((SheetMachine.REW_WRAP_Indicator)='WRAP2'));"
    End Select

End Sub

Where is REW_WRAP_Indicator? I have tried looking around but can't find it.
Also, isn't the record source the same for all "classification"? It all writes into SheetMachine. So why was a query needed?

I'm happy to see that it will actually work though! This must have been peanuts for you to make...and it took me so long...

 

[Tim L]

I've removed the duplicates. I have made a lot of changes and didn't bother removing them.

Huh? Now I'm confused. Duplicates in whose tables, mine or yours, and did you or didn't you remove them?

Where is REW_WRAP_Indicator? I have tried looking around but can't find it.

It is in the table SheetMachine. It's not in the SheetDetail table because SheetDetail is based on SheetMachine so it inherits its classification from SheetMachine. If you need to find all SheetMachine records relating to all REW (or WRAP...) records you would build a query. As I've previously suggested, the same should be true for the MachineID and MachineCategory data.

Also, isn't the record source the same for all "classification"?

Yes. This is what I was getting at earlier in the thread. You even started off this way, in your second post:

The reason why I have only one table is because all of the subfrm's data regardless of their categories are very very alike. I think it makes more sense for them to be in a single table.

It does make more sense, but you apparently had not included a method of differentiating between the three different classifications (the indicator, for want of a better name at the time).

It all writes into SheetMachine. So why was a query needed?

To enable us to use a single subform to show the records relating to REW/WRAP1/WRAP2. I used a query instead of a filter because it frees up filters for other uses and means that we don't have to check when filters are removed to ensure that the REW/WRAP... component remains; if you didn't and the user removed the form filter then all of a sudden they would be able to see all of the REW/WRAP.. records at once. (You may want this, in which case put in a new option and remove the WHERE criteria from the query.) Additionally, the navigation bar will always indicate the true number of entries relating to each of the classifications. I initially used buttons, rather than radio buttons, but decided that radio buttons were more appropriate for this particular solution. An alternative to radio buttons would be a drop-down/combo, especially if the quantity of classifications increases.

I'm happy to see that it will actually work though! This must have been peanuts for you to make...and it took me so long...

I'm pleased that I could help and not at all; I spent several hours mucking around with different potential solutions.

You still haven't commented on the recording of the Machine ID and Category in two separate but related tables.

Tim

 

[quicksilver1024]

Okay nevermind! I found out what the REW_WRAP_INDICATOR is.

 

[Tim L]

Okay nevermind! I found out what the REW_WRAP_INDICATOR is.

Errrr, sorry, how did I upset you? I did say where it was, which was your question.

I'd hope that my explanation to you questions would be seen as helpful, rather than critical. If you took my comment so then I apologise, they weren't meant that way. As I failed to point out clearly, the method of one table that I used was, in fact, copying your original design, rather than trying to change it; your initial instincts were correct about a using single table.

I know that I keep on harping on about this, but I would be grateful if you would explain the rationale behind storing the Machine ID and Machine Category in two separate but related tables; if nothing else than to enlighten me.

Cheers.

Tim

 

[quicksilver1024]

Hm..I took a snippet of your code, but I can't get the text box which is located in the subform to display the value I selected from the Option Group box. I think this is due to the fact that I have a blank subform called 'BlankSub' which loads the actual rewinder, wrapper1 or wrapper2 subforms. The purpose of the BlankSub is to load these subforms which have a different layout.

The code I have for that is:

Me.BlankSub.Visible = True
Me.BlankSub.SourceObject = "Manual_Entry_Master_Machine_Rew"

This code is part of an edit I made based on your own code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Select Case Me.frmMachineCategory
        Case Is = 1 ' REW
            Me.txtMachineCategory.Value = "Rewinder"
        Case Is = 2 ' WRAP1
            Me.txtMachineCategory.Value = "Wrapper1"
        Case Is = 3 ' WRAP2
            Me.txtMachineCategory.Value = "Wrapper2"
    End Select

End Sub

Private Sub frmMachineCategory_AfterUpdate()

    Select Case Me.frmMachineCategory
        Case Is = 1 ' REW
            Me.BlankSub.Visible = True
            Me.BlankSub.SourceObject = "Manual_Entry_Master_Machine_Rew"
            Me.RecordSource = "SELECT SheetMachine.* FROM SheetMachine WHERE (((SheetMachine.MACHINE_CATEGORY)='Rewinder'));"
        Case Is = 2 ' WRAP1
            Me.BlankSub.Visible = True
            Me.BlankSub.SourceObject = "Manual_Entry_Master_Machine_Wrap1"
            Me.RecordSource = "SELECT SheetMachine.* FROM SheetMachine WHERE (((SheetMachine.MACHINE_CATEGORY)='Wrapper1'));"
        Case Is = 3 ' WRAP2
            Me.BlankSub.Visible = True
            Me.BlankSub.SourceObject = "Manual_Entry_Master_Machine_Wrap2"
            Me.RecordSource = "SELECT SheetMachine.* FROM SheetMachine WHERE (((SheetMachine.MACHINE_CATEGORY)='Wrapper2'));"
    End Select

End Sub

Got any advice for me Tim?

 

[Tim L]

The way I had implemented the code the Before_Update event was running on the form that held the text box (into which the 'classification' text was being placed). Does your implementation still have this condition? If not you should move the code to the same form as the text box. As you have three forms, one for each of the classifications then you'll need to put the same code into all of them. You could also experiment with setting the default value for the 'classification' text box on each form to its relevant value; you may find that you won't need the code at all!

Any chance of hearing about the multiple ID and Category fields? It's not as if I haven't earnt it, is it?

HTH

Tim