Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-07-2017, 09:18 AM
ashg75 ashg75 is offline Word Template with a drop down list to populate other fields Windows 7 64bit Word Template with a drop down list to populate other fields Office 2013
Novice
Word Template with a drop down list to populate other fields
 
Join Date: Jun 2017
Posts: 11
ashg75 is on a distinguished road
Default Word Template with a drop down list to populate other fields


Hi everyone,

Your help is truly needed. I have provided a sample Word template attached of what I need to be better explain it. I have a project that I have to complete a drop down list with over 400 entries to choose from. I want to be able to choose a provider from the drop down list and it populate the provider's address, clients name, clients date of birth, clients address, clients prefix, and group number. The thing is I have over 400 different clients. How can this be done? Thanks in advance.
Attached Files
File Type: docx Provider Template Test.docx (22.7 KB, 23 views)
Reply With Quote
  #2  
Old 07-07-2017, 10:16 AM
gmaxey gmaxey is offline Word Template with a drop down list to populate other fields Windows 7 32bit Word Template with a drop down list to populate other fields Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,361
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

You are going have to roll up your sleeves and write an awful lot of code or tie your content control dropdown list to a data base.

I really don't see how "Clients Name" is associated with a selected provider but if you have a Excel files "Providers.xlsx" in the same folder as your template then something like this:

Code:
Option Explicit
Dim arrData As Variant
Private Sub Document_ContentControlOnEnter(ByVal oCC As ContentControl)
Dim lngIndex As Long
Dim strSQL As String
  Select Case oCC.Title
    Case "Provider"
      oCC.DropdownListEntries.Clear
      strSQL = "SELECT * FROM [Sheet1$];"
      xlFillList arrData, ThisDocument.Path & "\Providers.xlsx", "True", strSQL
      For lngIndex = 0 To UBound(arrData, 2)
        oCC.DropdownListEntries.Add arrData(0, lngIndex), arrData(0, lngIndex)
      Next lngIndex
  End Select
End Sub
Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
Dim lngIndex As Long
  Select Case oCC.Title
    Case "Provider"
      If Not oCC.ShowingPlaceholderText Then
        ActiveDocument.SelectContentControlsByTitle("Provider Address").Item(1).Range.Text = arrData(1, fcnDropDownIndex(oCC))
        ActiveDocument.SelectContentControlsByTitle("Client Name").Item(1).Range.Text = arrData(2, fcnDropDownIndex(oCC))
      Else
        ActiveDocument.SelectContentControlsByTitle("Provider Address").Item(1).Range.Text = vbNullString
        ActiveDocument.SelectContentControlsByTitle("Client Name").Item(1).Range.Text = vbNullString
      End If
  End Select
End Sub
Function fcnDropDownIndex(CC As ContentControl) As Long
Dim lngIndex As Long
  For lngIndex = 1 To CC.DropdownListEntries.Count
    If CC.DropdownListEntries.Item(lngIndex).Text = CC.Range.Text Then
      fcnDropDownIndex = lngIndex - 1
      Exit For
    End If
  Next
lbl_Exit:
  Exit Function
End Function
Public Function xlFillList(arrPassed As Variant, strWorkbook As String, _
                           bSuppressHeader As Boolean, strSQL As String)
Dim oConn As Object
Dim oRS As Object
Dim lngNumRecs As Long
Dim strConnection As String
  'Create connection:
  Set oConn = CreateObject("ADODB.Connection")
  If bSuppressHeader Then
   strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strWorkbook & ";" & _
                    "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  Else
   strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strWorkbook & ";" & _
                    "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
  End If
  oConn.Open ConnectionString:=strConnection
  Set oRS = CreateObject("ADODB.Recordset")
  'Read the data from the worksheet.
  oRS.Open strSQL, oConn, 3, 1 '3: adOpenStatic, 1: adLockReadOnly
  With oRS
    'Find the last record.
    .MoveLast
    'Get count.
    lngNumRecs = .RecordCount
    'Return to the start.
    .MoveFirst
  End With
  arrPassed = oRS.GetRows(lngNumRecs)
  'Cleanup
  If oRS.State = 1 Then oRS.Close
  Set oRS = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Function
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 07-07-2017, 12:08 PM
ashg75 ashg75 is offline Word Template with a drop down list to populate other fields Windows 7 64bit Word Template with a drop down list to populate other fields Office 2013
Novice
Word Template with a drop down list to populate other fields
 
Join Date: Jun 2017
Posts: 11
ashg75 is on a distinguished road
Default @gmaxey Word Template with a drop down list to populate other fields

@gmaxey thanks for your response. I do have an Excel file that I will be using as the source file. I have attached a sample of that file to this post and it is what I had initially planned to use to execute this project. Will this work?
Attached Files
File Type: xlsx Providers.xlsx (12.7 KB, 14 views)
Reply With Quote
  #4  
Old 07-07-2017, 06:04 PM
gmaxey gmaxey is offline Word Template with a drop down list to populate other fields Windows 7 32bit Word Template with a drop down list to populate other fields Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,361
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

What is the relationship between "Provider" and the client. If you Choose "Dell" from your list, what client to you want associated with it?
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #5  
Old 07-09-2017, 09:06 PM
ashg75 ashg75 is offline Word Template with a drop down list to populate other fields Windows 7 64bit Word Template with a drop down list to populate other fields Office 2013
Novice
Word Template with a drop down list to populate other fields
 
Join Date: Jun 2017
Posts: 11
ashg75 is on a distinguished road
Default

Quote:
Originally Posted by gmaxey View Post
What is the relationship between "Provider" and the client. If you Choose "Dell" from your list, what client to you want associated with it?
@gmaxey It's supposed to be the other way around if I choose the Client Name then I would like for the Provider (Dell, HP, or Microsoft) info along with clients info to populate in the appropriate fields. For example, in the template is states "Guidelines for submitting information for clients from Choose an item." Therefore, if I choose the Client Name Adair County (MO) from the drop down then I would like for Dell (Provider), Provider address, Client Prefix, Client Group, and the Client Addresses to populate once I choose the Client Name from the drop down list.
Reply With Quote
  #6  
Old 07-10-2017, 01:17 AM
gmayor's Avatar
gmayor gmayor is offline Word Template with a drop down list to populate other fields Windows 10 Word Template with a drop down list to populate other fields Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,096
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

I am as confused as my old friend Greg. Your workbook has client names and data associated with the provider, but your document is client oriented and the information relating to the client is not present in the workbook, and the fields relating to the provider are not present in the document.

It is easy enough to grab the information associated with a particular record and apply it to the document, but if you are going to do it with content controls, then you need to have the content controls titled and/or tagged and the information you want in them must be available from the worksheet record.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #7  
Old 07-10-2017, 04:19 PM
ashg75 ashg75 is offline Word Template with a drop down list to populate other fields Windows 7 64bit Word Template with a drop down list to populate other fields Office 2013
Novice
Word Template with a drop down list to populate other fields
 
Join Date: Jun 2017
Posts: 11
ashg75 is on a distinguished road
Default @gmaxey @gmayor Word Template with a drop down list to populate other fields

Correction: I have added the correct/revised Word Template of what I am needing help with. I have highlighted in yellow the entry that I would like to have a drop down list for and once that entry is selected from the drop down the other fields will populate (I have bolded as shown below). This isn't the actual information but I just wanted to give a general idea of what the format of the template will be. I will be using over 400 client names to choose from along with its corresponding provider & client info and I am not sure if it would be best to use VBA and have the excel file as my source. Please help. Thanks again for responding.


- Guidelines for submitting information for your client Adair County (MO) and Provider Dell.
Please complete applicable fields on the claim
All information should be submitted for payment to the provider address: P.O. BOX 1234 MISSOULA, MONTANA 59806-1234
Item Name: Click here to enter text. Item Control#: Click here to enter text.
Client Address: 123 Bradshaw St. Louis, MO 12345
Client Prefix: ARK
Client Group Number: 790123
Attached Files
File Type: docx Provider Template Test.docx (23.2 KB, 13 views)
File Type: xlsx Providers.xlsx (12.8 KB, 11 views)
Reply With Quote
  #8  
Old 07-10-2017, 05:54 PM
gmaxey gmaxey is offline Word Template with a drop down list to populate other fields Windows 7 32bit Word Template with a drop down list to populate other fields Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,361
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

You have already been given the solution to this requirement practically on a silver platter. If you are then going to move the goal post after the start of the game at least take the time to read and try to apply it.

Basically you need to change your OnEntry procedure as such:

Private Sub Document_ContentControlOnEnter(ByVal oCC As ContentControl)
Dim lngIndex As Long
Dim strSQL As String
Select Case oCC.Title
Case "Client"
oCC.DropdownListEntries.Clear
strSQL = "SELECT * FROM [Sheet1$];"
xlFillList arrData, ThisDocument.Path & "\Providers.xlsx", "True", strSQL
For lngIndex = 0 To UBound(arrData, 2)
'Since you have a gazillion empty records in your excel file:
If Not IsNull(arrData(0, lngIndex) Then
oCC.DropdownListEntries.Add arrData(0, lngIndex), arrData(0, lngIndex)
End If
Next lngIndex
End Select
End Sub

The rest of the code would be edited accordingly (e.g., the OnExit) and all the rest of the CCs should be text CCs.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #9  
Old 07-10-2017, 07:39 PM
ashg75 ashg75 is offline Word Template with a drop down list to populate other fields Windows 7 64bit Word Template with a drop down list to populate other fields Office 2013
Novice
Word Template with a drop down list to populate other fields
 
Join Date: Jun 2017
Posts: 11
ashg75 is on a distinguished road
Default

Got it! Thanks a bunch! @gmaxey
Reply With Quote
  #10  
Old 07-12-2017, 08:33 AM
ashg75 ashg75 is offline Word Template with a drop down list to populate other fields Windows 7 64bit Word Template with a drop down list to populate other fields Office 2013
Novice
Word Template with a drop down list to populate other fields
 
Join Date: Jun 2017
Posts: 11
ashg75 is on a distinguished road
Default

Sorry to bother you but I have tried running the code that you have provided but I keep getting the macros popup box to create a macro. I have already entered my Content Controls and I am not sure what it is that I am missing? I apologize but I have basic VBA knowledge and don't intend to make this harder than what it really is.
Attached Images
File Type: jpg Capture.JPG (102.9 KB, 37 views)
Reply With Quote
  #11  
Old 07-12-2017, 01:02 PM
gmaxey gmaxey is offline Word Template with a drop down list to populate other fields Windows 7 32bit Word Template with a drop down list to populate other fields Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,361
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Is the code you are showing in the ThisDocument module of the document or template?

What do you mean by '... but I keep getting the Create Macros popup menu?" Keep getting it when? What exactly are you doing?
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #12  
Old 07-12-2017, 01:21 PM
ashg75 ashg75 is offline Word Template with a drop down list to populate other fields Windows 7 64bit Word Template with a drop down list to populate other fields Office 2013
Novice
Word Template with a drop down list to populate other fields
 
Join Date: Jun 2017
Posts: 11
ashg75 is on a distinguished road
Default Word Template with a drop down list to populate other fields

The code is in the "ThisDocument" module of the normal document. Once I press F5 to run the code the shown dialog box pops up.
Reply With Quote
  #13  
Old 07-12-2017, 05:46 PM
gmaxey gmaxey is offline Word Template with a drop down list to populate other fields Windows 7 32bit Word Template with a drop down list to populate other fields Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,361
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Put it in the this document module of the active document. You don't run any macro. The ContenControl OnEnter event fires when you enter the CC.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #14  
Old 07-12-2017, 08:02 PM
ashg75 ashg75 is offline Word Template with a drop down list to populate other fields Windows 7 64bit Word Template with a drop down list to populate other fields Office 2013
Novice
Word Template with a drop down list to populate other fields
 
Join Date: Jun 2017
Posts: 11
ashg75 is on a distinguished road
Default Word Template with a drop down list to populate other fields

Greg,

I have tried doing as you suggested however I get the error message "data source name is too long. It seems as if this is the only issue. Please see the screenshots.
Attached Images
File Type: jpg Capture.1.JPG (33.4 KB, 31 views)
File Type: jpg Capture.2.JPG (71.0 KB, 31 views)
Reply With Quote
  #15  
Old 07-12-2017, 08:20 PM
gmaxey gmaxey is offline Word Template with a drop down list to populate other fields Windows 7 32bit Word Template with a drop down list to populate other fields Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,361
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Have you searched for information on that error? Maybe you could use a shorter data source name.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA coding for multiple drop-down lists to populate a text box for each list yeatropulo Word VBA 14 11-11-2015 01:08 PM
Error 5941 when running my macro to auto populate fields throughout the word doc VBAnovice1 Word VBA 2 05-26-2015 01:35 AM
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data Faldinio Word VBA 7 10-19-2014 06:03 AM
Word Template with a drop down list to populate other fields how do I auto-populate a word template Greenhorn Word 4 09-05-2012 04:16 PM
Word Template with a drop down list to populate other fields MSWord forms use list to populate successive text fields 2surgeons Word 4 03-05-2012 07:19 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:23 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft