none
Use a combobox to list records in an access database RRS feed

  • Question

  • Hi,

    I am trying to create a Word document that contains fields to display data from an Access table. I used the mail merge function.

    I want to add a combo box in the Word document that will hold the Invoice numbers generated in the Access database. The user then clicks on the invoice number in the combo box they want to print and the selected number with its related data must be displayed in the document.

    Currently, the mail merge document retrieves all the records (about 16 000 of them, and growing). The user has to step through them until the relevant data is displayed. This is time consuming.

    This is the very first time that I need to do it this way. My knowledge on Word is very limited. Is there some example available or can somebody direct me in the right direction?

    Thanks

    Thursday, January 8, 2015 8:18 AM

Answers

  • Hi Deon,

    You can use VBA code to populate a combobox, and show the needed record information in the document when the user select a combobox item. To connect to an Access database, ADO will help. Check this complete sample(you can paste this code snippet into the ThisDocument VBA editor to test):

    'Before open this document,
    'create an access db named test.accdb, and create a table UserInfo(ID,UserName,Age)
    
    Private Sub Document_Open()
        PopulateComboBox
    End Sub
    
    Private Sub ComboBox21_Change()
        ShowSelectedRecord ComboBox21.Text
    End Sub
    
    Sub PopulateComboBox()
        Dim dbConnectStr As String
        Dim conn As ADODB.Connection
        Dim sqlQuery As String
        Dim sqlCount As String
        Dim rsQuery As ADODB.Recordset
        Dim rsCount As ADODB.Recordset
        Dim userNames() As String
    
        dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.accdb;Persist Security Info=False;"
    
        sqlQuery = "Select UserName FROM UserInfo"
        sqlCount = "select count(UserName) as UsersCount from UserInfo"
    
        Set conn = New ADODB.Connection
    
        conn.ConnectionString = dbConnectStr
        conn.Open
    
        Set rsQuery = conn.Execute(sqlQuery)
        Set rsCount = conn.Execute(sqlCount)
        
        rsCount.MoveFirst
        i = rsCount!UsersCount
        
        ReDim userNames(1 To i)
        
        j = 1
        If Not (rsQuery.EOF And rsQuery.BOF) Then
            rsQuery.MoveFirst
            Do Until rsQuery.EOF = True
                
                UserName = rsQuery!UserName
                userNames(j) = UserName
                
                j = j + 1
                rsQuery.MoveNext
            Loop
        Else
            'do nothing
        End If
    
        rsQuery.Close
        rsCount.Close
        Set rsQuery = Nothing
        Set rsCount = Nothing
        
        
        ComboBox21.List = userNames
    End Sub
    
    Public Sub ShowSelectedRecord(SelectedUserName As String)
        Dim dbConnectStr As String
        Dim conn As ADODB.Connection
        Dim sqlQuery As String
        Dim rsQuery As ADODB.Recordset
    
        dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.accdb;Persist Security Info=False;"
    
        sqlQuery = "Select * FROM UserInfo where UserName='" & SelectedUserName & "'"
    
        Set conn = New ADODB.Connection
    
        conn.ConnectionString = dbConnectStr
        conn.Open
    
        Set rsQuery = conn.Execute(sqlQuery)
    
        If Not (rsQuery.EOF And rsQuery.BOF) Then
            rsQuery.MoveFirst
            Do Until rsQuery.EOF = True
                
                UserName = rsQuery!UserName
                Age = rsQuery!Age
    
                Application.ActiveDocument.Range(1).InsertAfter "UserName:" & UserName & " Age:" & Age
    
                rsQuery.MoveNext
            Loop
        Else
            'do nothing
        End If
    
        rsQuery.Close
        Set rsQuery = Nothing
    End Sub
    
    

    Besides VBA, you could also create a Word Add-in to connect to Access database and retrieve some data.


    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 Deon SA Monday, January 12, 2015 9:31 AM
    Friday, January 9, 2015 9:33 AM
    Moderator

