User Selection from dropdown

[NewVBer]

I've written code which adds a combo box dropdown list to a Word template and populates it with data from a database table (data not static, may change each time template is used).

This same list may be used to enter several projects and descriptions into the document each time so I need to repeat the code to add another box. However, I cannot loop it until the user selects a project from the first box. How do I prompt the user to make a selection from a combo box dropdown list and either pause the code until the user makes a selection from the dropdown or have the macro wait until a selection is made, then have it continue? Thanks for your help...I don't really have a code to show for this part of the macro because I don't know what to use to have it wait for input but I'd be happy to post anything else to show how I'm populating the lists, etc. Thank you!!!!

[Chrissy]

You need a "Click_Event" behind the drop down with code for that selection.

Private Sub ComboBox1_Click()
 
if combox1.text = "Runme" then
        'Put code based on selection here.
end if
 
End Sub

[NewVBer]

I still don't quite have it working. I tried to test the click event and it sort of worked but at the wrong place. I have a message box that asks the user if they want to "Add a Project?" If the user clicks No, then it's finished (for now). If the user clicks Yes, then a combo box dropdown list is added to the template and is populated by connecting to a database table (this list changes each time). I would like the user to select a project from the list and then have it repeat the message box, asking to "Add a Project?" It will continue to repeat until the user selects No. I added some test code to have it show another message box on click saying "It Worked". It did show the message box on click but it showed it when I clicked the Yes button on the first message box instead of waiting until I selected a project from the dropdown list. I can't quite figure out how to get it to reference the combo box (which is named Project List). Any assistance you could provide would be greatly appreciated. Here's the code I have:

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDB As String
Dim oCC As ContentControl
Dim Response As VbMsgBoxResult
Set cn = New ADODB.Connection
'Set string path to database'
    strDB = "C:\users\xxx\documents\my data sources\xxx.accdb"
    'Open the database to retrieve data
cnt.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
"Data Source=" & strDB & ";"
Response = MsgBox("Add a Project?", vbQuestion + vbYesNo)
'If response = no, then do nothing
If Response = vbNo Then
'Do Nothing
'If yes, then add a content control with dropdown list populated
Else
Set oCC = ActiveDocument.ContentControls.Add(wdContentControlDropdownList, Selection.Range)
oCC.SetPlaceholderText , , "Select a Project"
rst.Open "Select * From SelectedProjects", cnt
rst.MoveFirst
'Open recordset based on SelectedProjects Table
oCC.Title = "Project List"
  Do
oCC.DropdownListEntries.Add rst![ProjectName]
rst.MoveNext
  Loop Until rst.EOF
End If
OnClick = MsgBox("It Worked")
End Sub

[Chrissy]

What you need to do is programmatically add code to the drop down that you are adding...

Why don't you have the both the drop downs on the page... you can hide the one that loads from the database and then make it visible when you need it. This way you can have the click event for the second combo box already coded.

Does that make sense?

[NewVBer]

Not really but I'm still fairly new at this. The problem is I'm not sure how to write the code. It may not make a difference though because I just realized that the content that I need to bring in may be too large to use in the dropdown box (it's more than 255 characters). I'm not sure if there's a workaround for that. If not, then I'm at a loss and may have to dump it to Excel first before bringing it into Word. Before we go too much further on this code...do you know how to bypass the character limitation?

[westconn1]

may be too large to use in the dropdown box (it's more than 255 characters)

why would you show that much in a combobox?
you expect a user to read multiple items greater than 255 characters, to make a choice?
why not just use a heading or truncated version?

what application are you writing the code in, word or?

[NewVBer]

Ideally I'd prefer to give them a list of the project names and once they select the appropriate name, pull in the project details. However, I was running into difficulty with that because I am linking to a database that changes. So, because the list is not static, I am referencing the database table and specific column in the code. I'm guessing there may be a way to use the project name in the dropdown and when the user clicks go back to the table and pull in the corresponding description but I have no idea where to even begin with that code so I was trying an easier approach. In a nutshell, I've set up several queries in Access and built a switchboard with macros to run the queries as needed by the user. Once the Access piece is completed, the user goes into the Word template which I have set up to automatically link to another table and pull in the employee information. Then, another macro will launch to link to another table in the database to allow them to select the specific projects they need. I cannot create a report in Access because the format of the report has to be very specific and must be formatted in Word. So, most of the code I'm writing is in Word. I'm definitely open to suggestions. I would prefer that I not have to dump it into Excel run the macro there and then take it into Word...that would complicate it for the user. Thanks so much for any assistance!

[westconn1]

why not automate Word from access?

[NewVBer]

I've never done it but I suppose I could look into it.l But I would imagine I'd have to start my code from scratch?? I'm not even sure where to begin but it doesn't seem like I have alot of options at this point. Is there support for Word automation in this forum or would I go to another if (or, realistically, when) I need help? Thanks so much!

[NewVBer]

Ok...I'm not seeing how this is going to help me too much. I'm able to automate Word through Access but the user still needs to choose from a dropdown list and depending on how much space is taken up, be given the option to make additional selections. I'm not following how doing it through Access is going to change the code I need for the user to make the dropdown selection? If they make the selection in an Access table, I'd have to keep switching back and forth for the user to see how much space is remaining to know whether another item will fit in the space alotted. Does that make sense?

[westconn1]

maybe i misunderstood what you want to do, you never said anything about allotted space before

in either case you can read the faq thread at the top of this forum for the tutorial on automating office applications

[NewVBer]

Maybe I could show you the template that I'm trying to populate which might help explain it a little more. That might help make a little more sense of it and then you could point me in the right direction. If you think that will help, let me know and I'll attach it with more specifics. Thank you again! Much appreciated!

[westconn1]

what are you going to attach, a xl workbook?
make sure to put how you want things to work

ZIP before attach

[NewVBer]

It would be the Word template that I'm trying to populate with the data from the database table.