Primary Keys w/ Multiple Columns: Dual Combo Boxes

T

Tatakau

I have a table that describes different tour promotions at a resort. The
primary key is made up of a Keycode and a Promocode. Other columns include
Source, Program, and Company, Price, and Premium.

I am trying to make a form that uses two combo boxes to choose the primary
key, and then auto fill the remaining fields (for viewing - not for editing).
I am trying to make the first combo box select all unique values of column
Keycode, and then the second combo box only allow selection of unique values
of column Promo that are associated with the keycode.

A long-winded example is below... I really apprecate any help I could get on
this!

Thanks in advance!

Nick

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EG (referring to the table below)

When the form opens, combo1 can select one of the many unique values from
Keycode. No values are showed more than once (such as BBACK or HMV). Combo2
remains empty until a value is selected.

If a keycode that only appears once in the table is selected (eg., 150/100,
BG, or REX), combo2 will automatically fill in the correct value. All
remaining fields will be filled in automatically.

If a keycode that appears multiple times in the table is selected (eg.,
BBACK or HMV), combo2 should remain null, but the combo box should drop down
to offer only the values that appear with the corresponding keycodes. EG.,
if BBACK is selected for combo1, combo2 should have options '35' and '36'.
If HMV is selected, combo2 should have options '33', '34', '39', and '40'.
Once the second combo box is selected, all remaining fields will be filled in
automatically.

Sample data from the table:

Keycode | Promo | Source | Program | Company | Price | Premium
150/100 20 31 53 53 200G
BBACK 35 84 24 21 $119 100G
BBACK 36 84 24 21 $169 100G
BG 8 2 41 41
HMV 33 2 19 16 $89 50G
HMV 34 2 19 16 $119 50G
HMV 39 2 19 16 $169 100G
HMV 40 2 19 16 $169 100G
REX 20 14 10 10
 
6

'69 Camaro

Hi, Nick.

Your table isn't designed for what you want to do. Add an AutoNumber column
to it and name it KPID. Make it the primary key. Keep a unique index on the
Keycode and Promo fields, make them required fields, and don't allow NULL's.

Create a new table:

Table name: tblKeyCodes
KID: AutoNumber, primary key
KeyCode: Text

Fill this table with each of the key codes. No duplicates allowed. Put a
unique index on the Keycode field, make it a required field, and don't allow
NULL's.

Create a new query and name it qryKeyCodes:

SELECT *
FROM tblKeyCodes
ORDER BY KID;

Create a new query and name it qryTourPromos:

SELECT *
FROM tblTourPromos
ORDER BY KeyCode, Promo;

Create a new form. Use the Form Wizard and base it on qryTourPromos. Set
the form's properties:

AllowAdditions: No
AllowDeletions: No

Set all text boxes' Locked Properties to Yes. Rename each text box so that
there's a "txt" prefix. For example, the default name for a text box bound
to the Promos field is Promos, so rename it as txtPromos. Remove the Control
Source Property for every text box, so that they are all unbound text boxes.

Add a header to the form. Create two new combo boxes and place them both in
the header:

Name: cboKeyCode
RowSource: qryKeyCodes

Name: cboPromo
Bound Column: 3

Paste the following in the form's module:

Private Sub cboKeyCode_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrHandler

Me!cboPromo.RowSource = "SELECT TPID, KeyCode, Promo " & _
"From qryTourPromos " & _
"WHERE (KeyCode = '" & Me!cboKeyCode.Column(1) & "') " & _
"ORDER BY KeyCode, Promo;"
Me!cboPromo.Requery

Call removeCtrlSrc

Exit Sub

ErrHandler:

MsgBox "Error in cboKeycode_BeforeUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub


Private Sub cboPromo_AfterUpdate()

On Error GoTo ErrHandler

Me.RecordSource = "SELECT * " & _
"From qryTourPromos " & _
"WHERE (TPID = " & Me!cboPromo.Column(0) & ") " & _
"ORDER BY KeyCode, Promo;"

Me!txtTPID.ControlSource = "TPID"
Me!txtKeycode.ControlSource = "Keycode"
Me!txtPromo.ControlSource = "Promo"
Me!txtSource.ControlSource = "Source"
Me!txtProgram.ControlSource = "Program"
Me!txtCompany.ControlSource = "Company"
Me!txtPrice.ControlSource = "Price"
Me!txtPremium.ControlSource = "Premium"
Me.Requery

Exit Sub

ErrHandler:

MsgBox "Error in cboPromo_AfterUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub


Private Sub Form_Load()

On Error GoTo ErrHandler

Call removeCtrlSrc

Exit Sub

ErrHandler:

MsgBox "Error in Form_Load( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub


Private Sub removeCtrlSrc()

On Error GoTo ErrHandler

Me!txtTPID.ControlSource = ""
Me!txtKeycode.ControlSource = ""
Me!txtPromo.ControlSource = ""
Me!txtSource.ControlSource = ""
Me!txtProgram.ControlSource = ""
Me!txtCompany.ControlSource = ""
Me!txtPrice.ControlSource = ""
Me!txtPremium.ControlSource = ""

Exit Sub

ErrHandler:

MsgBox "Error in removeCtrlSrc( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


Save the code and compile. Go back to the form in Design View and double
check that the event properties are set to [Event Property] in the Properties
dialog window for each of the three events above. Open the form in Form View
and select the Key Code in the combo box in the header of the form, then the
appropriate Promo in the other combo box. The form fills with just one
record for viewing purposes only.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
T

Tatakau

Thanks again Gunny - Worked like a charm!

And seriously, I meant it when I said I would like to pay you for the amount
of help you've been giving me over the past two months. You have made my
life a lot easier! E-mail me that PayPal address some time, or a mailing
address or something, k?