All replies

  • Hi Deon,

    You can use VBA code to populate a combobox, and show the needed record information in the document when the user select a combobox item. To connect to an Access database, ADO will help. Check this complete sample(you can paste this code snippet into the ThisDocument VBA editor to test):

    'Before open this document,
    'create an access db named test.accdb, and create a table UserInfo(ID,UserName,Age)
    
    Private Sub Document_Open()
        PopulateComboBox
    End Sub
    
    Private Sub ComboBox21_Change()
        ShowSelectedRecord ComboBox21.Text
    End Sub
    
    Sub PopulateComboBox()
        Dim dbConnectStr As String
        Dim conn As ADODB.Connection
        Dim sqlQuery As String
        Dim sqlCount As String
        Dim rsQuery As ADODB.Recordset
        Dim rsCount As ADODB.Recordset
        Dim userNames() As String
    
        dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.accdb;Persist Security Info=False;"
    
        sqlQuery = "Select UserName FROM UserInfo"
        sqlCount = "select count(UserName) as UsersCount from UserInfo"
    
        Set conn = New ADODB.Connection
    
        conn.ConnectionString = dbConnectStr
        conn.Open
    
        Set rsQuery = conn.Execute(sqlQuery)
        Set rsCount = conn.Execute(sqlCount)
        
        rsCount.MoveFirst
        i = rsCount!UsersCount
        
        ReDim userNames(1 To i)
        
        j = 1
        If Not (rsQuery.EOF And rsQuery.BOF) Then
            rsQuery.MoveFirst
            Do Until rsQuery.EOF = True
                
                UserName = rsQuery!UserName
                userNames(j) = UserName
                
                j = j + 1
                rsQuery.MoveNext
            Loop
        Else
            'do nothing
        End If
    
        rsQuery.Close
        rsCount.Close
        Set rsQuery = Nothing
        Set rsCount = Nothing
        
        
        ComboBox21.List = userNames
    End Sub
    
    Public Sub ShowSelectedRecord(SelectedUserName As String)
        Dim dbConnectStr As String
        Dim conn As ADODB.Connection
        Dim sqlQuery As String
        Dim rsQuery As ADODB.Recordset
    
        dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.accdb;Persist Security Info=False;"
    
        sqlQuery = "Select * FROM UserInfo where UserName='" & SelectedUserName & "'"
    
        Set conn = New ADODB.Connection
    
        conn.ConnectionString = dbConnectStr
        conn.Open
    
        Set rsQuery = conn.Execute(sqlQuery)
    
        If Not (rsQuery.EOF And rsQuery.BOF) Then
            rsQuery.MoveFirst
            Do Until rsQuery.EOF = True
                
                UserName = rsQuery!UserName
                Age = rsQuery!Age
    
                Application.ActiveDocument.Range(1).InsertAfter "UserName:" & UserName & " Age:" & Age
    
                rsQuery.MoveNext
            Loop
        Else
            'do nothing
        End If
    
        rsQuery.Close
        Set rsQuery = Nothing
    End Sub
    
    

    Besides VBA, you could also create a Word Add-in to connect to Access database and retrieve some data.


    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 Deon SA Monday, January 12, 2015 9:31 AM
    Friday, January 9, 2015 9:33 AM
    Moderator
  • Hi,

    Thanks for the response.

    As I mentioned in my original message, it is the first time that I need to do it this way.

    When I run your code, I get the following message: "Compile error. User-define type not defined"

    This message appears on the line "Dim conn As ADODB.Connection".

    I suspect that I need to add a Reference, but not sure which one. Can you assist with this?

    Thanks

    Friday, January 9, 2015 9:55 AM
  • The references you need to add are Microsoft ActiveX Data Objects X.X Library and Microsoft ActiveX Data Objects Recordset X.X Library.

    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.


    Tuesday, January 13, 2015 5:34 AM
    Moderator