List Box Error

  • Thread starter jahoff3 via AccessMonster.com
  • Start date
J

jahoff3 via AccessMonster.com

I have established three combo boxes. The first box contains territories.
Based off of what territory is selected, the second box is populated with
broker companies. Finally, based off of what broker compnay is selected, the
third box is populated with account information. All of this informaiton is
tied to a collection analyst, analyst extension, collection manager, and
manager extension. I would like the latter to populate into a list box so
our sales rep knows who to contact. Everything seems to be working except
for the list box containing the analyst/manager information. Here is the
code I have setup so far:

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT [Collection Analyst], [Analyst Extension],
[Collection Manager], [Manager Extension]" & _
"FROM qryCombo1 WHERE Territory = '"
Private Const strSQL2 = "' AND Broker = "
Private Const strSQL3 = "' AND Status = "
Private strSQL As String

Private Sub cboBroker_Click()
Me!cboStatus = Null
Me!cboStatus.Requery
End Sub

Private Sub cboStatus_Click()
Me!Analyst = Null
Me!Analyst.Requery
End Sub

Private Sub cboTerritory_AfterUpdate()
Call FillList
End Sub

Private Sub cboBroker_AfterUpdate()
Call FillList
End Sub
Private Sub cboStatus_AfterUpdate()
Call FillList
End Sub

Private Sub FillList()
strSQL = strSQL1 & Me!cboTerritory.Value & _
strSQL2 & Me!cboBroker.Value
Me!Analyst.RowSource = strSQL
Me!Analyst.Requery
End Sub

Private Sub cboTerritory_Click()
Me!cboBroker = Null
Me!cboStatus = Null
Me!cboBroker.Requery
Me!cboStatus.Requery
End Sub

Private Sub Form_Activate()
Call FillList
End Sub

Any suggestions on what I am doing wrong or what is missing?
 
J

Jeanette Cunningham

Hi jahoff3,

In this section of the code

Private Sub FillList()
strSQL = strSQL1 & Me!cboTerritory.Value & _
strSQL2 & Me!cboBroker.Value
Me!Analyst.RowSource = strSQL
Me!Analyst.Requery
End Sub


Put in one line that enables you to check strSQL, like this
( also remove this line *Me!Analyst.Requery* it's not needed)

Private Sub FillList()
strSQL = strSQL1 & Me!cboTerritory.Value & _
strSQL2 & Me!cboBroker.Value
Debug.print strSQL
Me!Analyst.RowSource = strSQL
End Sub

Save and compile.
Open the form and operate the combos to set the list for the listbox.
The line Debug.print strSQL will print the query in the immediate window.
Press Ctl + G to open the immediate window.
Copy the query string and paste it into the sql view of a new query.
Switch the query to datasheet view - when you do this - you will get error
messages that will enable you to fix the query.
When the query is returning records correctly, alter the sql for FillList
Now the listbox back on the form should be working.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


jahoff3 via AccessMonster.com said:
I have established three combo boxes. The first box contains territories.
Based off of what territory is selected, the second box is populated with
broker companies. Finally, based off of what broker compnay is selected,
the
third box is populated with account information. All of this informaiton
is
tied to a collection analyst, analyst extension, collection manager, and
manager extension. I would like the latter to populate into a list box so
our sales rep knows who to contact. Everything seems to be working except
for the list box containing the analyst/manager information. Here is the
code I have setup so far:

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT [Collection Analyst], [Analyst Extension],
[Collection Manager], [Manager Extension]" & _
"FROM qryCombo1 WHERE Territory = '"
Private Const strSQL2 = "' AND Broker = "
Private Const strSQL3 = "' AND Status = "
Private strSQL As String

Private Sub cboBroker_Click()
Me!cboStatus = Null
Me!cboStatus.Requery
End Sub

Private Sub cboStatus_Click()
Me!Analyst = Null
Me!Analyst.Requery
End Sub

Private Sub cboTerritory_AfterUpdate()
Call FillList
End Sub

Private Sub cboBroker_AfterUpdate()
Call FillList
End Sub
Private Sub cboStatus_AfterUpdate()
Call FillList
End Sub

Private Sub FillList()
strSQL = strSQL1 & Me!cboTerritory.Value & _
strSQL2 & Me!cboBroker.Value
Me!Analyst.RowSource = strSQL
Me!Analyst.Requery
End Sub

Private Sub cboTerritory_Click()
Me!cboBroker = Null
Me!cboStatus = Null
Me!cboBroker.Requery
Me!cboStatus.Requery
End Sub

Private Sub Form_Activate()
Call FillList
End Sub

Any suggestions on what I am doing wrong or what is missing?
Click to expand...
 
J

jahoff3 via AccessMonster.com

Hellow Jeanette!

Thank you very much for your help with this. Unfortunately, I am a beginning
beginner and would appreciate any addition detail you can give me on what to
write in my SQL FillList.

I got as far as copying the SQL query, but am not sure I am doing the correct
thing after that.

Do you have an email address that I can fwd my Access file to?

Thanks :)

