Limiting list in a form

  • Thread starter Thread starter BruDe
  • Start date Start date
B

BruDe

I'm not sure if this is he corect forum for this but.
What I want to do is to limit the available choices in a
lookup field on a form based on the selection from another
list box.

I have three tables. One has loan information, the other
has available loan brokers and a third has all the loan
officers available from each broker. So, I would like to
limit the choices of available loan officers to only those
assigned to the selected broker in the add new loan form.
The data souces are the lookup columns used in the loan
information table.
 
I'm not sure if this is he corect forum for this but.
What I want to do is to limit the available choices in a
lookup field on a form based on the selection from another
list box.

I have three tables. One has loan information, the other
has available loan brokers and a third has all the loan
officers available from each broker. So, I would like to
limit the choices of available loan officers to only those
assigned to the selected broker in the add new loan form.
The data souces are the lookup columns used in the loan
information table.
Click to expand...

See:
http://www.mvps.org/access/forms/frm0028.htm
 
I tried your solution with only mixed results. Here is
the procedure as I entered it:

Private Sub BrokerID_AfterUpdate()

Dim strSQL As String
strSQL = "Select" & Me!BrokerID
strSQL = strSQL & "from Broker"
Me!LoanOfficerID.RowSourceType = "Table/Query"
Me!LoanOfficerID.RowSource = strSQL
End Sub

And for the second list box:

Private Sub LoanOfficerID_AfterUpdate()
' Me!LoanOfficerID.Requery
End Sub

I can get the fisrt box to work, and selct the broker, but
when I try to select the Loan Officer, there are no
choices, so the list isn't being populated.

here are the Row source used prior to the code:

Control Source: BrokerID
Row Source Type: Table/Query
Row Source: SELECT [Broker].[BrokerID], [Broker].
[BrokerName] FROM Broker;

For the second ListBox:

Control Source: LoanOfficerID
Row Source Type: Table/Query
Row Source: SELECT [LoanOfficer].[LoanOfficerID],
[LoanOfficer].[LoanOfficerFirstName] & " " & [LoanOfficer].
[LoanOfficerLastName] FROM LoanOfficer;

I hope this is helpful.
 
I tried your solution with only mixed results. Here is
the procedure as I entered it:

Private Sub BrokerID_AfterUpdate()

Dim strSQL As String
strSQL = "Select" & Me!BrokerID
strSQL = strSQL & "from Broker"
Me!LoanOfficerID.RowSourceType = "Table/Query"
Me!LoanOfficerID.RowSource = strSQL
End Sub

And for the second list box:

Private Sub LoanOfficerID_AfterUpdate()
' Me!LoanOfficerID.Requery
End Sub

I can get the fisrt box to work, and selct the broker, but
when I try to select the Loan Officer, there are no
choices, so the list isn't being populated.

here are the Row source used prior to the code:

Control Source: BrokerID
Row Source Type: Table/Query
Row Source: SELECT [Broker].[BrokerID], [Broker].
[BrokerName] FROM Broker;

For the second ListBox:

Control Source: LoanOfficerID
Row Source Type: Table/Query
Row Source: SELECT [LoanOfficer].[LoanOfficerID],
[LoanOfficer].[LoanOfficerFirstName] & " " & [LoanOfficer].
[LoanOfficerLastName] FROM LoanOfficer;

I hope this is helpful.
-----Original Message-----


See:
http://www.mvps.org/access/forms/frm0028.htm
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...

Try it this way
In the first Combo Box:

Control Source: BrokerID
Row Source Type: Table/Query
Row Source: SELECT [Broker].[BrokerID], [Broker]. [BrokerName] FROM
[Broker] Order by [BrokerName];

Code the AfterUpdate event of the above Broker Combo Box:

Regarding....
I have three tables. One has loan information, the
other has available loan brokers and a third has all the loan
officers available from each broker
Click to expand...
NOTE: You will have to get the LoanOfficer data from the table that
unites the broker and the loan officer.
I don't know what you named that table, so change the AfterUpdate
event table name ([CombinedTable] below) and field names as required.

