Populating Listbox control with Query Object

[davesmith202]

I have a listbox control that I want to populate with my list of Queries when the Form loads.

How can I do that?

Thanks,

Dave

 

[vbaInet]

Open up the Property Sheet for the listbox, select the DATA tab and in the ROW SOURCE property select your query.

If you want to do this in code then you simply do:

Listbox1.RowSource = "query name"

 

[davesmith202]

I mean to populate the listbox with my list of Query objects.

e.g.

qryExport1
qryExport2

Do I have to do a loop and read the object names somehow?

 

[vbaInet]

I see, have a look at this (at the bottom of the page):

http://www.btabdevelopment.com/ts/samples

There's an example there but he uses the "qry_" prefix so just change it to "qry" in the query.

You can also use this:

SELECT MsysObjects.Name 
FROM MsysObjects
WHERE (((Left([Name],1))<>"~") AND ((MsysObjects.Type)=5))
ORDER BY MsysObjects.Name;

 

[davesmith202]

Nice code find - sweet as a nut! All working and a big thanks.

 

[vbaInet]

You're welcome.

 

[DonLeverton]

I also followed this, and got WAY too many results.
Since I DO prefix my queries (the ones I want here anyway) with "qry", it was pretty easy to filter the list.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((Left([Name],3))="qry"));

 

[isladogs]

Hi Don

It's a very old thread that you've revived here!

The original code by vbaInet was correct.

Type=5 lists all queries.

The <>'~' excludes all temp objects
This is needed because all form & report items containing a row source (listboxes/comboboxes) are assigned their own record in the MSysObjects table
These are identified as TEMP queries (type = 5 ; flag = 3) and the object name will look similar to this:
~sq_c#frmMessageLog~sq_ccboPhone
~sq_cfrmMessageLog~sq_ccboPhone
~sq_cfrmSendEmail~sq_cLstContactEmail
~sq_ffrmPStats3
~sq_rrptPRecordTeacherCrosstab

If you omitted the <>'~' section you will have got lots of additional records for all these temporary 'queries'

Your modification will have excluded those but also excluded all queries not starting with 'qry'

You may find this post explaining the MSysObjects table of interest:
https://www.access-programmers.co.uk/forums/showthread.php?t=293579

 

[DonLeverton]

Hi Colin,
I "skimmed" the text of that article in the link, and may adopt that policy of deleting temporary objects.
=====================================
My main purpose, and the reason for finding this old thread is that I am trying to recreate the functionality of an old utility called SQL2VAR.EXE that I used in the Access 2.0/97 days.

I had found a newer utility included with IDBE tools, that did this too ... but have discovered that it no longer works with Access 2016.

I combined a couple of ideas and code snippets to design a form containing a list-box that is populated by the code above. Allen Brown had instructions for creating a tab control with 2 text-boxes. One for the SQL string which he was copy/pasting from the SQL view of a query, and the other tab/textbox would contain another textbox to contain the generated VBA string. Again, this was intended to be copy/pasted into the code window. Ugh.

So I worked on my idea of combining the two, and came up with this: (Still far from the functionality of the good old SQL2VAR utility.)

==== Code ==========
Private Sub cmdGenereateSQL_Click()

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strQuery As String
Dim strSQL As String

Set frm = Me
Set ctl = frm!lbxQueries
For Each varItm In ctl.ItemsSelected
strQuery = ctl.ItemData(varItm)
'Debug.Print strQuery
Next varItm

Me.txtSQL = CurrentDb.QueryDefs(strQuery).SQL

'Now run Allen Browne's code.

'Purpose: Convert a SQL statement into a string to paste into VBA code.
Const strcLineEnd = " "" & vbCrLf & _" & vbCrLf & """"

If IsNull(Me.txtSQL) Then
Beep
Else
strSQL = Me.txtSQL
strSQL = Replace(strSQL, """", """""") 'Double up any quotes.
strSQL = Replace(strSQL, vbCrLf, strcLineEnd)
strSQL = "strSql = """ & strSQL & """"
Me.txtVBA = strSQL
Me.txtVBA.SetFocus
RunCommand acCmdCopy
End If

End Sub
'=========end code =============
The VBA generated is not NEAR as neat, and I haven't yet tested it.
BTW, my "post count" is still below the threshold of 10, so I was not able to post links or use code tags.
(This policy seems unnecessary to me, but hey ...)

 

[isladogs]

Hi Don

I also skimmed your code ....!
I never used SQL2VAR so I don't know what it did

I do have IDBE Tools but am unclear which part of it you meant.
I've just tested and the 'Clear Immediate Window' button still works in A2016- haven't tried the rest of it

I combined a couple of ideas and code snippets to design a form containing a list-box that is populated by the code above. Allen Brown had instructions for creating a tab control with 2 text-boxes. One for the SQL string which he was copy/pasting from the SQL view of a query, and the other tab/textbox would contain another textbox to contain the generated VBA string. Again, this was intended to be copy/pasted into the code window. Ugh.

Strange - most people like that utility.
Try my version which significantly extends Allen Browne's code so it works in both directions.
SQL to VBA and back again

If you also think that's 'ugh' then I'm not offended

 

[DonLeverton]

SQL2VAR and IDBE tools "Query to VBA String Builder" both created Strings that look like this:
'append new data
' Created by IDBE Tools 2010
' SQL Text from Query: "qryAppendPOMSTDT"

MySQL = "INSERT INTO POMSTDT ( PoNumber ) "
MySQL = MySQL & "SELECT BLMYPRDDTA_POMSTDT.*, [POPRFX] & Format([PONMBR],'0000') "
MySQL = MySQL & "AS PoNumber "
MySQL = MySQL & "FROM BLMYPRDDTA_POMSTDT "
MySQL = MySQL & "WHERE (((BLMYPRDDTA_POMSTDT.POPRFX)='OP')) "
MySQL = MySQL & "ORDER BY [POPRFX] & Format([PONMBR],'0000'); "

MyDB.Execute MySQL, dbFailOnError

 

[isladogs]

Do have a look at my utility which does much the same ... and more.

The only significant difference is that, as written, mine doesn't use the repeated MySQL notation on each line.
In my view that makes code less readable but its a personal choice.