Listbox Will Not Display Data

[Sorrells]

I've got to be doing something pretty dumb here and hope someone will grade my card to see what is missing.

I am using SQL to assign a recordset to a listbox. The code looks like this:

Dim Table_Name As String
Table_Name = "[Lookup:" & lbl_1.Caption & "]"

lstTask_List.RowSource = "SELECT " & Table_Name & ".Task, " & _
Table_Name & "." & OG_abbrev & " " & _
"FROM " & Table_Name & " " & _
"WHERE " & Table_Name & "." & OG_abbrev & "=True " & _
"ORDER BY " & Table_Name & ".Priority"

When printed in the debug window, it looks like this:
SELECT [Lookup:Walls].Task, [Lookup:Walls].Bed
FROM [Lookup:Walls]
WHERE [Lookup:Walls].Bed=True
ORDER BY [Lookup:Walls].Priority

When compared with the SQL view of a query, shown below, it looks pretty darn good:
SELECT [Lookup:Walls].Task, [Lookup:Walls].Bed
FROM [Lookup:Walls]
WHERE ((([Lookup:Walls].Bed)=True))
ORDER BY [Lookup:Walls].Priority;


In fact, I just canot figure it out!!!! The query runs and returns the correct data. If I assign the query to the RowSource, I still get no display. Thats why I think I'm overlooking something simple.

So here are the properties of the listbox. If I have omitted one of importance, please let me know.

Name: lstTask_List
RowSourceType = Table/Query
ColumnCount = 2
ColunmWidths = 2.75";0"
BoundColumn = 1
Visible = Yes
Width = 3.0625"

Well, that is about the jist of it. Any thoughts????

 

[Rich]

Isn't ; missing in the first statement
".Priority;"

 

[Sorrells]

Rich,

Good observation! I have had success without it in the past but have added it in and opened the form again. I still am getting no display and no error either.

Keep after me, I know it has to be something simple!

 

[rich.barry]

You probably need to refresh the listbox after setting the rowsource.
First check the SQL statement works by printing it to the dedug window, copying it to a blank query and running the query.

If it does the put something like:

Forms!frmwhatever!lstTask_List.Requery

after your code that sets the rowsource.

 

[Sorrells]

rich.berry,

I have had the following code at the bottom of the function:

lstTask_List.SetFocus
lstTask_List.Requery
DoCmd.RunCommand acCmdRecordsGoToFirst

A great suggestion, I just neglected to include that code in my first post!

I keep thinking that I have a listbox property set wrong.

What is more incredible is that I had this working yesterday and did not save off the form before making additional changes. I got engrossed, changed things around then no more display. Then access crashed on me and here I am.

Do you have any other thoughts?

 

[Sorrells]

It was dumb (my mistake that is)

Would you believe....

The above problem occurred in the OnOpen event of the form.

What I was missing was that later in the procedure, I made a second call to a function of the same type for when a change occurred that would require a requery of the listbox.

The SQL in that query was incorrect and I forgot that I was calling it to debug that function.

The short of it is that the SQL I have presented in this thread is correct. the data would have been displayed were it NOT for the call later to the function with incorrect SQL!

I apologize for wasting the time of you kind folks but hope the SQL above may prove helpful to some.