Listbox rowsource issue

titobudd · Sep 29, 2011

I have two tables linked through an ODBC connection. I have a form with a textbox for entering a date and a combobox for selecting one of 7 attributes, and a listbox for displaying query results.

I am having a problem with the listbox populating with values when updating the rowsource through the textbox and combobox object changes. I am having success when the form loads with getting the listbox to populate, but after changing the date or combobox values to filter the listbox results, nothing populates the listbox. I also have a button for exporting the listbox results to excel, which works because of a temporary query that is created based upon the values entered/selected in the previously mentioned text and combo boxes. A dynamic query is created and the listbox rowsource is set to that query.

What I find to be interesting is that I am able to get the listbox to populate through the textbox and combobox object changes after I have done the export to excel function or if I open the Linked Table Manager and refresh the linked tables. These actions trigger something that makes everything suddenly work as intended. I'm not sure what is going on. If anyone has any ideas, your input is appreciated. Thanks.

Here's some of my code:

Code:

Private Sub Form_Load()
      
Dim sql As String

Forms("Report_Creation_Form")!cboProblem = "ALL"

Me.txtFromDate = Date
Me.txtFromDate.SetFocus

sql = "SELECT azteca_REQUEST.REQUESTID AS Request_ID, azteca_CUSTOMERCALL.DATETIMECALL AS Call_Date_Time, azteca_REQUEST.PROBLEMCODE AS Problem_Code, azteca_REQUEST.PROBADDRESS AS Problem_Address, azteca_CUSTOMERCALL.FIRSTNAME AS Caller_First_Name, " & _
      "azteca_CUSTOMERCALL.LASTNAME AS Caller_Last_Name, azteca_CUSTOMERCALL.HOMEPHONE AS Home_Phone, azteca_CUSTOMERCALL.WORKPHONE AS Work_Phone, azteca_CUSTOMERCALL.OTHERPHONE AS Other_Phone, azteca_CUSTOMERCALL.CUSTADDRESS AS Caller_Address, " & _
      "azteca_CUSTOMERCALL.CUSTCITY AS Caller_City, azteca_CUSTOMERCALL.CUSTZIP AS Caller_Zip, " & _
      "azteca_REQUEST.TEXT1 AS Reading, azteca_REQUEST.TEXT2 AS Notes " & _
      "FROM azteca_CUSTOMERCALL INNER JOIN azteca_REQUEST ON azteca_CUSTOMERCALL.REQUESTID = azteca_REQUEST.REQUESTID " & _
      "WHERE azteca_REQUEST.PROBLEMCODE = 'WATER - GENERAL' OR azteca_REQUEST.PROBLEMCODE = 'WATER - HYDRANT' OR azteca_REQUEST.PROBLEMCODE = 'WATER - MAIN BREAK' OR azteca_REQUEST.PROBLEMCODE = 'WATER - METER MAINT' OR azteca_REQUEST.PROBLEMCODE = 'WATER - TURN OFF' OR azteca_REQUEST.PROBLEMCODE = 'WATER - TURN ON' " & _
      "ORDER BY azteca_CUSTOMERCALL.DATETIMECALL DESC"


Me.lstReport.RowSource = sql
Me.lstReport.Requery

Me.cboProblem.Enabled = False
Me.cmdExport.Enabled = False

End Sub


Private Sub txtFromDate_Change()

Dim sqlDate As String

Me.txtFromDate.SetFocus

Select Case Me.cboProblem

Case "ALL"

sqlDate = "SELECT azteca_REQUEST.REQUESTID AS Request_ID, azteca_CUSTOMERCALL.DATETIMECALL AS Call_Date_Time, azteca_REQUEST.PROBLEMCODE AS Problem_Code, azteca_REQUEST.PROBADDRESS AS Problem_Address, azteca_CUSTOMERCALL.FIRSTNAME AS Caller_First_Name, " & _
          "azteca_CUSTOMERCALL.LASTNAME AS Caller_Last_Name, azteca_CUSTOMERCALL.HOMEPHONE AS Home_Phone, azteca_CUSTOMERCALL.WORKPHONE AS Work_Phone, azteca_CUSTOMERCALL.OTHERPHONE AS Other_Phone, azteca_CUSTOMERCALL.CUSTADDRESS AS Caller_Address, " & _
          "azteca_CUSTOMERCALL.CUSTCITY AS Caller_City, azteca_CUSTOMERCALL.CUSTZIP AS Caller_Zip, " & _
          "azteca_REQUEST.TEXT1 AS Read, azteca_REQUEST.TEXT2 AS Notes " & _
          "FROM azteca_CUSTOMERCALL INNER JOIN azteca_REQUEST ON azteca_CUSTOMERCALL.REQUESTID = azteca_REQUEST.REQUESTID " & _
          "WHERE azteca_CUSTOMERCALL.DATETIMECALL >= CDate('" & Me.txtFromDate.Text & "') AND (azteca_REQUEST.PROBLEMCODE = 'WATER - GENERAL' OR azteca_REQUEST.PROBLEMCODE = 'WATER - HYDRANT' OR azteca_REQUEST.PROBLEMCODE = 'WATER - MAIN BREAK' OR azteca_REQUEST.PROBLEMCODE = 'WATER - METER MAINT' OR azteca_REQUEST.PROBLEMCODE = 'WATER - TURN OFF' OR azteca_REQUEST.PROBLEMCODE = 'WATER - TURN ON') " & _
          "ORDER BY azteca_CUSTOMERCALL.DATETIMECALL DESC"
      

