Solved Function result assigned to a listBox Rowsource - Not working (1 Viewer)

[PatAccess]

Hello Guys,
I have a Function which is supposed to return a Select statement
Here is the function

Public Function sqlTaskFName(strControl As String)
    Dim sqlTask As String
    sqlTask = "SELECT FName,[State],RegistrationNo,DateExpires,Select1,OfficeID FROM QryPE WHERE [Employee]=strControl, ORDER BY FName"
End Function

This Function is used as a rowsource for my listbox - Here is the code below

Dim strControl As String
strControl = Me.cboFName.Column(0)
Me.lstPE.RowSource = sqlTaskFName(strControl)
Me.lstPE.Requery

But when I change the listBox option nothing happens. I stepped through it and saw that

Me.cboFName.Column(0)

returns the correct value but when I hover over

Me.lstPE.RowSource

is shows Me.lstPE.RowSource = ""

Can you guys please help me?

Thank you

 

[Isaac]

Among other problems, your function doesn't return itself at the end.

try:

sqlTaskFName=sqlTask

as the last line

or just change the whole thing to

Public Function sqlTaskFName(strControl As String)
 
    sqlTaskFName = "SELECT FName,[State],RegistrationNo,DateExpires,Select1,OfficeID FROM QryPE WHERE [Employee]='" & strControl & "'  ORDER BY FName"
End Function

But there is more. You aren't correctly concatenating the string

 

[PatAccess]

Which one of the String?
The strControl is supposed to be the listbox

 

[Isaac]

First of all, a function has to assign some value to itself before the finish of it, else it isn't anything. So the way you first had the function, the function wouldn't return anything except a zero length string.

Second there was a concatenation problem. Is Employee a text-type of column in the table?

 

[PatAccess]

First of all, a function has to assign some value to itself before the finish of it, else it isn't anything. So the way you first had the function, the function wouldn't return anything except a zero length string.

Second there was a concatenation problem. Is Employee a text-type of column in the table?

Yes Employee is a text
I changed the concatenation in my function. But when I go to use the function, Is this the correct format to get column(0)?
strControl = Me.cboFName.Column(0)

 

[Isaac]

Yes Employee is a text

Okay, then at least we know the syntax (from my post #2) is correct.

Is this the correct format to get column(0)?
strControl = Me.cboFName.Column(0)

If the value you want is in the first column of the combobox's rowsource, yes

 

[PatAccess]

Okay, then at least we know the syntax (from my post #2) is correct.


If the value you want is in the first column of the combobox's rowsource, yes

Ok one more question will this same code work under a Sub?

 

[Isaac]

Ok one more question will this same code work under a Sub?

I'm not 100% sure what you mean by that question. If you want a procedure that returns something, then a Function is the way to go. Both Subs and Functions can accept input parameters, but only Functions return things.

Bear in mind that you are saving yourself exactly zero lines of code by using this particular function. It's your decision though on that

In fact I think your approach is actually costing you effort.

You could also write this one-liner:

me.lstpe.rowsource="select fname, [state],registrationno,dateexpires,select1,officeid from qrype where employee='" & me.cbofName.column(0) & "'"
me.lstpe.requery

 

[PatAccess]

I'm not 100% sure what you mean by that question. If you want a procedure that returns something, then a Function is the way to go. Both Subs and Functions can accept input parameters, but only Functions return things.

Bear in mind that you are saving yourself exactly zero lines of code by using this particular function. It's your decision though on that

In fact I think your approach is actually costing you effort.

You could also write this one-liner:

me.lstpe.rowsource="select fname, [state],registrationno,dateexpires,select1,officeid from qrype where employee='" & me.cbofName.column(0) & "'"
me.lstpe.requery

WOW. Thank you for letting me know. I was in fact trying to save lines but this is good to know what thank you very much for the One liner. It actually saves me a whole lot of line

 

[Isaac]

WOW. Thank you for letting me know. I was in fact trying to save lines but this is good to know what thank you very much for the One liner. It actually saves me a whole lot of line

Glad I could help