Full Version: linked tables displayed as subforms
UtterAccess Forums > Microsoft® Access > Access Forms
matlight
Hi guys,
hope you are well.
If I go to create a form which has data from multiple tables, it either gives me the option of displaying using subform or linked tables.
I have found that the linked tables is far better because you can click on a record in one table/form and it filters the second form based upon the record in the first table.
How do I do this in subforms instead?
If I have FOUR subforms relating to four tables linked by one to many relationships using ID's (please see attached document to see a screenshot of how the relationships work between the tables)
CATEGORIES / BRAND / VARIANT / SIZES
In the 1st, I want to select DOG
In the 2nd, I want to select from a list of products for DOG - example, BAKERS
In the 3rd, I want to select from a list of variants for the product BAKERS - example, CHICKEN & RICE
In the 4th, I want to select from a list of product sizes for BAKERS, CHICKEN & RICE - example, 1 x 15Kg STANDARD
I know I can do this using a cascading combo box for finding a product which is already present to use in an order, but I need the above method to enter in the product details to get them into the database
Please help!
Thanks and kind regards,
Matt Lightbourn
adamsherring
Have you tried using the Link Master & Link child properties of a subform? They will do the filtering for you.
And you could still use cascading combo's for data entry if you like - I think it would look better than four subforms.
Adam
matlight
Link mater & child properties - I didn't know about that, still showing my newness to all this - is that displayed under the Data tab of the properties of the actual subform?
ascading combo for displaying and adding data - really? How does that look? Do you happen to have any examples of that or where I should look?
Also, is there anyway of displaying a combo box as a list rather than a drop down menu so that it can (after a Me!forms.field.requery) show all the options and allow me to select on one to then update the next one?
Hope that made sense
Thank you for your help
adamsherring
The link master/Child property is found in the subform object (not the subform itself) found on the main form under the Data tab.
With regard to cascading combo boxes, you can use them exactly as you would before, unbound, each filtering the next, and have an "add" command button that will create an sql statement on the fly and place that into your table.

Oprefer that method as it gives me more flexibility to do whatever I want, and I'm not shy when it comes to programming.
Ex. Docmd.runsql "Insert into mytable (value1,value2,etc...) values (" & me.cboValue1 & "," & me.cbovalue2 & "," etc...)
And that would insert the record for you.
Hope this helps,
Adam
matlight
thank you for your reply. I will try and give it a go.
I'm still trying to get my head around the hands on programming - not used to doing too much of it so need a little steer in the right direction understanding how what you've written will work, still learning - is "Insert into mytable (value1,value2,etc..) creating fields for the value1,2,etc and asking the question "insert into mytable" in some popup window? Is value1, value2 the name of the unbound text boxes or the fields?
I guess I just need to know how everything else is setup around the above statement to make it work.
Sorry to ask you to explain further, I will get it - I assure you. Just need a bit more guidance - I really appreciate everything so far and feel like I am getting somewhere because of your response.
Thanks
Matt
jmcwk
Matt,

I have not read the entire thread but the attached will give you examples of cascading Combo boxes. If you open the form in design view and right click on a button and scroll down you will see Event Procedure this is the Procedure that has been entered in reference to that button and the action it takes. You can see the code by either clicking on the ... to the right of the Event procedure OR go to the Menu and select View>code and the VBA window will open. Also click on a control in the Form and look at those Event Procedures Also look at the queries in design view and you will see that the criterias are assigned to a particular control on a particular form.

In regards to linking a Main and a Subform IF you have a PK (Primary Key) on your Mainform you should have a FK (Foreign Key) on your subform in order to estabish the link. There are a few ways to do this but the most common that I know of is to use the Subform Wizard whereas you select your sunform and drag it to the detail section of your form under the mainform data and then follow the directions IF there is a link it will give you that option. The other method is to open your form in design view and click on the border of the subform and the properties window will pop up and you can enter your Child Link And Your Master link in the appropriate property. Again this is generally based on a PK and a FK. If more than one control is needed seperate the names with a ;.

