Solved how to listbox for specific record

[MK1999]

hi,
i want to view all programs for a coordinator to appear in a listbox with other information such as year..

i wrote this command

Dim stringCo as string
StringCo = "SELECT [CoordinatorID],[year],[nameÌ],[Co_name]" & "FROM Qco" & _
"WHERE [CoordinatorID] = Me.CoordinatorID.Text"
CoPro.RowSource = StringCo

it works fine for all records but when i run the condition (
& _
"WHERE [CoordinatorID] = Me.CoordinatorID.Text")
it gives a blank list box

any ideas?

 

[bob fitz]

Perhaps
"WHERE [CoordinatorID] =" & Me.CoordinatorID)
If CoordinatorID is text rather than number, try
"WHERE [CoordinatorID] ='" & Me.CoordinatorID & "'")

 

[MK1999]

thankyou but didnt work.. same issue blank listbox

 

[Eugene-LS]

any ideas?

There could be many causes for this
Can you post a sample form with the necessary tables

 

[bob fitz]

What is Me.CoordinatorID
Is the listbox bound

 

[moke123]

StringCo = "SELECT [CoordinatorID],[year],[nameÌ],[Co_name]" & "FROM Qco" & _
"WHERE [CoordinatorID] = Me.CoordinatorID.Text"

Year and Name are reserved words so you should change those to something like dteYear and txtName.
When you concatenate strings make sure you account for spaces between words.

"FROM Qco" & _ 
"WHERE

will read as

"FROM QcoWHERE

you need to add a space after Qco or before Where.

if CoordinatorID is text datatype
"WHERE CoordinatorID = """ & Me.CoordinatorID & """"

if CoordinatorID is numeric datatype
"WHERE CoordinatorID = " & Me.CoordinatorID

 

[GPGeorge]

hi,
i want to view all programs for a coordinator to appear in a listbox with other information such as year..

i wrote this command

Dim stringCo as string
StringCo = "SELECT [CoordinatorID],[year],[nameÌ],[Co_name]" & "FROM Qco" & _
"WHERE [CoordinatorID] = Me.CoordinatorID.Text"
CoPro.RowSource = StringCo

it works fine for all records but when i run the condition (
& _
"WHERE [CoordinatorID] = Me.CoordinatorID.Text")
it gives a blank list box

any ideas?

The code as posted can't work. You are concatenating strings with NO spaces between words. E.g. ... [Co_name]" & "From Qco" & _
That same thing occurs in the beginning of the next line.

The result is a string that CAN'T return anything because it's not valid SQL.

You are creating a string variable called "StringCo". Use Debug.Print to show it in the Immediate Window. Copy it to a new query and see what error is raised. Then you'll know how to fix it.

 

[GPGeorge]

The code as posted can't work. You are concatenating strings with NO spaces between words. E.g. ... [Co_name]" & "From Qco" & _
That same thing occurs in the beginning of the next line.

The result is a string that CAN'T return anything because it's not valid SQL.

You are creating a string variable called "StringCo". Use Debug.Print to show it in the Immediate Window. Copy it to a new query and see what error is raised. Then you'll know how to fix it.

Ah, I see Moke123 already pointed out the space problem....

 

[moke123]

Ah, I see Moke123 already pointed out the space problem....

But I forgot to mention debug.print which probably would have shown him the error before he even needed to post.

 

[Pat Hartman]

And then there's the confusion caused by using table level lookups. Do you have one of those by chance? If so, the combo will be returning the ID not the name of the company.