Later!

Nick

'69 Camaro said:
Hi, Nick.

Your table isn't designed for what you want to do. Add an AutoNumber column
to it and name it KPID. Make it the primary key. Keep a unique index on the
Keycode and Promo fields, make them required fields, and don't allow NULL's.

Create a new table:

Table name: tblKeyCodes
KID: AutoNumber, primary key
KeyCode: Text

Fill this table with each of the key codes. No duplicates allowed. Put a
unique index on the Keycode field, make it a required field, and don't allow
NULL's.

Create a new query and name it qryKeyCodes:

SELECT *
FROM tblKeyCodes
ORDER BY KID;

Create a new query and name it qryTourPromos:

SELECT *
FROM tblTourPromos
ORDER BY KeyCode, Promo;

Create a new form. Use the Form Wizard and base it on qryTourPromos. Set
the form's properties:

AllowAdditions: No
AllowDeletions: No

Set all text boxes' Locked Properties to Yes. Rename each text box so that
there's a "txt" prefix. For example, the default name for a text box bound
to the Promos field is Promos, so rename it as txtPromos. Remove the Control
Source Property for every text box, so that they are all unbound text boxes.

Add a header to the form. Create two new combo boxes and place them both in
the header:

Name: cboKeyCode
RowSource: qryKeyCodes

Name: cboPromo
Bound Column: 3

Paste the following in the form's module:

Private Sub cboKeyCode_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrHandler

Me!cboPromo.RowSource = "SELECT TPID, KeyCode, Promo " & _
"From qryTourPromos " & _
"WHERE (KeyCode = '" & Me!cboKeyCode.Column(1) & "') " & _
"ORDER BY KeyCode, Promo;"
Me!cboPromo.Requery

Call removeCtrlSrc

Exit Sub

ErrHandler:

MsgBox "Error in cboKeycode_BeforeUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub


Private Sub cboPromo_AfterUpdate()

On Error GoTo ErrHandler

Me.RecordSource = "SELECT * " & _
"From qryTourPromos " & _
"WHERE (TPID = " & Me!cboPromo.Column(0) & ") " & _
"ORDER BY KeyCode, Promo;"

Me!txtTPID.ControlSource = "TPID"
Me!txtKeycode.ControlSource = "Keycode"
Me!txtPromo.ControlSource = "Promo"
Me!txtSource.ControlSource = "Source"
Me!txtProgram.ControlSource = "Program"
Me!txtCompany.ControlSource = "Company"
Me!txtPrice.ControlSource = "Price"
Me!txtPremium.ControlSource = "Premium"
Me.Requery

Exit Sub

ErrHandler:

MsgBox "Error in cboPromo_AfterUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub


Private Sub Form_Load()

On Error GoTo ErrHandler

Call removeCtrlSrc

Exit Sub

ErrHandler:

MsgBox "Error in Form_Load( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub


Private Sub removeCtrlSrc()

On Error GoTo ErrHandler

Me!txtTPID.ControlSource = ""
Me!txtKeycode.ControlSource = ""
Me!txtPromo.ControlSource = ""
Me!txtSource.ControlSource = ""
Me!txtProgram.ControlSource = ""
Me!txtCompany.ControlSource = ""
Me!txtPrice.ControlSource = ""
Me!txtPremium.ControlSource = ""

Exit Sub

ErrHandler:

MsgBox "Error in removeCtrlSrc( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


Save the code and compile. Go back to the form in Design View and double
check that the event properties are set to [Event Property] in the Properties
dialog window for each of the three events above. Open the form in Form View
and select the Key Code in the combo box in the header of the form, then the
appropriate Promo in the other combo box. The form fills with just one
record for viewing purposes only.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Tatakau said:
I have a table that describes different tour promotions at a resort. The
primary key is made up of a Keycode and a Promocode. Other columns include
Source, Program, and Company, Price, and Premium.

I am trying to make a form that uses two combo boxes to choose the primary
key, and then auto fill the remaining fields (for viewing - not for editing).
I am trying to make the first combo box select all unique values of column
Keycode, and then the second combo box only allow selection of unique values
of column Promo that are associated with the keycode.

A long-winded example is below... I really apprecate any help I could get on
this!

Thanks in advance!

Nick

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EG (referring to the table below)

When the form opens, combo1 can select one of the many unique values from
Keycode. No values are showed more than once (such as BBACK or HMV). Combo2
remains empty until a value is selected.

If a keycode that only appears once in the table is selected (eg., 150/100,
BG, or REX), combo2 will automatically fill in the correct value. All
remaining fields will be filled in automatically.

If a keycode that appears multiple times in the table is selected (eg.,
BBACK or HMV), combo2 should remain null, but the combo box should drop down
to offer only the values that appear with the corresponding keycodes. EG.,
if BBACK is selected for combo1, combo2 should have options '35' and '36'.
If HMV is selected, combo2 should have options '33', '34', '39', and '40'.
Once the second combo box is selected, all remaining fields will be filled in
automatically.

Sample data from the table:

Keycode | Promo | Source | Program | Company | Price | Premium
150/100 20 31 53 53 200G
BBACK 35 84 24 21 $119 100G
BBACK 36 84 24 21 $169 100G
BG 8 2 41 41
HMV 33 2 19 16 $89 50G
HMV 34 2 19 16 $119 50G
HMV 39 2 19 16 $169 100G
HMV 40 2 19 16 $169 100G
REX 20 14 10 10
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to Hard Code in Combo Boxes Instead of using a lookup table 4
Combo box contents doesn't change when filtering combo box is chan 2
Combo Box Populating 2
setting value of combo box 3
Trouble with combo box 1
Combo box trouble 1
Cascading combo box 4
combo box data type 1

Top