change listbox rowsource using vba (1 Viewer)

[Jim8541]

Hello, I'm trying to change the row source of a listbox using VBA. My listbox is set to show column heads and multi select is set to none. I copied my sql( actually two of them, one for each of the info I want to show) from a query that returns the proper info but when I set the row source in vba and requery the listbox the listbox is empty. I tried hard coded the sql in the listbox's row source(with the proper syntax changes) and it works fine. here's my two sql"s

Private Sub chkOpenReferrals_Click()
Debug.Print
Dim strSQL As String

If Me.chkOpenReferrals = -1 Then
strSQL = "SELECT tblHistory.TicketNum, tblHistory.StartDate, tblHistory.TermDate, tblContractor.ContractorName, tblHistory.ReferralNumber, tblHistory.ReferralComplete" & _
"FROM tblContractor INNER JOIN tblHistory ON tblContractor.[ContrID-PK] = tblHistory.ContrID" & _
" WHERE (((tblHistory.TicketNum)=[forms]![frmMain].[cboticketnum]) AND ((tblHistory.ReferralComplete)=False));"




ElseIf Me.chkOpenReferrals = 0 Then

strSQL = "SELECT tblHistory.TicketNum, tblHistory.StartDate, tblHistory.TermDate, tblContractor.ContractorName, tblHistory.ReferralNumber, tblHistory.ReferralComplete" & _
"FROM tblContractor INNER JOIN tblHistory ON tblContractor.[ContrID-PK] = tblHistory.ContrID" & _
" WHERE (((tblHistory.TicketNum)=[forms]![frmMain].[cboticketnum]));"




End If

Me.lstReferrals.RowSource = strSQL
Me.lstReferrals.Requery


End Sub

Any ideas on why I can't get this to show in the listbox?
Thanks, Jim

 

[WayneRyan]

Jim,

You have to isolate the [forms]![frmMain].[cboticketnum] so that VBA
can substitute its real value.

Private Sub chkOpenReferrals_Click()
Debug.Print
Dim strSQL As String

If Me.chkOpenReferrals = -1 Then
   strSQL = "SELECT tblHistory.TicketNum, " & _
            "       tblHistory.StartDate, " & _
            "       tblHistory.TermDate, " & _
            "       tblContractor.ContractorName, " & _
            "       tblHistory.ReferralNumber, " & _
            "       tblHistory.ReferralComplete " & _
            "FROM   tblContractor INNER JOIN tblHistory " & _
            "          ON tblContractor.[ContrID-PK] = tblHistory.ContrID " & _
            "WHERE  tblHistory.TicketNum = " & [forms]![frmMain].[cboticketnum] & " AND " & _
            "       tblHistory.ReferralComplete = False;"
ElseIf Me.chkOpenReferrals = 0 Then

   strSQL = "SELECT tblHistory.TicketNum, " & _
            "       tblHistory.StartDate, " & _
            "       tblHistory.TermDate,  " & _
            "       tblContractor.ContractorName, " & _
            "       tblHistory.ReferralNumber,  " & _
            "       tblHistory.ReferralComplete " & _
            "FROM tblContractor INNER JOIN tblHistory " & _
            "        ON tblContractor.[ContrID-PK] = tblHistory.ContrID " & _
            "WHERE tblHistory.TicketNum = " & [forms]![frmMain].[cboticketnum] & ";"
End If

Me.lstReferrals.RowSource = strSQL
Me.lstReferrals.Requery

End Sub

Wayne

 

[Jim8541]

Thanks Wayne,
But now when I run it I get a pop box asking for the parameter value. Should I set the parameter value(forms!frmMain.cboticketnum) to variable and set the variable value each time I run it?
Jim

 

[john471]

Missing Space in concattenated SQL

There is a syntax error in your SQL statement. This scenario does not seem to generate an error - it just quietly dies !

Change

"FROM
to
" FROM

(include leading space, otherwise SQL is being concattenated as (e.g.)
tblHistory.ReferralCompleteFROM tblContractor
instead of
tblHistory.ReferralComplete FROM tblContractor

I've just noticed that in the time it took me to work this out, Rich has posted a reply... but seeing as you are still having trouble... I'll continue with this post anyway.

Regards

John.

Good Luck

 

[lobodava]

1. There is no space before "FROM" in the original SQL string...

2. There is no need to make Me.lstReferrals.Requery once you just set Me.lstReferrals.RowSource = strSQL

3. To debug the code and to see just made SQL string, use Debug.Print strSQL before Me.lstReferrals.RowSource = strSQL, then open immediate window, copy the SQL string and insert it in regular query builder in SQL mode, run this query and see the error.
Or just insert MsgBox strSQL before Me.lstReferrals.RowSource = strSQL and analyze the string.

Good luck!!!

 

[WayneRyan]

lobo,

Thanks for covering my back, there was a space!

But, just assigning the .RowSource doesn't refresh the data. You need to
do a .ReQuery to display the new data.

Thanks lobo,
Wayne

 

[lobodava]

Wayne Ryan,

You are always welcome.

The attacment is a db I've just made to show that .RowSource works without .Requery. It is MSA 2000.

Please show me where .RowSource doesn't work.

Thank you.
lobo

 

[WayneRyan]

lobo,

That's amazing!

I even made a combo-box based on your table, toggled the sort order,
changed its .RowSource and it works. I even made a new form and
toggled its .RecordSource between "" and "Select ..." and it Requeried!

Must investigate this later. There are COUNTLESS posts here where
people have solved their problem by doing a .ReQuery after their data
source has changed.

I have always Requeried after changing something's data source, so
it is a non-issue. Must do more research on this.

Thanks for the demo.

Wayne

 

[lobodava]

Wayne,

It is very strange... I have never seen the cases you described. It would be interesting to try it. I even heard the opposite opinion that sting Me.lst.RowSoursce=Me.lst.RowSoursce sometimes works better than .Requery

By the way, I have another "Demo" to support my point of view - the fourth post at http://www.access-programmers.co.uk/forums/showthread.php?t=63766.

Thank you.
lobo

 

[Mile-O]

But, just assigning the .RowSource doesn't refresh the data. You need to do a .ReQuery to display the new data.

Never used the ReQuery method unless the data that fed the query changes. If I'm completely changing the RowSource then this is done automatically upon assignation.

 

[WayneRyan]

lobo & SJ,

Dunno ... Every time I change a .RowSource or .RecordSource I have
done a .Requery

Just a habit. I guess it doesn't hurt to be explicit (as long as the
.ReQuery doesn't take too long).

Wayne