Querylist in combobox 2

[Namlop]

I want to build a form with a combobox that contains all the names of the queries i've got in my database. But since i've got over 80 queries i don't want to fill the combobox manually. Is there a way by using VBA to fill the combobox?

 

[PaulBricker]

You could try this in the open event for the Form. Change the name of the combo box to your combo name.

Paul

Dim qdf As DAO.QueryDef
Dim strHolder As String
For Each qdf In CurrentDb.QueryDefs
strHolder = strHolder & qdf.Name & "; "
Next
Me.ComboName.RowSourceType = "Value List"
Me.ComboName.RowSource = Left(strHolder,Len(strHolder)-2)

 

[Namlop]

Thanx

 

[Namlop]

Is there a way, in vba, how i can find out what the names of the output fields of a query are?

 

[PaulBricker]

Use the Fields collection to iterate thru the fields in a query or table.

Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
For Each qdf In CurrentDb.QueryDefs
For Each fld In qdf.Fields
Debug.Print fld.Name
Next
Next

Paul

 

[Namlop]

(I'm starting to feel dumb.)

Thanx

 

[PaulBricker]

If you have problems just let folks know. We've all been there.

Paul

 

[jebry]

Hi all!

There is a somewhat easier way to do this. Set the list box, or combo box RowSource Type to Table/Query and use the following query:

Select Name From MSysObjects Where Type = 5 And Name Not Like "~*"

This will bring back a list of query names. For the fields set the RowSource Type to Field List and set the rowsource to the table or query you want the fields listed from. If you want to make it dynamic, the let the user double click on the query they want to see the fields for and use the doubleclick event to populate the other list box's rowsource with the name clicked.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Namlop]

And how do i go to work if i want to do the same with forms instead of queries?

 

[DaltonVB]

Use Jeff Bridgham's code except tables are type 1


Select Name From MSysObjects Where Type = 1 And Name Not Like "~*"

 

[Namlop]

Nevermind, i already found the answer.

 

[Namlop]

DaltonVB, that returns a list of tables, not forms

 

[jebry]

Hi!

You can use a query from MSysObjects like this:

Select Name From MSysObjects Where Type = -32768

Of course setting the RowSourceType to Table/Query

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Namlop]

Jeff, i found a more elaborate function that, i think should do the same as your solution.

But with your solution i end up with a empty combobox, but the function works.

This is the function:

Function ReportsList(Optional strLikeReportName As String = "", Optional strReportList As String = "", Optional intStartPos As Integer = 1, Optional strContainer As String = "Reports&quot As String
On Error GoTo Err_ReportsList
'strLikeReportName - part of report name which is same all needed reports
'strReportList - exist listbox rowsource (text such "rptMyReport";"This is my report" what you want to keep in the new rowsource
'strContainer="Forms" for forms (must be included in called command)
Dim rst As Recordset
Dim strSQL As String
Dim strWhere As String
Dim strDescription As String
Dim intLenght As Integer
Dim prp As Property

intStartPos = Abs(intStartPos)
intLenght = Len(strLikeReportName)

strSQL = "SELECT MSysObjects.Name FROM MSysObjects "
If strContainer = "Reports" Then
strWhere = "WHERE MSysObjects.Type = -32764 "
Else
strWhere = "WHERE MSysObjects.Type = -32768 "
End If

If strLikeReportName <> &quot;&quot; Then
strWhere = strWhere & &quot;And Mid(Name,&quot; & intStartPos & &quot;,&quot; & intLenght & &quot = '&quot; & strLikeReportName & &quot;' &quot;
End If
strSQL = strSQL & strWhere & &quot;ORDER BY MSysObjects.Name;&quot;

Set rst = CurrentDb.OpenRecordset(strSQL)
If Not rst.EOF Then
While Not rst.EOF
'strDescription = CurrentDb.Containers(strContainer).Documents(rst(0)).Properties(&quot;Description&quot.Value
If strReportList <> &quot;&quot; Then
'strReportList = strReportList & &quot;;&quot;
End If
strReportList = strReportList & rst(0) & &quot;;&quot; '& strDescription
rst.MoveNext
Wend
End If
rst.Close
Set rst = Nothing
ReportsList = strReportList

Exit_ReportsList:
Exit Function

Err_ReportsList:
If Err.Number = 3270 Then 'Property not found.
MsgBox &quot;You need to write descriptions of &quot; & LCase(strContainer) & &quot;!&quot;, vbCritical, &quot;Property not found&quot;
ReportsList = &quot;Property not found&quot;
Else
MsgBox &quot;Error No &quot; & Err.Number & vbLf & Err.Description, , &quot;Public Function ReportList&quot;
End If
Resume Exit_ReportsList
End Function


I disabled the description because i don't need it.

I call the function like this:
myList = ReportsList(&quot;frm&quot;, , , &quot;Forms&quot



Why doesn't your solution work?

 

[DaltonVB]

Sorry Namlop,
I have tables on the brain. Jeff pull through again.

 

[jebry]

Hi!

I use this method and it certainly works. Do you get any error message or just a blank box? I just tried it again in another db and got back the names of all of my forms.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Namlop]

No errors no nothing, just a blank box.

But the function allows me to edit the form names before i put them in the combobox. So i think i'll keep working with the function.

Thanx for your help anyway