combo box base on another, save value to a table

[mxaxn]

I am working with three tables. Dept table has two columns: DeptKy (PK)and
DeptCode. Unit table has three columns: UnintKy (PK), DeptKy (FK), UnitCode.
Product table has eight columns: Barcode (PK), Dept, UnitCode +5 other
columns.

I have a form to input data into Product table, with a text box for each
column and two combo boxes: I) cboDept and II) cboUnitCode.
Each Dept has a list of UnitCodes. I am trying to show the content of the
DeptCode in the cboDept, and depending on what user selets in the cboDept, I
would like to show that list in the cboUnitCode. Then I would like to save
the content of cboDept to Dept and content of cboUnitCode to UnitCode.

Properties of cboDept is:
Control Source: dept
Row Source Type: Table/Query
Row Source: SELECT dept.DeptKy, dept.DeptCode FROM dept
Bound Column: 1
Column Count: 2
Column Width: 0; 1
This combo box is supposed to show DeptCode from the Dept table and save it
in the Dept field in Unit table.

When user chosses a DeptCode, cboUnitCode is supposed to show all the Unit
Codes for that Department and save it to Unit Code field of the Product
table. Properties for Unit table are:
Control Source: UnitCode
Bound Column: 3
Column Count: 3
Column Width: 0;0; 1
In the after update event of cboDept I have the following code:
Private Sub cboDept_AfterUpdate()
Dim unitSource As String

unitSource = "SELECT [unit].[UnitKy], [unit].[DeptKy], [unit].[UnitCode]
" & _
"FROM unit " & _
"WHERE [DeptKy] = " & Me.cboDept.Value
Me.cboUnitCode.RowSource = unitSource
Me.cboUnitCode.Requery
End Sub
This saves the data of the cboUnitCode field to the Unit Code field of the
Product table. But it saves the Primary Key NOT the content of the of the
cboDept field to the Unit field. Please help.

 

[Joan Wild]

You have a Department, which can have one or more Units. Each Unit has many
Products.
(Do I have that right?)

If so then I think you should modify the design of your tables:
Dept - DeptKy(PK), DeptCode
Unit - UnitKy(PK), DeptKy(FK), UnitCode
Product - Barcode(PK), UnitKy(FK) + 5 columns

There is no need to store the Dept in the Product table. Because you store
the UnitKy in that table, you can always lookup in the Unit table to see
what department that unit belongs to. No need to store it twice. Storing
it and seeing it on the form are not the same thing.

With this design, on your Product form you can have a cboDept combobox as
you've setup. It's sole purpose is to restrict items in the cboUnit
combobox.

Set the rowsource of cboUnit to
SELECT [unit].[UnitKy], [unit].[UnitCode] FROM unit WHERE [DeptKy] = " &
Me.cboDept;
Change the Control Source to UnitKy
Bound Column: 1

Column Count: 2
Column Width: 0; 1

In the afterupdate of cboDept you only need
Me.cboUnit.Requery


--
Joan Wild
Microsoft Access MVP

I am working with three tables. Dept table has two columns: DeptKy
(PK)and DeptCode. Unit table has three columns: UnintKy (PK), DeptKy
(FK), UnitCode. Product table has eight columns: Barcode (PK), Dept,
UnitCode +5 other columns.

I have a form to input data into Product table, with a text box for
each column and two combo boxes: I) cboDept and II) cboUnitCode.
Each Dept has a list of UnitCodes. I am trying to show the content of
the DeptCode in the cboDept, and depending on what user selets in the
cboDept, I would like to show that list in the cboUnitCode. Then I
would like to save the content of cboDept to Dept and content of
cboUnitCode to UnitCode.

Properties of cboDept is:
Control Source: dept
Row Source Type: Table/Query
Row Source: SELECT dept.DeptKy, dept.DeptCode FROM dept
Bound Column: 1
Column Count: 2
Column Width: 0; 1
This combo box is supposed to show DeptCode from the Dept table and
save it in the Dept field in Unit table.

