List Box

[Kat]

I have a form that I would like to creat a list box for a group of fields.
In my main table I have a group of fields that I would like to show up as a
list in a from. I want to be able to select from that list of fields and
save it to a table. Here is my example:

Table Name: Contractor
Field Names: CC1, CC2, CC3, CC4, CC5

For each contractor they could have up to 5 diffrent Cost codes. I have
listed these cost codes in the table by a column. What I want is to have one
field on a form to list all of them for that contractor in one box to select
from.

How do I do it?

 

[Sprinks]

Kat,

When you name fields 1, 2, 3, etc., it's a red flag that your tables are not
normalized. I've listed some online resources devoted to this topic, which I
think is the single most important one in relational database design. In
your example, there is a natural one-to-many relationship between a
contractor and his associated cost codes, suggesting the following table
structures:

Contractors
-----------------
ContID AutoNumber (PK)
ContName Text
Address
.....
etc.

CostCodes
-------------------
CostCodeID AutoNumber (PK)
CostCode Text

ContractorCostCodes
-------------------------
ContCostCodeID AutoNumber (PK)
ContID Integer (FK to Contractors)
CostCodeID Integer (FK to CostCodes)

To list the cost codes for the Contractor, use a combo box whose Row Source
is set in the ContractorID AfterUpdate event procedure:

Dim strSQL as String

strSQL = "SELECT ContractorCostCodes.CostID, CostCodes.CostCode " & _
"FROM (ContractorCostCodes " & _
"INNER JOIN Contractors ON ContractorCostCodes.ContID=Contractors.ContID) "
& _
"INNER JOIN CostCodes ON ContractorCostCodes.CostID=CostCodes.CostCodeID;"

With Me![YourComboBox]
.RowSource = strSQL
.Requery
End With

Set the Bound Column to 1 to save the CostCodeID to the Bound field. The
description of the cost code will appear in the drop-down list and remain
after selection if you set the first ColumnWidth to 0".

Hope that helps.
Sprinks