Jeanette said:
Hi jahoff3,

In this section of the code

Private Sub FillList()
strSQL = strSQL1 & Me!cboTerritory.Value & _
strSQL2 & Me!cboBroker.Value
Me!Analyst.RowSource = strSQL
Me!Analyst.Requery
End Sub

Put in one line that enables you to check strSQL, like this
( also remove this line *Me!Analyst.Requery* it's not needed)

Private Sub FillList()
strSQL = strSQL1 & Me!cboTerritory.Value & _
strSQL2 & Me!cboBroker.Value
Debug.print strSQL
Me!Analyst.RowSource = strSQL
End Sub

Save and compile.
Open the form and operate the combos to set the list for the listbox.
The line Debug.print strSQL will print the query in the immediate window.
Press Ctl + G to open the immediate window.
Copy the query string and paste it into the sql view of a new query.
Switch the query to datasheet view - when you do this - you will get error
messages that will enable you to fix the query.
When the query is returning records correctly, alter the sql for FillList
Now the listbox back on the form should be working.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
I have established three combo boxes. The first box contains territories.
Based off of what territory is selected, the second box is populated with
Click to expand...
[quoted text clipped - 58 lines]
Any suggestions on what I am doing wrong or what is missing?
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Would you post the sql for qryCombo1.
You can open qryCombo1 and switch it to SQL view.
Copy the query string and paste it into your reply to this post.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


jahoff3 via AccessMonster.com said:
Hellow Jeanette!

Thank you very much for your help with this. Unfortunately, I am a
beginning
beginner and would appreciate any addition detail you can give me on what
to
write in my SQL FillList.

I got as far as copying the SQL query, but am not sure I am doing the
correct
thing after that.

Do you have an email address that I can fwd my Access file to?

Thanks :)

Jeanette said:
Hi jahoff3,

In this section of the code

Private Sub FillList()
strSQL = strSQL1 & Me!cboTerritory.Value & _
strSQL2 & Me!cboBroker.Value
Me!Analyst.RowSource = strSQL
Me!Analyst.Requery
End Sub