LoanOfficerComboName.RowSource = "Select
[CombinedTable].[LoanOfficerID],
[CombinedTable].[LoanOfficerFirstName] & " " & [LoanOfficer].
[LoanOfficerLastName] FROM [CombinedTable] Where
[CombinedTable].[BrokerID] = " & Me![BrokerID & " Order by
[LoanOfficerLastName] & " " & [FirstName];"

In the Second combo box:
LEAVE THE ROWSOURCE BLANK

Above I used the "Where [CombinedTable].[BrokerID] = " & Me! etc.
Change this BrokerID to whatever the actual field name is that ties
the BrokerID in the Broker table to the Loan Officer in the combined
table. The above code assumes it is going to be a Number datatype.
 
I deleted the previous cose and inserted your suggested
statment in the code. It is as follows:

LoanOfficerComboName.RowSource = "Select[LoanOfficer].
[LoanOfficerID],[LoanOfficer].[LoanOfficerFirstName] & " "
& [LoanOfficer].[LoanOfficerLastName] FROM [LoanOfficer]
Where [LoanOfficer].[BrokerID] = " & Me![BrokerID & "
Order by [LoanOfficerLastName] & " " & [FirstName];"

But when I try to save it, or go to the next line, I get a
compile error message "expected: end of statement" up to
this point: & Me![BrokerID & " Order by
[LoanOfficerLastName] & " " & [FirstName];"

-----Original Message-----
I tried your solution with only mixed results. Here is
the procedure as I entered it:

Private Sub BrokerID_AfterUpdate()

Dim strSQL As String
strSQL = "Select" & Me!BrokerID
strSQL = strSQL & "from Broker"
Me!LoanOfficerID.RowSourceType = "Table/Query"
Me!LoanOfficerID.RowSource = strSQL
End Sub

And for the second list box:

Private Sub LoanOfficerID_AfterUpdate()
' Me!LoanOfficerID.Requery
End Sub

I can get the fisrt box to work, and selct the broker, but
when I try to select the Loan Officer, there are no
choices, so the list isn't being populated.

here are the Row source used prior to the code:

Control Source: BrokerID
Row Source Type: Table/Query
Row Source: SELECT [Broker].[BrokerID], [Broker].
[BrokerName] FROM Broker;

For the second ListBox:

Control Source: LoanOfficerID
Row Source Type: Table/Query
Row Source: SELECT [LoanOfficer].[LoanOfficerID],
[LoanOfficer].[LoanOfficerFirstName] & " " & [LoanOfficer].
[LoanOfficerLastName] FROM LoanOfficer;

I hope this is helpful.
-----Original Message-----
On Wed, 11 Feb 2004 14:55:06 -0800, BruDe wrote:

I'm not sure if this is he corect forum for this but.
What I want to do is to limit the available choices
Click to expand...
in
a
lookup field on a form based on the selection from another
list box.

I have three tables. One has loan information, the other
has available loan brokers and a third has all the loan
officers available from each broker. So, I would
Click to expand...
like
to
limit the choices of available loan officers to only those
assigned to the selected broker in the add new loan form.
The data souces are the lookup columns used in the loan
information table.

See:
http://www.mvps.org/access/forms/frm0028.htm
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...

Try it this way
In the first Combo Box:

Control Source: BrokerID
Row Source Type: Table/Query
Row Source: SELECT [Broker].[BrokerID], [Broker]. [BrokerName] FROM
[Broker] Order by [BrokerName];

Code the AfterUpdate event of the above Broker Combo Box:

Regarding....
I have three tables. One has loan information, the
other has available loan brokers and a third has all the loan
officers available from each broker
Click to expand...
NOTE: You will have to get the LoanOfficer data from the table that
unites the broker and the loan officer.
I don't know what you named that table, so change the AfterUpdate
event table name ([CombinedTable] below) and field names as required.

LoanOfficerComboName.RowSource = "Select
[CombinedTable].[LoanOfficerID],
[CombinedTable].[LoanOfficerFirstName] & " " & [LoanOfficer].
[LoanOfficerLastName] FROM [CombinedTable] Where
[CombinedTable].[BrokerID] = " & Me![BrokerID & " Order by
[LoanOfficerLastName] & " " & [FirstName];"

In the Second combo box:
LEAVE THE ROWSOURCE BLANK

Above I used the "Where [CombinedTable].[BrokerID] = " & Me! etc.
Change this BrokerID to whatever the actual field name is that ties
the BrokerID in the Broker table to the Loan Officer in the combined
table. The above code assumes it is going to be a Number datatype.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
I deleted the previous cose and inserted your suggested
statment in the code. It is as follows:

LoanOfficerComboName.RowSource = "Select[LoanOfficer].
[LoanOfficerID],[LoanOfficer].[LoanOfficerFirstName] & " "
& [LoanOfficer].[LoanOfficerLastName] FROM [LoanOfficer]
Where [LoanOfficer].[BrokerID] = " & Me![BrokerID & "
Order by [LoanOfficerLastName] & " " & [FirstName];"

But when I try to save it, or go to the next line, I get a
compile error message "expected: end of statement" up to
this point: & Me![BrokerID & " Order by
[LoanOfficerLastName] & " " & [FirstName];"
*** snipped **
Click to expand...

My fault ...!
I forgot about the quotes around the space in the order by [lastname]
& " " & [firstname]
Change just those double quotes to single quotes.

Where [LoanOfficer].[BrokerID] = " & Me![BrokerID & "
Order by [LoanOfficerLastName] & ' ' & [FirstName];"
 
when I try to run it. I now get and error
message: "object required." VBA will accept the code now
though. here is what I have now:

Private Sub BrokerID_AfterUpdate()

Dim strSQL As String
strSQL = "Select" & Me!BrokerID
strSQL = strSQL & "from LoanOfficer" ' this is the
table where Loan Officer and Broker are joined
Me!LoanOfficerID.RowSourceType = "Table/Query"
Me!LoanOfficerIDRowSource = strSQL
LoanOfficerComboName.RowSource = "Select
[LoanOfficer].[LoanOfficerID],[LoanOfficer].
[LoanOfficerFirstName] & ' ' & [LoanOfficer].
[LoanOfficerLastName] FROM [LoanOfficer] Where
[LoanOfficer].[BrokerID] = " & Me![BrokerID] & " Order by
[LoanOfficerLastName] & ' ' & [FirstName];"

I've tried it both ways, with and without the strSQL
statements with the same results. Seems I need to have an
additional Dim statement to call an object on the form, or
in the table, I'm not sure which.
-----Original Message-----
I deleted the previous cose and inserted your suggested
statment in the code. It is as follows:

LoanOfficerComboName.RowSource = "Select[LoanOfficer].
[LoanOfficerID],[LoanOfficer].[LoanOfficerFirstName] & " "
& [LoanOfficer].[LoanOfficerLastName] FROM [LoanOfficer]
Where [LoanOfficer].[BrokerID] = " & Me![BrokerID & "
Order by [LoanOfficerLastName] & " " & [FirstName];"

But when I try to save it, or go to the next line, I get a
compile error message "expected: end of statement" up to
this point: & Me![BrokerID & " Order by
[LoanOfficerLastName] & " " & [FirstName];"
*** snipped **
Click to expand...

My fault ...!
I forgot about the quotes around the space in the order by [lastname]
& " " & [firstname]
Change just those double quotes to single quotes.

Where [LoanOfficer].[BrokerID] = " & Me![BrokerID & "
Order by [LoanOfficerLastName] & ' ' & [FirstName];"

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Back
Top