Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-19-2019, 08:51 AM   #1
craigachan
Newly Registered User
 
Join Date: Nov 2007
Posts: 253
Thanks: 9
Thanked 0 Times in 0 Posts
craigachan is on a distinguished road
Listbox won't populate with select query

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.

craigachan is offline   Reply With Quote
Old 09-19-2019, 08:58 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,946
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Listbox won't populate with select query

One thing to check is the RowSourceType. Is it Table/Query?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-19-2019, 08:59 AM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,312
Thanks: 13
Thanked 4,114 Times in 4,046 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Listbox won't populate with select query

Use this on the SQL variable and see if it has what it should:

http://www.baldyweb.com/ImmediateWindow.htm

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-19-2019, 09:24 AM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,856
Thanks: 36
Thanked 565 Times in 533 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Listbox won't populate with select query

I am just guessing from the name that PID is numeric so you would need to drop the single quotes.
MajP is offline   Reply With Quote
Old 09-19-2019, 09:42 AM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Listbox won't populate with select query

what is the WindowMode of form "SelectChartNote", Modal?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-19-2019, 09:44 AM   #6
craigachan
Newly Registered User
 
Join Date: Nov 2007
Posts: 253
Thanks: 9
Thanked 0 Times in 0 Posts
craigachan is on a distinguished road
Re: Listbox won't populate with select query

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.
craigachan is offline   Reply With Quote
Old 09-19-2019, 09:53 AM   #7
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,856
Thanks: 36
Thanked 565 Times in 533 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Listbox won't populate with select query

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.recordcou nt

MajP is offline   Reply With Quote
Old 09-19-2019, 09:55 AM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,856
Thanks: 36
Thanked 565 Times in 533 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Listbox won't populate with select query

Also if you drop this in the query editor
Code:
SELECT nDate, nNote, nID, nType, nPID FROM ChartNotes WHERE nPID = '080517'
does it work?
MajP is offline   Reply With Quote
Old 09-19-2019, 10:03 AM   #9
craigachan
Newly Registered User
 
Join Date: Nov 2007
Posts: 253
Thanks: 9
Thanked 0 Times in 0 Posts
craigachan is on a distinguished road
Re: Listbox won't populate with select query

MsgBox Forms!SelectChartNote!lstNotes.Recordset.RecordCou nt 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'
craigachan is offline   Reply With Quote
Old 09-19-2019, 10:07 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,946
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Listbox won't populate with select query

Quote:
Originally Posted by craigachan View Post
MsgBox Forms!SelectChartNote!lstNotes.Recordset.RecordCou nt 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
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-19-2019, 10:10 AM   #11
craigachan
Newly Registered User
 
Join Date: Nov 2007
Posts: 253
Thanks: 9
Thanked 0 Times in 0 Posts
craigachan is on a distinguished road
Re: Listbox won't populate with select query

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 is offline   Reply With Quote
Old 09-19-2019, 10:15 AM   #12
craigachan
Newly Registered User
 
Join Date: Nov 2007
Posts: 253
Thanks: 9
Thanked 0 Times in 0 Posts
craigachan is on a distinguished road
Re: Listbox won't populate with select query

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
craigachan is offline   Reply With Quote
Old 09-19-2019, 10:17 AM   #13
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,856
Thanks: 36
Thanked 565 Times in 533 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Listbox won't populate with select query

Quote:
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 is offline   Reply With Quote
Old 09-19-2019, 10:21 AM   #14
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,856
Thanks: 36
Thanked 565 Times in 533 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Listbox won't populate with select query

Chartnotes vs Channotes?
MajP is offline   Reply With Quote
Old 09-19-2019, 10:26 AM   #15
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Listbox won't populate with select query

is ChartNotes and CHANNotes same table?

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Populate listbox with sql query shabbaranks Modules & VBA 6 12-13-2016 08:37 PM
Run a query with value from cbobox to populate a listbox MikeLeBen Forms 2 03-10-2011 04:03 AM
Trying to populate a listbox from a query mcarlin Queries 2 08-22-2005 05:31 AM
listbox populate from sql query mlh407 Modules & VBA 2 07-05-2005 09:44 AM
[SOLVED] Using a Multi-select listbox to populate a DB field mackdaddy Forms 1 07-10-2000 02:48 PM




All times are GMT -8. The time now is 06:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World