Put in one line that enables you to check strSQL, like this
( also remove this line *Me!Analyst.Requery* it's not needed)

Private Sub FillList()
strSQL = strSQL1 & Me!cboTerritory.Value & _
strSQL2 & Me!cboBroker.Value
Debug.print strSQL
Me!Analyst.RowSource = strSQL
End Sub

Save and compile.
Open the form and operate the combos to set the list for the listbox.
The line Debug.print strSQL will print the query in the immediate
window.
Press Ctl + G to open the immediate window.
Copy the query string and paste it into the sql view of a new query.
Switch the query to datasheet view - when you do this - you will get error
messages that will enable you to fix the query.
When the query is returning records correctly, alter the sql for FillList
Now the listbox back on the form should be working.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
I have established three combo boxes. The first box contains
territories.
Based off of what territory is selected, the second box is populated
with
Click to expand...
[quoted text clipped - 58 lines]
Any suggestions on what I am doing wrong or what is missing?
Click to expand...
Click to expand...
Click to expand...
 
J

jahoff3 via AccessMonster.com

Hello Jeanette.

Here is what it says. I am guessing this is not what it should say...

SELECT tbl_all_info.*
FROM tbl_all_info;


Jeanette said:
Would you post the sql for qryCombo1.
You can open qryCombo1 and switch it to SQL view.
Copy the query string and paste it into your reply to this post.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Hellow Jeanette!
Click to expand...
[quoted text clipped - 53 lines]
Click to expand...
 
J

Jeanette Cunningham

Try the query for the listbox like this:

strSQL = "SELECT [Collection Analyst], [Analyst Extension], " & _
"[Collection Manager], [Manager Extension] " & _
"FROM qryCombo1 WHERE Territory = " & _
"""" & Me.cboTerritory & """ " & _
"AND BROKER = """ & Me.cboBroker & """ " & _
"AND STATUS = """ & Me.cboStatus & """"

Debug.Print strSQL
Me!Analyst.RowSource = strSQL


Cunningham MS Access MVP -- Melbourne Victoria Australia


jahoff3 via AccessMonster.com said:
Hello Jeanette.

Here is what it says. I am guessing this is not what it should say...

SELECT tbl_all_info.*
FROM tbl_all_info;


Jeanette said:
Would you post the sql for qryCombo1.
You can open qryCombo1 and switch it to SQL view.
Copy the query string and paste it into your reply to this post.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Hellow Jeanette!
Click to expand...
[quoted text clipped - 53 lines]
Any suggestions on what I am doing wrong or what is missing?
Click to expand...
Click to expand...
Click to expand...
 
J

jahoff3 via AccessMonster.com

This is great.

One thing, however. In order for the information in the listbox to update,
after I change the combo box options, I have to go out of the form and enter
back in. Is there a way to fix this so it just updates as new option are
selected?

Jeanette said:
Try the query for the listbox like this:

strSQL = "SELECT [Collection Analyst], [Analyst Extension], " & _
"[Collection Manager], [Manager Extension] " & _
"FROM qryCombo1 WHERE Territory = " & _
"""" & Me.cboTerritory & """ " & _
"AND BROKER = """ & Me.cboBroker & """ " & _
"AND STATUS = """ & Me.cboStatus & """"

Debug.Print strSQL
Me!Analyst.RowSource = strSQL

Cunningham MS Access MVP -- Melbourne Victoria Australia
Hello Jeanette.
Click to expand...
[quoted text clipped - 14 lines]
Click to expand...
 
D

Douglas J. Steele

You need to issue a Requery command:

Me!Analyst.Requery

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


jahoff3 via AccessMonster.com said:
This is great.

One thing, however. In order for the information in the listbox to
update,
after I change the combo box options, I have to go out of the form and
enter
back in. Is there a way to fix this so it just updates as new option are
selected?

Jeanette said:
Try the query for the listbox like this:

strSQL = "SELECT [Collection Analyst], [Analyst Extension], " & _
"[Collection Manager], [Manager Extension] " & _
"FROM qryCombo1 WHERE Territory = " & _
"""" & Me.cboTerritory & """ " & _
"AND BROKER = """ & Me.cboBroker & """ " & _
"AND STATUS = """ & Me.cboStatus & """"

Debug.Print strSQL
Me!Analyst.RowSource = strSQL

Cunningham MS Access MVP -- Melbourne Victoria Australia
Hello Jeanette.
Click to expand...
[quoted text clipped - 14 lines]
Any suggestions on what I am doing wrong or what is missing?
Click to expand...
Click to expand...
Click to expand...
 
J

jahoff3 via AccessMonster.com

I am very new at this. Where, in the code, would I place this statement?

Here is what I have:

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT [Collection Analyst], [Analyst Extension],
[Collection Manager], [Manager Extension]" & _
"FROM qryCombo2 WHERE Territory = "
Private Const strSQL2 = "AND Broker = "
Private Const strSQL3 = "AND Status = "
Private Const strSQL4 = "ORDER by [Collection Analyst] ASC;"
Private strSQL As String

Private Sub cboBroker_Click()
Me!cboStatus = Null
Me!cboStatus.Requery
End Sub

Private Sub cboStatus_Click()
Me!Analyst = Null
Me!Analyst.Requery
End Sub

Private Sub cboTerritory_AfterUpdate()
Call FillList
End Sub

Private Sub cboBroker_AfterUpdate()
Call FillList
End Sub
Private Sub cboStatus_AfterUpdate()
Call FillList
End Sub

Private Sub FillList()
strSQL = "SELECT [Collection Analyst], [Analyst Extension], " & _
"[Collection Manager], [Manager Extension] " & _
"FROM qryCombo1 WHERE Territory = " & _
"""" & Me.cboTerritory & """ " & _
"AND BROKER = """ & Me.cboBroker & """ " & _
"AND STATUS = """ & Me.cboStatus & """"

Debug.Print strSQL
Me!Analyst.RowSource = strSQL

End Sub

Private Sub cboTerritory_Click()

Me!cboBroker = Null
Me!cboStatus = Null
Me!cboBroker.Requery
Me!cboStatus.Requery

End Sub

Private Sub Form_Activate()
Call FillList
End Sub
You need to issue a Requery command:

Me!Analyst.Requery
This is great.
Click to expand...
[quoted text clipped - 24 lines]
Click to expand...
 
J

jahoff3 via AccessMonster.com

Nevermind...I figured it out!

Thank you ALL so very much for your help :)
I am very new at this. Where, in the code, would I place this statement?

Here is what I have:

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT [Collection Analyst], [Analyst Extension],
[Collection Manager], [Manager Extension]" & _
"FROM qryCombo2 WHERE Territory = "
Private Const strSQL2 = "AND Broker = "
Private Const strSQL3 = "AND Status = "
Private Const strSQL4 = "ORDER by [Collection Analyst] ASC;"
Private strSQL As String

Private Sub cboBroker_Click()
Me!cboStatus = Null
Me!cboStatus.Requery
End Sub

Private Sub cboStatus_Click()
Me!Analyst = Null
Me!Analyst.Requery
End Sub

Private Sub cboTerritory_AfterUpdate()
Call FillList
End Sub

Private Sub cboBroker_AfterUpdate()
Call FillList
End Sub
Private Sub cboStatus_AfterUpdate()
Call FillList
End Sub

Private Sub FillList()
strSQL = "SELECT [Collection Analyst], [Analyst Extension], " & _
"[Collection Manager], [Manager Extension] " & _
"FROM qryCombo1 WHERE Territory = " & _
"""" & Me.cboTerritory & """ " & _
"AND BROKER = """ & Me.cboBroker & """ " & _
"AND STATUS = """ & Me.cboStatus & """"

Debug.Print strSQL
Me!Analyst.RowSource = strSQL

End Sub

Private Sub cboTerritory_Click()

Me!cboBroker = Null
Me!cboStatus = Null
Me!cboBroker.Requery
Me!cboStatus.Requery

End Sub

Private Sub Form_Activate()
Call FillList
End Sub
You need to issue a Requery command:
Click to expand...
[quoted text clipped - 5 lines]
Click to expand...
 
J

jahoff3 via AccessMonster.com

Is there a way to get the information to show up in the list box after
selecting an option in the first combo box and then have the information
narrowed down as selections are made int the other two combo boxes?
I am very new at this. Where, in the code, would I place this statement?

Here is what I have:

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT [Collection Analyst], [Analyst Extension],
[Collection Manager], [Manager Extension]" & _
"FROM qryCombo2 WHERE Territory = "
Private Const strSQL2 = "AND Broker = "
Private Const strSQL3 = "AND Status = "
Private Const strSQL4 = "ORDER by [Collection Analyst] ASC;"
Private strSQL As String

Private Sub cboBroker_Click()
Me!cboStatus = Null
Me!cboStatus.Requery
End Sub

Private Sub cboStatus_Click()
Me!Analyst = Null
Me!Analyst.Requery
End Sub

Private Sub cboTerritory_AfterUpdate()
Call FillList
End Sub

Private Sub cboBroker_AfterUpdate()
Call FillList
End Sub
Private Sub cboStatus_AfterUpdate()
Call FillList
End Sub

Private Sub FillList()
strSQL = "SELECT [Collection Analyst], [Analyst Extension], " & _
"[Collection Manager], [Manager Extension] " & _
"FROM qryCombo1 WHERE Territory = " & _
"""" & Me.cboTerritory & """ " & _
"AND BROKER = """ & Me.cboBroker & """ " & _
"AND STATUS = """ & Me.cboStatus & """"

Debug.Print strSQL
Me!Analyst.RowSource = strSQL

End Sub

Private Sub cboTerritory_Click()

Me!cboBroker = Null
Me!cboStatus = Null
Me!cboBroker.Requery
Me!cboStatus.Requery

End Sub

Private Sub Form_Activate()
Call FillList
End Sub
You need to issue a Requery command:
Click to expand...
[quoted text clipped - 5 lines]
Click to expand...
 
J

Jeanette Cunningham

Yes, you can do it this way.
There are pictures showing how to do this on this website

http://www.members.shaw.ca/AlbertKallal/Search/index.html

There is a sample database with search techniques that narrows down the
search as you describe on this website.

http://allenbrowne.com/ser-62.html

You will need to do quite a bit of work to get your database search going.
There is not any quick way that I know of without doing the coding to suit
your particular data. You can use a continuous form, a subform in continuous
view or a list box - the choice is yours.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


jahoff3 via AccessMonster.com said:
Is there a way to get the information to show up in the list box after
selecting an option in the first combo box and then have the information
narrowed down as selections are made int the other two combo boxes?
I am very new at this. Where, in the code, would I place this statement?

Here is what I have:

Option Compare Database
Option Explicit

Private Const strSQL1 = "SELECT [Collection Analyst], [Analyst Extension],
[Collection Manager], [Manager Extension]" & _
"FROM qryCombo2 WHERE Territory = "
Private Const strSQL2 = "AND Broker = "
Private Const strSQL3 = "AND Status = "
Private Const strSQL4 = "ORDER by [Collection Analyst] ASC;"
Private strSQL As String

Private Sub cboBroker_Click()
Me!cboStatus = Null
Me!cboStatus.Requery
End Sub

Private Sub cboStatus_Click()
Me!Analyst = Null
Me!Analyst.Requery
End Sub

Private Sub cboTerritory_AfterUpdate()
Call FillList
End Sub

Private Sub cboBroker_AfterUpdate()
Call FillList
End Sub
Private Sub cboStatus_AfterUpdate()
Call FillList
End Sub

Private Sub FillList()
strSQL = "SELECT [Collection Analyst], [Analyst Extension], " & _
"[Collection Manager], [Manager Extension] " & _
"FROM qryCombo1 WHERE Territory = " & _
"""" & Me.cboTerritory & """ " & _
"AND BROKER = """ & Me.cboBroker & """ " & _
"AND STATUS = """ & Me.cboStatus & """"

Debug.Print strSQL
Me!Analyst.RowSource = strSQL

End Sub

Private Sub cboTerritory_Click()

Me!cboBroker = Null
Me!cboStatus = Null
Me!cboBroker.Requery
Me!cboStatus.Requery

End Sub

Private Sub Form_Activate()
Call FillList
End Sub
You need to issue a Requery command:
Click to expand...
[quoted text clipped - 5 lines]
Any suggestions on what I am doing wrong or what is missing?
Click to expand...
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

Help with frmCombo Code 3
Sync two combo boxes and show the results in a list box on the form 0
VBA works in orignal but fails in copy of database 1
multiple selection list box 0
Getting an "All" choice to return all records in a list box 5
Problem adding new record to table via form - error 2105 "You can'tgo to the specified record" 1
Cascading Combo Box BIG issue 11
Filtering a search combo box 1

Top