Me.lstReport.RowSource = sqlDate

If Me.lstReport.ListCount = 0 Then
Me.txtFileName = ""
Me.cmdExport.Enabled = False
Else
Me.txtFileName = IIf(Len(DatePart("m", Date)) = 1, 0 & DatePart("m", Date), DatePart("m", Date)) & "-" & IIf(Len(DatePart("d", Date)) = 1, 0 & DatePart("d", Date), DatePart("d", Date)) & "-" & Right(DatePart("yyyy", Date), 2) & " All"
Me.cmdExport.Enabled = True
Me.cboProblem.Enabled = True
End If

Case "WATER - TURN ON"

sqlDate = "SELECT azteca_REQUEST.REQUESTID AS Request_ID, azteca_CUSTOMERCALL.DATETIMECALL AS Call_Date_Time, azteca_REQUEST.PROBLEMCODE AS Problem_Code, azteca_REQUEST.PROBADDRESS AS Problem_Address, azteca_CUSTOMERCALL.FIRSTNAME AS Caller_First_Name, " & _
          "azteca_CUSTOMERCALL.LASTNAME AS Caller_Last_Name, azteca_CUSTOMERCALL.HOMEPHONE AS Home_Phone, azteca_CUSTOMERCALL.WORKPHONE AS Work_Phone, azteca_CUSTOMERCALL.OTHERPHONE AS Other_Phone, azteca_CUSTOMERCALL.CUSTADDRESS AS Caller_Address, " & _
          "azteca_CUSTOMERCALL.CUSTCITY AS Caller_City, azteca_CUSTOMERCALL.CUSTZIP AS Caller_Zip, " & _
          "azteca_REQUEST.TEXT1 AS Read, azteca_REQUEST.TEXT2 AS Notes " & _
          "FROM azteca_CUSTOMERCALL INNER JOIN azteca_REQUEST ON azteca_CUSTOMERCALL.REQUESTID = azteca_REQUEST.REQUESTID " & _
          "WHERE azteca_CUSTOMERCALL.DATETIMECALL >= CDate('" & Me.txtFromDate.Text & "') AND (azteca_REQUEST.PROBLEMCODE = 'WATER - TURN ON') " & _
          "ORDER BY azteca_CUSTOMERCALL.DATETIMECALL DESC"
      

Me.lstReport.RowSource = sqlDate

If Me.lstReport.ListCount = 0 Then
Me.txtFileName = ""
Me.cmdExport.Enabled = False
Else
Me.txtFileName = IIf(Len(DatePart("m", Date)) = 1, 0 & DatePart("m", Date), DatePart("m", Date)) & "-" & IIf(Len(DatePart("d", Date)) = 1, 0 & DatePart("d", Date), DatePart("d", Date)) & "-" & Right(DatePart("yyyy", Date), 2) & " On"
Me.cmdExport.Enabled = True
End If

Case "WATER - TURN OFF"