When user chosses a DeptCode, cboUnitCode is supposed to show all the
Unit Codes for that Department and save it to Unit Code field of the
Product table. Properties for Unit table are:
Control Source: UnitCode
Bound Column: 3
Column Count: 3
Column Width: 0;0; 1
In the after update event of cboDept I have the following code:
Private Sub cboDept_AfterUpdate()
Dim unitSource As String

unitSource = "SELECT [unit].[UnitKy], [unit].[DeptKy],
[unit].[UnitCode] " & _
"FROM unit " & _
"WHERE [DeptKy] = " & Me.cboDept.Value
Me.cboUnitCode.RowSource = unitSource
Me.cboUnitCode.Requery
End Sub
This saves the data of the cboUnitCode field to the Unit Code field
of the Product table. But it saves the Primary Key NOT the content of
the of the cboDept field to the Unit field. Please help.

 

[mxaxn]

Good advice, thank you.

You have a Department, which can have one or more Units. Each Unit has many
Products.
(Do I have that right?)

If so then I think you should modify the design of your tables:
Dept - DeptKy(PK), DeptCode
Unit - UnitKy(PK), DeptKy(FK), UnitCode
Product - Barcode(PK), UnitKy(FK) + 5 columns

There is no need to store the Dept in the Product table. Because you store
the UnitKy in that table, you can always lookup in the Unit table to see
what department that unit belongs to. No need to store it twice. Storing
it and seeing it on the form are not the same thing.

With this design, on your Product form you can have a cboDept combobox as
you've setup. It's sole purpose is to restrict items in the cboUnit
combobox.

Set the rowsource of cboUnit to
SELECT [unit].[UnitKy], [unit].[UnitCode] FROM unit WHERE [DeptKy] = " &
Me.cboDept;
Change the Control Source to UnitKy
Bound Column: 1

Column Count: 2
Column Width: 0; 1

In the afterupdate of cboDept you only need
Me.cboUnit.Requery


--
Joan Wild
Microsoft Access MVP

I am working with three tables. Dept table has two columns: DeptKy
(PK)and DeptCode. Unit table has three columns: UnintKy (PK), DeptKy
(FK), UnitCode. Product table has eight columns: Barcode (PK), Dept,
UnitCode +5 other columns.

I have a form to input data into Product table, with a text box for
each column and two combo boxes: I) cboDept and II) cboUnitCode.
Each Dept has a list of UnitCodes. I am trying to show the content of
the DeptCode in the cboDept, and depending on what user selets in the
cboDept, I would like to show that list in the cboUnitCode. Then I
would like to save the content of cboDept to Dept and content of
cboUnitCode to UnitCode.

Properties of cboDept is:
Control Source: dept
Row Source Type: Table/Query
Row Source: SELECT dept.DeptKy, dept.DeptCode FROM dept
Bound Column: 1
Column Count: 2
Column Width: 0; 1
This combo box is supposed to show DeptCode from the Dept table and
save it in the Dept field in Unit table.

When user chosses a DeptCode, cboUnitCode is supposed to show all the
Unit Codes for that Department and save it to Unit Code field of the
Product table. Properties for Unit table are:
Control Source: UnitCode
Bound Column: 3
Column Count: 3
Column Width: 0;0; 1
In the after update event of cboDept I have the following code:
Private Sub cboDept_AfterUpdate()
Dim unitSource As String

unitSource = "SELECT [unit].[UnitKy], [unit].[DeptKy],
[unit].[UnitCode] " & _
"FROM unit " & _
"WHERE [DeptKy] = " & Me.cboDept.Value
Me.cboUnitCode.RowSource = unitSource
Me.cboUnitCode.Requery
End Sub
This saves the data of the cboUnitCode field to the Unit Code field
of the Product table. But it saves the Primary Key NOT the content of
the of the cboDept field to the Unit field. Please help.