Listbox Will Not Display Data (VBA)

[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????

Regards, Sorrells

 

[Duvy]

Hello

Try to put a ";" at the end of your lstTask_List.RowSource definition.
This is the only diff I can see and already this type of problem : put or don't put a ";"

Hope this help
Yvon Duvivier

 

[Sorrells]

Duvy,

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! Regards, Sorrells

 

[Duvy]

Does it give an error message or just run without result ?
Another tricky thing is all those ((( ))))
Also had some problem with that
Did you try :
WHERE ((([Lookup:Walls].Bed)=True)) from the working sql

Regards,
Yvon

 

[Sorrells]

Yvon,

The form runs without error, just no display! Your suggestion about

WHERE ((([Lookup:Walls].Bed)=True))

I'd discuss more with you. Whenever I have added parenthesis into SQL, major trouble has occurred. Thus when creating a prototype in a query, I then strip out the internal parenthesis of the SQL statement. The one after the WHERE statement a particular nuisance!

I pasted the above in the statement, immediately received a syntax error. Realize that both the "Lookup:Walls" and "Bed" are actually served by variables in the actual code. In other words not an easy substitution when the generated SQL is virtually, so it seems, the same.

I am dying to find out that I am missing a property setting but can't find it. Can you see anything wrong there?

Thanks for your help.
Regards, Sorrells

 

[Sorrells]

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.
Regards, Sorrells

 

[Duvy]

Sorells,
Don't worry about time waste ... I know how it is frustrating.
Your late answer recall me another strange behavour in Access at least 97 (don't know about other version). When an error is pointed and you don't see why, I allways do a "Compile all modules" and most of the time somthing is wrong somewhere else. After correction, program run just fine.
Best regards,
Yvon

 

[Sorrells]

Yvon,

I agree. I have used that in the past and in this situation. Of course, this provided no clue this time. Regards, Sorrells