sqlDate = "SELECT azteca_REQUEST.REQUESTID AS Request_ID, azteca_CUSTOMERCALL.DATETIMECALL AS Call_Date_Time, azteca_REQUEST.PROBLEMCODE AS Problem_Code, azteca_REQUEST.PROBADDRESS AS Problem_Address, azteca_CUSTOMERCALL.FIRSTNAME AS Caller_First_Name, " & _
          "azteca_CUSTOMERCALL.LASTNAME AS Caller_Last_Name, azteca_CUSTOMERCALL.HOMEPHONE AS Home_Phone, azteca_CUSTOMERCALL.WORKPHONE AS Work_Phone, azteca_CUSTOMERCALL.OTHERPHONE AS Other_Phone, azteca_CUSTOMERCALL.CUSTADDRESS AS Caller_Address, " & _
          "azteca_CUSTOMERCALL.CUSTCITY AS Caller_City, azteca_CUSTOMERCALL.CUSTZIP AS Caller_Zip, " & _
          "azteca_REQUEST.TEXT1 AS Read, azteca_REQUEST.TEXT2 AS Notes " & _
          "FROM azteca_CUSTOMERCALL INNER JOIN azteca_REQUEST ON azteca_CUSTOMERCALL.REQUESTID = azteca_REQUEST.REQUESTID " & _
          "WHERE azteca_CUSTOMERCALL.DATETIMECALL >= CDate('" & Me.txtFromDate.Text & "') AND (azteca_REQUEST.PROBLEMCODE = 'WATER - TURN OFF') " & _
          "ORDER BY azteca_CUSTOMERCALL.DATETIMECALL DESC"
      
Me.lstReport.RowSource = sqlDate

If Me.lstReport.ListCount = 0 Then
Me.txtFileName = ""
Me.cmdExport.Enabled = False
Else
Me.txtFileName = IIf(Len(DatePart("m", Date)) = 1, 0 & DatePart("m", Date), DatePart("m", Date)) & "-" & IIf(Len(DatePart("d", Date)) = 1, 0 & DatePart("d", Date), DatePart("d", Date)) & "-" & Right(DatePart("yyyy", Date), 2) & " Off"
Me.cmdExport.Enabled = True
Me.cboProblem.Enabled = True
End If

'etc.

End Select

Me.txtFromDate.SetFocus

End Sub


Private Sub cmdExport_Click()

Dim strQryName As String, strXLFile As String

If DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = 'qryTemp101'") <> 0 Then
        DoCmd.DeleteObject acQuery, "qryTemp101"
        CurrentDb.QueryDefs.Refresh
End If

Select Case Me.cboProblem

Case "ALL"
    
    Me.txtFromDate.SetFocus
    CurrentDb.CreateQueryDef "qryTemp101", "SELECT azteca_REQUEST.REQUESTID AS Request_ID, azteca_CUSTOMERCALL.DATETIMECALL AS Call_Date_Time, azteca_REQUEST.PROBLEMCODE AS Problem_Code, azteca_REQUEST.PROBADDRESS AS Problem_Address, azteca_CUSTOMERCALL.FIRSTNAME AS Caller_First_Name, " & _
             "azteca_CUSTOMERCALL.LASTNAME AS Caller_Last_Name, azteca_CUSTOMERCALL.HOMEPHONE AS Home_Phone, azteca_CUSTOMERCALL.WORKPHONE AS Work_Phone, azteca_CUSTOMERCALL.OTHERPHONE AS Other_Phone, azteca_CUSTOMERCALL.CUSTADDRESS AS Caller_Address, " & _
             "azteca_CUSTOMERCALL.CUSTCITY AS Caller_City, azteca_CUSTOMERCALL.CUSTZIP AS Caller_Zip, " & _
             "azteca_REQUEST.TEXT1 AS Read, azteca_REQUEST.TEXT2 AS Notes " & _
             "FROM azteca_CUSTOMERCALL INNER JOIN azteca_REQUEST ON azteca_CUSTOMERCALL.REQUESTID = azteca_REQUEST.REQUESTID " & _
             "WHERE azteca_CUSTOMERCALL.DATETIMECALL >= CDate('" & Me.txtFromDate.Text & "') AND (azteca_REQUEST.PROBLEMCODE = 'WATER - GENERAL' OR azteca_REQUEST.PROBLEMCODE = 'WATER - HYDRANT' OR azteca_REQUEST.PROBLEMCODE = 'WATER - MAIN BREAK' OR azteca_REQUEST.PROBLEMCODE = 'WATER - METER MAINT' OR azteca_REQUEST.PROBLEMCODE = 'WATER - TURN OFF' OR azteca_REQUEST.PROBLEMCODE = 'WATER - TURN ON') " & _
             "ORDER BY azteca_CUSTOMERCALL.DATETIMECALL DESC"
    CurrentDb.QueryDefs.Refresh

'Debug.Print qryTemp101

Me.txtFileName.SetFocus
strXLFile = "G:\EVERYONE\Foreclosure Lists\Sent to Janis @ Connexus\Weekly Report\" & Me.txtFileName.Text & ".xlsx"

