Limiting list in a form

[BruDe]

Guess I got too far down the list


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 **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];"

 

[BruDe]

Thanks for you help, your suggestion worked perfectly

-----Original Message-----
BruDe,

Dim strSQL As String
strSQL = "Select" & Me!BrokerID
^^^^^^^
This doesn't work because you need to select a field name

(BrokerID), not the value from a control (Me!BrokerID). In any
case, it is not what you need.

------------
Set up the Broker combo box:

On the Data Tab:

Row Source Type: Table/Query
Row Source: SELECT [tblBroker].[lngBrokerID],

[tblBroker].[txtBrokerName] FROM tblBroker ORDER BY
[tblBroker]. [txtBrokerName]:

On the Format Tab:

Column Count: 2
Column Width: 0; 0.5

In the AfterUpdate Event:

Private Sub cboBrokerID_AfterUpdate()
Me.cboLoanOfficer = Null
Me.cboLoanOfficer.Requery
End Sub

------------
Set up the Loan Officer combo box:

On the Data Tab:

Row Source Type: Table/Query
Row Source: SELECT tblLoanOfficer.lngLoanOfficerID,

[txtLoanOfficerLastName] & ", " & [txtLoanOfficerFirstName]
AS OfficerName, tblLoanOfficer.lngBrokerID_FK FROM
tblLoanOfficer WHERE (((tblLoanOfficer.lngBrokerID_FK)=
[Forms]![Form1].[cboBrokerID]));

On the Format Tab:

Column Count: 3
Column Width: 0; 1; 0
------------

I use the three letter prefix naming convention; change the names to what you use.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

----- BruDe wrote: -----

Guess I got too far down the list


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 **
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.

.