none
Trying to find code for Row Source of ComboBox Using Query of table in Access. NOT for MAIL MERGE or Bookmark fields. RRS feed

  • Question

  • I have hope there is a way to load query result of a query based on a table in Access.

    I am NOT creating a "Form Letter" with mail merge fields, or bookmarks in the document.

     

    I thought in Word 2013 I might use MyAccessObject = CreateObject(strPathToAccessFile)

    Then dim various objects part of Access App Object model to get info into Word in way I can select results of qry in a combo or list box. Without showing Access starting.  Wasn't able to put path to Access file in the RowSource of a cboBox in a Word's UserForm.

    I was able to do that in one Access file, having it call a public function, or qry in another Access file.  Some times I had to use a wrapper for Access item I wanted to use. 

    Worse case, and NOT that bad, I will have to just use Word's QAT to open Access file, then, enter parameters,  run a qry or report get values I want, then Just Copy and Paste into the spot needed in Word's ActiveDocument.

    Next NOT as worse case as above, would be when I am at point that I want to check each word in the Word doc for words on a "Bad Words" list in Access, and if that word is found on that list, then stop on that word,

    then a CBO shows up with approved alternates.  And User picks one from cbo, then new word is inserted in Word doc.

    I would close Word 2013 with current doc.

    Then start Access, use CreateObject(pathToWordDocToFix) add objects to refer to ActiveDocument, ActiveDocument.Range  and so on.   

    Then I would use Access based comboBox, filled with words based on table GoodWords, table BadWords and current "word" in document needing to be replaced. 

    Bye the way, I did figure out how to walk thru word document, one word at a time, pass that word to code to test if word needing to be changed. 

     So only answer I need is:  Is it possible to fill a combo box in Word 2013 with values based on a table in Access.

     In Word, I have been using code that refers to a table in a word document, to fill a combo box in a user form in that document.  the code formats to match the number of columns and rows in the table.  

    Only reason I am not using a table in a word document, is at the current time, I would have a table of 8 columns and 174 rows.  And number of rows will increase, and a very good chance number of columns might increase, soon.

    In access, it is much easier to do different constraints in qry to reduce results returned.

    So good news for me this time, I have working option even if I can't fill a cboBox in Word with data in Access File.

    And if I insist on using code, I appear to be able to open a word doc in Access, and do all I need to the doc, search for "Bad Word" and "Replace it". 

    Just having to work on doc first in word then access then back to word, and repeat, seems a pain, if not a very big pain.

    Thanks,

    Mark


    Mark J


    Sunday, September 27, 2015 9:33 AM

Answers

  • Hi Mark,

    If you want to fill the combobox in userform with value in access database, I think you could try the code below.

    Private Sub UserForm_Initialize()
        Dim dbs As DAO.Database
        Dim rsTable As DAO.Recordset
        Dim rsQuery As DAO.Recordset
        Dim coll As New Collection
        'open database
        Set dbs = OpenDatabase("D:\OfficeDev\Access\Database1.accdb")
    
        'Open a table-type Recordset
        Set rsTable = dbs.OpenRecordset("Test", dbOpenTable) ‘ Test is table name
        Do While Not rsTable.EOF
            coll.Add rsTable.Fields("Name").Value ‘Name is field name
        rsTable.MoveNext
        Loop
        ComboBox1.List = toArray(coll)
    End Sub
    Function toArray(col As Collection)
      Dim arr() As Variant
      ReDim arr(1 To col.Count) As Variant
      For i = 1 To col.Count
          arr(i) = col(i)
      Next
      toArray = arr
    End Function

    In addition, you use the code above, you need to add Microsoft Office x.0 Access database engine Object reference.

    Best Regards,

    Edward

       

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by PuzzledByWord Saturday, October 3, 2015 3:47 AM
    Monday, September 28, 2015 8:12 AM
  • Edward,

    thanks for a fast response.

    I haven't used Collections very much, but I seem to understand what code is doing.

    First question is : should I do bit like below?

    Dim XLApp As Excel.Application
        Dim XLBook As Excel.Workbook
        Dim XLSheet As Excel.Worksheet
        Set XLApp = CreateObject("Excel.Application")
        XLApp.Workbooks.Open strFilePath
        XLApp.Application.Visible = True

    or did I misunderstand the line below.

    "In addition, you use the code above, you need to add Microsoft Office x.0 Access database engine Object reference."

    I thought it meant that I should under Tools/ References  click on check box, Microsoft Access 15.0 object library,

    But didn't take "DAO." until I also clicked on Microsoft DAO 3.6 object library.

    Then "DAO." was recognized.

    But I kept getting Run-Time Error of 3343, Unrecognized format.

    I also noticed something strange, occurring, when after I compiled code, I saved code , then ran code UserForm_Initialize  my path was changed from \Documents\My Documents\Test.accdb to  \Documents\Test.accdb   "My Documents" would disappear from path.

    But in Windows Explorer showing as Computer    OS(C:)   Users    Mark     My Document

    But when I open Access File   Test.accdb  and click on File then Info section click on database properties then general then Location is shown as C:\Users\Mark\Documents     for  Test.accdb Properties .

    when the path is NOT changed, I get error msg NOT VAILD path.  But the path shows in Windows Explorer.

    any idea of anything here I might be missing? Any about any of these strange items.

    Thanks,

    Mark


    Mark J

    • Marked as answer by PuzzledByWord Saturday, October 3, 2015 3:47 AM
    Tuesday, September 29, 2015 9:16 AM
  • Hi Mark,

    >> should I do bit like below?
    No, why do you use this code? Collections is used to store values in tables.

    >> thought it meant that I should under Tools/ References  click on check box, Microsoft Access 15.0 object library, But didn't take "DAO." until I also clicked on Microsoft DAO 3.6 object library.

    No, you need to uncheck the Microsoft DAO 3.6 object library, and add Microsoft Office 15.0 Access database engine Object reference. Microsoft Access 15.0 object library and Microsoft Office 15.0 Access database engine Object reference are different. If you use Microsoft DAO 3.6 object library, when you open .accdb file, you will get this error “Unrecognized format”. You need to use Microsoft Office 15.0 Access database engine Object reference.

    >> when the path is NOT changed, I get error msg NOT VAILD path

    This is strange. I suggest you use “debug.print” to output the file path and check whether it exist. I suggest you share us simple code to reproduce this issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by PuzzledByWord Saturday, October 3, 2015 3:48 AM
    Wednesday, September 30, 2015 5:52 AM