strQryName = "qryTemp101"

'Debug.Print strXLFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQryName, strXLFile, True
If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
FollowHyperlink strXLFile
End If

Case "WATER - TURN ON"

    Me.txtFromDate.SetFocus
    CurrentDb.CreateQueryDef "qryTemp101", "SELECT azteca_REQUEST.REQUESTID AS Request_ID, azteca_CUSTOMERCALL.DATETIMECALL AS Call_Date_Time, azteca_REQUEST.PROBLEMCODE AS Problem_Code, azteca_REQUEST.PROBADDRESS AS Problem_Address, azteca_CUSTOMERCALL.FIRSTNAME AS Caller_First_Name, " & _
             "azteca_CUSTOMERCALL.LASTNAME AS Caller_Last_Name, azteca_CUSTOMERCALL.HOMEPHONE AS Home_Phone, azteca_CUSTOMERCALL.WORKPHONE AS Work_Phone, azteca_CUSTOMERCALL.OTHERPHONE AS Other_Phone, azteca_CUSTOMERCALL.CUSTADDRESS AS Caller_Address, " & _
             "azteca_CUSTOMERCALL.CUSTCITY AS Caller_City, azteca_CUSTOMERCALL.CUSTZIP AS Caller_Zip, " & _
             "azteca_REQUEST.TEXT1 AS Read, azteca_REQUEST.TEXT2 AS Notes " & _
             "FROM azteca_CUSTOMERCALL INNER JOIN azteca_REQUEST ON azteca_CUSTOMERCALL.REQUESTID = azteca_REQUEST.REQUESTID " & _
             "WHERE azteca_CUSTOMERCALL.DATETIMECALL >= CDate('" & Me.txtFromDate.Text & "') AND (azteca_REQUEST.PROBLEMCODE = 'WATER - TURN ON') " & _
             "ORDER BY azteca_CUSTOMERCALL.DATETIMECALL DESC"
    CurrentDb.QueryDefs.Refresh

'Debug.Print qryTemp101

Me.txtFileName.SetFocus
strXLFile = "G:\EVERYONE\Foreclosure Lists\Sent to Janis @ Connexus\Weekly Report\" & Me.txtFileName.Text & ".xlsx"

strQryName = "qryTemp101"

'Debug.Print strXLFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQryName, strXLFile, True
If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
FollowHyperlink strXLFile
End If

Case "WATER - TURN OFF"

    Me.txtFromDate.SetFocus
    CurrentDb.CreateQueryDef "qryTemp101", "SELECT azteca_REQUEST.REQUESTID AS Request_ID, azteca_CUSTOMERCALL.DATETIMECALL AS Call_Date_Time, azteca_REQUEST.PROBLEMCODE AS Problem_Code, azteca_REQUEST.PROBADDRESS AS Problem_Address, azteca_CUSTOMERCALL.FIRSTNAME AS Caller_First_Name, " & _
             "azteca_CUSTOMERCALL.LASTNAME AS Caller_Last_Name, azteca_CUSTOMERCALL.HOMEPHONE AS Home_Phone, azteca_CUSTOMERCALL.WORKPHONE AS Work_Phone, azteca_CUSTOMERCALL.OTHERPHONE AS Other_Phone, azteca_CUSTOMERCALL.CUSTADDRESS AS Caller_Address, " & _
             "azteca_CUSTOMERCALL.CUSTCITY AS Caller_City, azteca_CUSTOMERCALL.CUSTZIP AS Caller_Zip, " & _
             "azteca_REQUEST.TEXT1 AS Read, azteca_REQUEST.TEXT2 AS Notes " & _
             "FROM azteca_CUSTOMERCALL INNER JOIN azteca_REQUEST ON azteca_CUSTOMERCALL.REQUESTID = azteca_REQUEST.REQUESTID " & _
             "WHERE azteca_CUSTOMERCALL.DATETIMECALL >= CDate('" & Me.txtFromDate.Text & "') AND (azteca_REQUEST.PROBLEMCODE = 'WATER - TURN OFF') " & _
             "ORDER BY azteca_CUSTOMERCALL.DATETIMECALL DESC"
    CurrentDb.QueryDefs.Refresh

'Debug.Print qryTemp101

Me.txtFileName.SetFocus
strXLFile = "G:\EVERYONE\Foreclosure Lists\Sent to Janis @ Connexus\Weekly Report\" & Me.txtFileName.Text & ".xlsx"

strQryName = "qryTemp101"

'Debug.Print strXLFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQryName, strXLFile, True
If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
FollowHyperlink strXLFile
End If

'etc.

End Select

End Sub

Mr. B · Sep 29, 2011

It would seem that the links to your tables (because of the ODBC connection) may not be being connected at startup.

Take a look at this link: http://bytes.com/topic/access/insights/876985-relinking-odbc-tables-using-vba

titobudd · Sep 29, 2011

Mr. B, thanks for the reply and the link. I just have a couple of questions:

1. Since the initial form load sets the rowsource correctly, filling the listbox with the query results, wouldn't that indicate that the table connections are initially working?

2. The other thing that is odd is that the queries used to export the excel table also work, even though the listbox isn't being populated. That would also indicate that the tables are correctly linked from the start but not cooperating when I want to update the rowsource.

Thoughts? Thank you.

Mr. B · Sep 29, 2011

What I find to be interesting is that I am able to get the listbox to populate through the textbox and combobox object changes after I have done the export to excel function or if I open the Linked Table Manager and refresh the linked tables.

I would say that your deductions are correct, but from your original post, I just thought that the link could be the issue.

You may just need to verify that the sql statement that is populating your list box is actually constructed correctly. You can try displaying the value that is in the "sqlDate" variable in the Immediate window and then take the results and copy and paste that into a QBE and see if it works from here to return values. If not, then it is going to be something wrong with the way the sql statement is being constructed.

titobudd · Sep 29, 2011

So I took your advice and pasted one of my queries into query designer in sql view (I think this is what you meant by QBE). Here was the issue:

Code:

sqlDate = "SELECT azteca_REQUEST.REQUESTID AS Request_ID, azteca_CUSTOMERCALL.DATETIMECALL AS Call_Date_Time, azteca_REQUEST.PROBLEMCODE AS Problem_Code, azteca_REQUEST.PROBADDRESS AS Problem_Address, azteca_CUSTOMERCALL.FIRSTNAME AS Caller_First_Name, " & _
          "azteca_CUSTOMERCALL.LASTNAME AS Caller_Last_Name, azteca_CUSTOMERCALL.HOMEPHONE AS Home_Phone, azteca_CUSTOMERCALL.WORKPHONE AS Work_Phone, azteca_CUSTOMERCALL.OTHERPHONE AS Other_Phone, azteca_CUSTOMERCALL.CUSTADDRESS AS Caller_Address, " & _
          "azteca_CUSTOMERCALL.CUSTCITY AS Caller_City, azteca_CUSTOMERCALL.CUSTZIP AS Caller_Zip, " & _
          "azteca_REQUEST.TEXT1 AS [COLOR="Red"]Read[/COLOR], azteca_REQUEST.TEXT2 AS Notes " & _
          "FROM azteca_CUSTOMERCALL INNER JOIN azteca_REQUEST ON azteca_CUSTOMERCALL.REQUESTID = azteca_REQUEST.REQUESTID " & _
          "WHERE azteca_CUSTOMERCALL.DATETIMECALL >= CDate('" & Me.txtFromDate.Text & "') AND (azteca_REQUEST.PROBLEMCODE = 'WATER - GENERAL' OR azteca_REQUEST.PROBLEMCODE = 'WATER - HYDRANT' OR azteca_REQUEST.PROBLEMCODE = 'WATER - MAIN BREAK' OR azteca_REQUEST.PROBLEMCODE = 'WATER - METER MAINT' OR azteca_REQUEST.PROBLEMCODE = 'WATER - TURN OFF' OR azteca_REQUEST.PROBLEMCODE = 'WATER - TURN ON') " & _
          "ORDER BY azteca_CUSTOMERCALL.DATETIMECALL DESC"

"Read" is a SQL Server reserved word, which must have been what was breaking everything. I had "Reading" in place for the initial form load query, which is why that one was working.

As to why everything began to work after refreshing via linked table manager or by exporting to excel, I don't know. But everything seems to be working now. Thank you very much for pointing me in the right direction!

Mr. B · Sep 29, 2011

You are quite welcome. Glad to help.

Creating SQL statements on the fly can be a little tricky sometimes. I use the technique of copying and pasting to the QBE (yes is did mean the query designer) just to make it faster to spot a problem faster than just reading the SQL.

Good luck with your project.

Listbox rowsource issue

titobudd

Registered User.

Mr. B

"Doctor Access"

titobudd

Registered User.

Mr. B

"Doctor Access"

titobudd

Registered User.

Mr. B

"Doctor Access"

Users who are viewing this thread