UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Save Data From 3 Dependant Combos, Access 2013    
 
   
soggycashew
post Feb 3 2020, 09:42 AM
Post#1



Posts: 408
Joined: 23-April 13
From: WV, USA


Hello, I'm having trouble understanding how to save data from my 3 combo boxes in form frmVWI (cboDepartment, cboCategories, cboSubcategories). If you look at the image the form uses (tbl_VWI) and I can save the data from cboSubcategories with SubcategoriesID but how do I save the others to my table tbl_VWI? I attached my relationship image and my DB broke down for the example.

Thanks,
Attached File(s)
Attached File  TEST_Blank_VWI.zip ( 381.73K )Number of downloads: 5
Attached File  Relationships.JPG ( 52.14K )Number of downloads: 9
 
Go to the top of the page
 
GroverParkGeorge
post Feb 3 2020, 09:51 AM
Post#2


UA Admin
Posts: 37,482
Joined: 20-June 02
From: Newcastle, WA


Without looking at your accdb, I am going to point out that because of the way the relationships are defined, you need only to save the SubcategoriesID in that table. Why? If you know the SubcategoryID you also know the category to which it belongs by virtue of that relationship (one-to-many), and the same is also true for the department. That is, in fact, the beauty of the Relational approach to table design. It helps to reduce redundancy, among other things.

For example, let's say that SubcategoryID 12, is in a one-to-many relationship with CategoryID 3. There is NO reason to store CategoryID 3 anywhere because you can always do a lookup to find it.
This post has been edited by GroverParkGeorge: Feb 3 2020, 09:53 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Feb 3 2020, 10:06 AM
Post#3


UA Admin
Posts: 37,482
Joined: 20-June 02
From: Newcastle, WA


Ah, I see what your problem might be.

When you select a value from one of the Upstream combo boxes, you requery the Downstream combo box for it. That part is good. But then you immediately set that Upstream combo box to Null, removing any filtering from the Downstream combo box. Don't do that and it should work.

However, I see another problem in naming. Your form in this sample has a control called "combo118", which is apparently supposed to be using Departments. However, the SQL behind the category combo box refers to a non-existent control called "cboDepartment"

Perhaps this is an artifact of creating this sample, but it prevents the Downstream combo boxes from working correctly.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
soggycashew
post Feb 3 2020, 10:58 AM
Post#4



Posts: 408
Joined: 23-April 13
From: WV, USA


GroverParkGeorge, I thought I fixed that cboDepartment in my example. I seen that prior, Sorry.... As for removing the Nulls to clear the combo boxes I commented that out and it still doesn't work. What I mean is I want to be able to go to that record and save the SubcategoryID 12 like you said to my tbl_VWI and when I open the record I need it to display data based on the SubcategoryID 12 in the cboCategories and cboDepartment and it doesn't but if you go to the tbl_VWI it saved the SubcategoryID. How do I get it to display what was previously chosen if I never saved the data except for the last combo box?
Go to the top of the page
 
GroverParkGeorge
post Feb 3 2020, 11:10 AM
Post#5


UA Admin
Posts: 37,482
Joined: 20-June 02
From: Newcastle, WA


"...I need it to display data based on the SubcategoryID 12 in the cboCategories and cboDepartment ..."

As previously noted, if you don't assign Null to those two combo boxes, they'll retain the department and category until you change them or close the form.

I see. You will have to write some additional code to lookup the categoryID and the DepartmentID and assign those values to the two combo boxes.

Because they are unbound controls, they don't retrieve values from their respective tables--as you already know.

actually, I would NOT depend on populating these two combo boxes for editing of existing records anyway. They are there ONLY to provide the filtering of the downstream combo box.

Instead I would put two additional controls on the form to display those two values in a read-only display. These could be combo boxes or text boxes. They would use the value of the current SubcategoryID to retrieve the related category and department.

I do this a lot, in fact. Here's a screen shot from a presentation I'm preparing for later this month.

Attached File  Readonlycontrol.png ( 44.14K )Number of downloads: 8

This post has been edited by GroverParkGeorge: Feb 3 2020, 11:16 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
soggycashew
post Feb 3 2020, 11:55 AM
Post#6



Posts: 408
Joined: 23-April 13
From: WV, USA


To show the data associated with the Subcategory combobox would I have to do something like rowsource = SQL? Please give an example using my data....

Thanks,
Go to the top of the page
 
GroverParkGeorge
post Feb 3 2020, 11:57 AM
Post#7


UA Admin
Posts: 37,482
Joined: 20-June 02
From: Newcastle, WA


Sure. It'll take a few minutes.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Feb 3 2020, 12:28 PM
Post#8


UA Admin
Posts: 37,482
Joined: 20-June 02
From: Newcastle, WA


After playing with this a few minutes, I'm starting to see some problems in the data. Are you sure this data is representative of real data?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Feb 3 2020, 12:48 PM
Post#9


UA Admin
Posts: 37,482
Joined: 20-June 02
From: Newcastle, WA


It looks like you actually have many-to-many relationships rather than one-to-many.

For example.

Attached File  twocategories.png ( 8.4K )Number of downloads: 0


You actually have ONLY TWO categories. VWI and LOTO.

This table creates 12 "categories" but that really isn't the case; there are two, not 12.

The same appears to be the case with subcategories. There are 40 records in that table, but some "subcategories" appear multiple times.

Attached File  duplicatesubcategories.png ( 51.91K )Number of downloads: 4


This problem doesn't make itself felt until you try to work with the assigned categories in the form.

You need two additional tables to handle this correctly. These are junction tables that allow you to handle many-to-many relationships and avoid these duplicates.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
soggycashew
post Feb 3 2020, 02:18 PM
Post#10



Posts: 408
Joined: 23-April 13
From: WV, USA


If you see in that image the categoryID's arnt the same because they go to different categories. I don't know why your saying I have many to many because all of the relationships are One to Many?

The table creates 12 categories because there is a VWI and a LOTO for each of the 6 departments
This post has been edited by soggycashew: Feb 3 2020, 02:20 PM
Go to the top of the page
 
mike60smart
post Feb 18 2020, 12:34 PM
Post#11


UtterAccess VIP
Posts: 14,027
Joined: 6-June 05
From: Dunbar,Scotland


Hi

See if this method works for you.

Attached File  TEST_Blank_VWI.zip ( 50.69K )Number of downloads: 3

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th July 2020 - 02:08 AM