Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    This is great!

    Now I

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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>

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Perfect!

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

    Thanks for your help

    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    I don

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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(

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Fantastic!

    Write once use many

    Thanks, John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •