Edward:
A further point to note is that by having both a Dealer and a Dealer Location
column in the Quotes table you are introducing redundancy and the table is
not therefore correctly normalized, and is open to the risk of inconsistent
data being entered. You only need to include the Dealer location in the
table as a foreign key which references the ID primary key of the
DealerLocations table. This determines the Dealer, so to include a column
for the latter is redundant. You would only need both columns (as a
composite foreign key) if the relationship between dealers and locations was
many-to-many, i.e. if each dealer could be in one or more locations, and each
location could have one or more dealers, for which you would need a further
table with a composite primary key of DealerID and LocationID to model the
relationship, but as your DealerLocations table references the dealer
directly this appears not to be the case.
The following is a reply I sent to a similar post not long ago. It is
analogous to your situation in that EmployeeID is the equivalent of your
Dealer Location column (referencing the ID key of your DealerLocations table),
and Title is the equivalent of your DealerName. The only difference isthat
in your case you have an ID column as the 'surrogate' key of the Dealers
table, whereas the following example uses the Title column as the 'natural'
key:
"The first thing to note is that the table to which the form is bound should
have a column only for the lower level value (EmployeeID in the example below)
, but not for the higher level value (Title in the example below). The
latter is determined by the former so if both columns are included the Title
column is redundant and the table is not normalized to Third Normal Form as
it contains a transitive functional dependency. Its consequently open to
inconsistent data.
As regards the correlated combo boxes how you set these up depends on whether
the form is in continuous forms view or single form view. If the former, and
you are using 'surrogate' numeric keys for the tables (EmployeeID in the
example below as names are unsuitable as 'natural' keys being legitimately
duplicated) then you cannot use combo boxes alone as controls in rows will go
blank if you select a different higher level value (Title in the example
below in the other). The solution is to use hybrid controls by superimposing
a text box on each combo box so that it looks like a single combo box control
to the user. Single forms are much simpler and don't require the hybrid
controls, merely the combo boxes
For continuous forms here's an example of a Projects form which includes
correlated combo boxes for Title and Employee, while maintaining the Projects
table in Third Normal Form by having an EmployeeID column, but not a Title
column:
1. An unbound cboTitles with a RowSource property of:
SELECT Title
FROM Titles
ORDER BY Title;
and an AfterUpdate event procedure of:
Private Sub cboTitles_AfterUpdate()
' requery employees combo box to
' show employees with selected title
Me!cboEmployees.Requery
' clear employees combo box
Me!cboEmployees = Null
End Sub
2. A bound cboEmployees combo box with a ControlSource property of EmployeeID
and a RowSource property of:
SELECT EmployeeID, Employee
FROM Employees
WHERE Title =Form!cboTitles
ORDER BY Employees.Employee;
The ColumnCount property is 2 and the ColumWidths property is 0cm;8cm to hide
the first column.
Note the use of the Form property in the above rather than a full reference
to the form. This is possible as both controls are ion the same form.
3. An unbound text box txtTitle superimposed of cboTitles, with a
ControlSource property of:
=GetTitle([cboEmployees])
4. An unbound text box txtEmployee superimposed of cboEmployees , witha
ControlSource property of:
=GetEmployee([cboEmployees])
Along with the other code the form's module in total would thus be:
''''module starts''''
Option Compare Database
Option Explicit
Private Function GetTitle(varEmployeeID)
' get Title for current value of EmployeeID field
If Not IsNull(varEmployeeID) Then
GetTitle = DLookup("Title", "Employees", "EmployeeID = " &
varEmployeeID)
Else
GetTitle = Me.cboTitles
End If
End Function
Private Function GetEmployee(varEmployeeID)
If Not IsNull(varEmployeeID) Then
GetEmployee = DLookup("Employee", "Employees", "EmployeeID = " &
varEmployeeID)
End If
End Function
Private Sub cboTitles_AfterUpdate()
' requery employees combo box to
' show employees with selected title
Me!cboEmployees.Requery
' clear employees combo box
Me!cboEmployees = Null
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Current()
If Me.NewRecord Then
Me!cboTitles = Null
Else
Me!cboTitles = GetTitle(Me!cboEmployees)
End If
Me!cboTitles.Requery
Me!cboEmployees.Requery
End Sub
Private Sub Form_Undo(Cancel As Integer)
If Not Me.NewRecord Then
Me!cboTitles = aOldVals(0)
End If
Me!cboTitles.Requery
Me!cboEmployees.Requery
End Sub
''''module ends''''
Basically the way it works is that when you move focus to one of the combo
boxes by clicking on its arrow its text box part becomes visible and its list
drops down. When you move focus off the control the superimposed text box
becomes visible. The functions get the text values for these by looking them
up from the relevant table on the basis of the corresponding key value which
is the hidden value of the bound cboEmployees combo box.
2. For single form view you can dispense with the two text boxes txtTitle
and txtEmployee and the module is simpler:
''''module starts
Option Compare Database
Option Explicit
Private Function GetTitle(varEmployeeID)
' get Title for current value of EmployeeID field
If Not IsNull(varEmployeeID) Then
GetTitle = DLookup("Title", "Employees", "EmployeeID = " &
varEmployeeID)
Else
GetTitle = Me.cboTitles
End If
End Function
Private Sub cboTitles_AfterUpdate()
' requery employees combo box to
' show employees with selected title
Me!cboEmployees.Requery
' clear employees combo box
Me!cboEmployees = Null
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Current()
If Me.NewRecord Then
Me!cboTitles = Null
Else
Me!cboTitles = GetTitle(Me!cboEmployees)
End If
Me!cboTitles.Requery
Me!cboEmployees.Requery
End Sub
''''module ends''''
Watch out for any lines which your newsreader might have split over two lines
in the above.
If you'd like a copy of the demo file from which the above code is taken mail
me at:
kenwsheridan<at>yahoo<dot>co<dot>uk"
NB: I'll be away incommunicado for a week after Thursday.
Ken Sheridan
Stafford, England
Hey all,
I'm having a tough time figuring out how to limit the results of one
lookup column by the value of another.
I have a table (Table name is "Quotes") with two lookup fields: One is
"Dealer" on is "Dealer Location"
So when I select the "Dealer" I want only the related "Dealer
Locations" to be available to select for dealer location.
I'm thinking that if I could find the variable for the current row's
"Dealer" value then it would work. Am I way off base here?
Here is the SQL code I put into the lookup rowsource:
SELECT DealerLocations.ID, DealerLocations.DealerLocation
FROM DealerLocations
WHERE DealerLocations.[Dealername] = Quotes.[Dealer];
When I run it, it prompts me for an ID (of the dealer), if I enter
that, then it works. However, i dont' want to enter an ID, i want it
to pass that ID. Does that make sense?
Also, this is not a form, or a query, just a table with lookup fields.
Thanks in advance,
Edward
so it was kind