Help With Populate Combo Box? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

[Support4John]

I have a folder F:AppsAccess2kClerkTch of many Snap View Report files .(snp)

I

[HansV]

You could use code like this air code, for example in the On Load or On Open event of the form:
<code>
Dim strPath As String
Dim strFile As String
Dim strRowSource As String
' Path must include trailing backslash
strPath = "F:AppsAccess2kClerkTch"
' Get first file
strFile = Dir(strPath & "*.snp")
' Loop through files
Do While Not strFile = ""
strRowSource = strRowSource & ";" & strFile
strFile = Dir
Loop
If Not strRowSource = "" Then
' Remove first semicolon
strRowSource = Mid(strRowSource, 2)
End If
' Set row source
Me.cboFiles.RowSource = strRowSource
</code>
Replace cboFiles with the name of the combo box.

Notes:
1) The Row Source Type of the combo box must be set to Value List, not to the default Table/Query.
2) There'll be a problem if you have file names containing a semicolon.

[Support4John]

This is great!

Now I

[HansV]

The full path of the .exe and the full path of the snapshot file both contain spaces, so you must enclose each of them in quotes. This can be done by including double double quotes in the quoted string:
<code>
strAppName = """C:Program FilesSnapshot ViewerSNAPVIEW.EXE"" ""F:AppsAccess2kClerkTchRpt-Clerk's Monthly Report.snp"""
</code>
(The above should be one line in the Visual Basic Editor, even if it is wrapped by your browser). It would also be possible to concatenate with Chr(34) to insert the quotes.

[Support4John]

Hi Hans

The following works, what can I do to clean up the double quotes to make it more readable?

Thanks, John

'path to application
strAppName = Chr(34) & "C:Program FilesSnapshot ViewerSNAPVIEW.EXE" & Chr(34)

'initial folder and report name
strFilePath = """" & strInitDir & "" & "" & cboSnapshotFiles & """"
strAppName = strAppName & " " & strFilePath

Call Shell(strAppName, 1)

[HansV]

You can replace <code>""""</code> with Chr(34), and you can omit <code>""</code> since that is merely an empty string. So the code becomes:
<code>
'path to application
strAppName = Chr(34) & "C:Program FilesSnapshot ViewerSNAPVIEW.EXE" & Chr(34)

'initial folder and report name
strFilePath = Chr(34) & strInitDir & "" & cboSnapshotFiles & Chr(34)
strAppName = strAppName & " " & strFilePath

Call Shell(strAppName, 1)</code>

[Support4John]

Perfect!

What limitation am I facing with number of .snp file being stored in strRowSource?

Thanks for your help

John

[HansV]

In my Access 2002 (XP), the total Row Source can be at most 32,767 characters. It was 2,048 characters in Access 97, I'm not sure what the limit is in Access 2000.
If you run into problems, there's another more complicated method to populate a combo box or list box - let me know if you need it.

[Support4John]

I don

[HansV]

You can create a special type of function (a call-back function) with a very specific structure to handle populating a combo box or list box.
You then set the Row Source Type property of the combo box to the name of this function, and leave the Row Source property blank.
See Programming Combo Box and List Box Controls in Microsoft Access, Part 2 for an explanation.
I have attached a small demo database. You can adapt the code for your situation.

[Support4John]

Got it!

In the future if I need a ListSnap and a ListMDB and a ListTxt functions, do I need 3 redundant functions with the exception of *.snp and *.mdb and *.txt, or can I have 1 generic ListFiles function and call it from Combo Box Row Source Type with something like ListFiles(

[HansV]

The format of the function is obligatory, you can't add other arguments. But you can replace "*.snp" in the code with a reference to a global variable or a control on a form. See modified demo - it lets you pick an extension from a combo box.

[Support4John]

Fantastic!

Write once use many

Thanks, John