HTH
Edited by: jmcwk on Fri May 19 19:19:40 EDT 2006.
matlight
Thanks for explaining all of that - I will check out the attachment.
I am fine with creating a main to sub form relationship, it is just that I might want to have a subform to subform link which are both displayed on the main form - without one being inside the other.
I want to select a record in one subform and for the second subform to display the information relating to what I have just clicked on, then I click on one of the records in the second subform and it display in a third one records relating to that one - and so on. I wish I could replicate subdatasheet environment within a form.
CATEGORY > BRAND > VARIANT > VarExt > SIZE > Product Codes
Dog > Bakers > Complete (Puppy) > Chicken & Rice > 1 x 15Kg STD > 459604840
Each detail comes from it's own table:
I have attached the the database to this so please feel free to open the categories TABLE and drill down to see how it is construcuted and let me know how you would allow users to update the database with the same efficiency as the actual table structure using subdatasheets.
Thank you for your time, patience and help
Kind Regards
Matt
sredworb
Hi Matt,
Take a look at this, I think this is what you want.
Modify the code to fit your project.
Jerry
matlight
I will take a look. Does this example also use cascading combos to enter in data as well as view it?
matlight
This is great but if I enter in something which isn't already there, it doesn't allow you to continue.
o you know a way of using this but when you enter something that isn't in the table which the combo is coming from for it to ask you do you wish to add this record to the table?
This way, if you have a new product, you can use the cascading combos to say what category, then what brand and if it isn't listed, you add one at this point - then you have to add new information for the rest of the cascade because there will be no related records lower down the drill. Do you see what I mean?
If you can think of a way of achieving that then we have the answer to my prayers - thank you
Thank for your help!
Matt
sredworb
Add this to the NotInList Event
!--c1-->
CODE
Dim db As DAO.Database
Dim rs As DAO.Recordset
If MsgBox("That Item is not in list. Do You Want to Add It?", vbOKCancel) = vbOK Then
  Set db = CurrentDb
  Set rs = db.OpenRecordset("YourTable", dbOpenDynaset)
  rs.AddNew
  rs!ControlName = NewData
  rs.Update
  Response = acDataErrAdded
  rs.CLOSE
Else
  Response = acDataErrContinue
  
End If

Jerry
matlight
THis is great! Thank you!
o it looks like it comes up with a popup question and when you click on OK it creates a new record in the "Your table" field that you are choosing from in the Combo list?
Otake it that you have to have a combo box which displays and uses the same field that you are entering into rather than a combo box which shows you the name of a brand but when selected, enters the brandID?
I hope that made sense - I know in a combo box you can choose what result you want in the text box/field and it will actually display as many field details as you like.
You have been a great help!
sredworb
Hello,
It will enter anything you want. If you have an autonumber set up as the record Id then it will just increase the number by 1 unless it's a random selection.
For example, your want to enter jetplane in a combo or list box, jetplane is not in the list, the msgbox pops and asks if you want to add it? you click OK and now this entry now becomes part of your combo or listbox. It is added to the table automatically.
Works very well.
Jerry
matlight
you're a star! You have just made my life seem bright again - it's been the milestone of my solution for a sales team.
My the way - is the any way of displaying a combo list like a normal list so that you don't have to click on a pull down menu to see all the available options? just a side thought but would be handy for some of the guys I have here!
Thank you so much
sredworb
Right click on the combobox in design view, click on change to and see if this is what you want.
Glad I could Help.
Jerry
matlight
Brilliant - thanks - I think you have solved my issue!
sredworb
You're Welcome
Glad I could Help
Jerry
matlight
Hi,
I have just got around to testing out the adding of new record to table related in combo list and it has come up with an error that I cannot work out.
please see attached doc for more details. It is stuck on the line in the code:
rs!ControlName = NewData
it says "item not found in this collection
Apart from that, it asked me the question "do i want to add record?" so up until that point it's all good. any ideas?
thanks
matlight
"Item not found in this collection"
im db As DAO.Database
Dim rs As DAO.Recordset
If MsgBox("That Item is not in list. Do You Want to Add It?", vbOKCancel) = vbOK Then
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTable", dbOpenDynaset)
rs.AddNew
rs!ControlName = NewData ********Here is where it flags up with problem
rs.Update
Response = acDataErrAdded
rs.CLOSE
Else
Response = acDataErrContinue
End If
Thanks
matlight
I found the problem
It needed to specify from field list using:
rs.Fields("Brandname").Value = NewData
instead of rs!controlname = new data
Option Compare Database
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
If MsgBox("That Item is not in list. Do You Want to Add It?", vbOKCancel) = vbOK Then
Set db = CurrentDb
Set rs = db.OpenRecordset("Brands", dbOpenDynaset)
rs.AddNew
' rs!ControlName = NewData
rs.Fields("Brandname").Value = NewData
rs.Update
Response = acDataErrAdded
rs.Close
Else
Response = acDataErrContinue

End If
End Sub
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.