All replies

  • Hi Mark,

    If you want to fill the combobox in userform with value in access database, I think you could try the code below.

    Private Sub UserForm_Initialize()
        Dim dbs As DAO.Database
        Dim rsTable As DAO.Recordset
        Dim rsQuery As DAO.Recordset
        Dim coll As New Collection
        'open database
        Set dbs = OpenDatabase("D:\OfficeDev\Access\Database1.accdb")
    
        'Open a table-type Recordset
        Set rsTable = dbs.OpenRecordset("Test", dbOpenTable) ‘ Test is table name
        Do While Not rsTable.EOF
            coll.Add rsTable.Fields("Name").Value ‘Name is field name
        rsTable.MoveNext
        Loop
        ComboBox1.List = toArray(coll)
    End Sub
    Function toArray(col As Collection)
      Dim arr() As Variant
      ReDim arr(1 To col.Count) As Variant
      For i = 1 To col.Count
          arr(i) = col(i)
      Next
      toArray = arr
    End Function

    In addition, you use the code above, you need to add Microsoft Office x.0 Access database engine Object reference.

    Best Regards,

    Edward

       

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by PuzzledByWord Saturday, October 3, 2015 3:47 AM
    Monday, September 28, 2015 8:12 AM
  • Edward,

    thanks for a fast response.

    I haven't used Collections very much, but I seem to understand what code is doing.

    First question is : should I do bit like below?

    Dim XLApp As Excel.Application
        Dim XLBook As Excel.Workbook
        Dim XLSheet As Excel.Worksheet
        Set XLApp = CreateObject("Excel.Application")
        XLApp.Workbooks.Open strFilePath
        XLApp.Application.Visible = True

    or did I misunderstand the line below.

    "In addition, you use the code above, you need to add Microsoft Office x.0 Access database engine Object reference."

    I thought it meant that I should under Tools/ References  click on check box, Microsoft Access 15.0 object library,

    But didn't take "DAO." until I also clicked on Microsoft DAO 3.6 object library.

    Then "DAO." was recognized.

    But I kept getting Run-Time Error of 3343, Unrecognized format.

    I also noticed something strange, occurring, when after I compiled code, I saved code , then ran code UserForm_Initialize  my path was changed from \Documents\My Documents\Test.accdb to  \Documents\Test.accdb   "My Documents" would disappear from path.

    But in Windows Explorer showing as Computer    OS(C:)   Users    Mark     My Document

    But when I open Access File   Test.accdb  and click on File then Info section click on database properties then general then Location is shown as C:\Users\Mark\Documents     for  Test.accdb Properties .

    when the path is NOT changed, I get error msg NOT VAILD path.  But the path shows in Windows Explorer.

    any idea of anything here I might be missing? Any about any of these strange items.

    Thanks,

    Mark


    Mark J

    • Marked as answer by PuzzledByWord Saturday, October 3, 2015 3:47 AM
    Tuesday, September 29, 2015 9:16 AM
  • Hi Mark,

    >> should I do bit like below?
    No, why do you use this code? Collections is used to store values in tables.

    >> thought it meant that I should under Tools/ References  click on check box, Microsoft Access 15.0 object library, But didn't take "DAO." until I also clicked on Microsoft DAO 3.6 object library.

    No, you need to uncheck the Microsoft DAO 3.6 object library, and add Microsoft Office 15.0 Access database engine Object reference. Microsoft Access 15.0 object library and Microsoft Office 15.0 Access database engine Object reference are different. If you use Microsoft DAO 3.6 object library, when you open .accdb file, you will get this error “Unrecognized format”. You need to use Microsoft Office 15.0 Access database engine Object reference.

    >> when the path is NOT changed, I get error msg NOT VAILD path

    This is strange. I suggest you use “debug.print” to output the file path and check whether it exist. I suggest you share us simple code to reproduce this issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by PuzzledByWord Saturday, October 3, 2015 3:48 AM
    Wednesday, September 30, 2015 5:52 AM