Listbox won't populate with select query (1 Viewer)

craigachan

Registered User.
Local time
Today, 02:38
Joined
Nov 9, 2007
Messages
282
I have a cmdbutton on Form1 that calls a public function that opens a separate form2 and then populates Form2.listbox. It seems like I've done this before, but this time the code runs but the listbox populates with no data.

Code:
Private Sub cmdChartNote_Click()
    DoCmd.OpenForm "SelectChartNote"
    Forms!SelectChartNote!PID = Me.PID
    Call BuildListOfChartNotes(Me.PID)             'LtrPublic
End Sub
/[Code]

{Code}
Public Function BuildListOfChartNotes(strPID As String)
    On Error GoTo BuildListOfChartNotesErr
    Dim msql As String

    Forms!SelectChartNote!PID = strPID
    With Forms!SelectChartNote!lstNotes
        msql = "SELECT nDate, nNote, nID, nPID, nType FROM ChartNotes WHERE nPID = '" & strPID & "'"
        'MsgBox msql
        .ColumnCount = 5
        .ColumnWidths = "0.6in, 4.5in, 1in, 0.4in, 0.4in"
        .RowSource = msql
    End With
BuildListOfChartNotesExit:
    
    Exit Function
    
BuildListOfChartNotesErr:
    MsgBox "LtrPublic-BuildListOfChartNotes: " & Err.Number & " - " & Err.Description
    Resume BuildListOfChartNotesExit
End Function

/{Code}

The code runs without errors....but not data in the listbox.  any ideas why?

Thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:38
Joined
Oct 29, 2018
Messages
21,357
One thing to check is the RowSourceType. Is it Table/Query?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:38
Joined
May 21, 2018
Messages
8,463
I am just guessing from the name that PID is numeric so you would need to drop the single quotes.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,169
what is the WindowMode of form "SelectChartNote", Modal?
 

craigachan

Registered User.
Local time
Today, 02:38
Joined
Nov 9, 2007
Messages
282
Thanks for all of your quick replies.

Listbox Row Source Type: Table/Query
msql produced the correct variable: SELECT nDate, nNote, nID, nType, nPID FROM ChartNotes WHERE nPID = '080517'
PID and strPID are strings
Forms!SelectChartNote is not yet Modal or Popup.

Using the msql in the actual listbox.rowsource on form2, I can get the listbox to populate with a static variable. I don't now how to get the variable, PID to load earlier than the listbox so that if the select query is in the listbox.rowsource property of the form it will see the variable PID.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:38
Joined
May 21, 2018
Messages
8,463
I do not think it is needed but maybe add
.RowSource = msql
.requery
End With

and for debug purposes to ensure it is a data issue and not a display issue

msgbox Forms!SelectChartNote!lstNotes.recordset.recordcount
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:38
Joined
May 21, 2018
Messages
8,463
Also if you drop this in the query editor
Code:
SELECT nDate, nNote, nID, nType, nPID FROM ChartNotes WHERE nPID = '080517'
does it work?
 

craigachan

Registered User.
Local time
Today, 02:38
Joined
Nov 9, 2007
Messages
282
MsgBox Forms!SelectChartNote!lstNotes.Recordset.RecordCount returns runtime 91 Object variable or With block variable not set

MsgBox Forms!SelectChartNote!lstNotes.Rowsource returns:
SELECT nDate, nNote, nID, nType, nPID FROM ChartNotes WHERE nPID = '30580'
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:38
Joined
Oct 29, 2018
Messages
21,357
MsgBox Forms!SelectChartNote!lstNotes.Recordset.RecordCount returns runtime 91 Object variable or With block variable not set

MsgBox Forms!SelectChartNote!lstNotes.Rowsource returns:
SELECT nDate, nNote, nID, nType, nPID FROM ChartNotes WHERE nPID = '30580'
Maybe try:
Code:
Forms!SelectChartNote.lstNotes.ListCount
 

craigachan

Registered User.
Local time
Today, 02:38
Joined
Nov 9, 2007
Messages
282
placing
SELECT nDate, nNote, nID, nType, nPID FROM ChartNotes WHERE nPID = '30580'

directly into the listbox rowsource property of the form produces 10 records.
 

craigachan

Registered User.
Local time
Today, 02:38
Joined
Nov 9, 2007
Messages
282
Guess what. this msql worked regardless of the variable.

Code:
msql = "SELECT CHANNotes.nDate, CHANNotes.nNote, CHANNotes.nID, CHANNotes.nPID, CHANNotes.nType " & _
            "From CHANNotes " & _
            "WHERE (((CHANNotes.nPID)='" & strPID & "'));"
/[Code]

I'm open to explainations
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:38
Joined
May 21, 2018
Messages
8,463
MsgBox Forms!SelectChartNote!lstNotes.Recordset.RecordCou nt returns runtime 91 Object variable or With block variable not set
Sorry that is actually possible to have returned items, but the recordset is not yet set. So that is not the best test. But if you do what dbguy says, are you returning an item count?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:38
Joined
May 21, 2018
Messages
8,463
Chartnotes vs Channotes?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,169
is ChartNotes and CHANNotes same table?
 

craigachan

Registered User.
Local time
Today, 02:38
Joined
Nov 9, 2007
Messages
282
I think you found the problem. Spelling error. It should have been ChanNotes all along. I'll test it again and see what happens. But I think this is resolved by a silly reference error.

Thanks again for all of your attention to detail.
 

Users who are viewing this thread

Top Bottom