 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / May 2008combo box, drop down - dynamic populate
I have an insurance template which auto populates some fields from a database. One of the fields being populated is a client id. What I would also like to do get the policies of this client and populate a drop down or combo box. I know how to sql the databases but unsure of how to populate the drop-down or combo box for selection. Example: Let's say client abc has 5 different policies with our company (PAP 101, HO 123, CF 345, MC 456, and GL 890). I would like the drop down or combo box to populate with this info. Once one is selected then I would populate additional fileds based upon info of that policy. Thanks for the help in advance. Bryan let me qualify a bit: my sql code: ... ... Set zd = CreateObject("ADODB.Recordset") zd.Open strquery4, objConn4, 2, 3 If Not zd.EOF Then zd.MoveFirst Do While Not zd.EOF 'here I want to load the drop down or combo zd.MoveNext Loop End If Also: In my document (new) I ge the client id. In the doc new, if I use a combo in a userform I will use UserForm1.Show How can I pass the client id if the above sql is in the userform ? Bryan > I have an insurance template which auto populates some fields from a > database. One of the fields being populated is a client id. What I would also [quoted text clipped - 14 lines] > > Bryan Hi Bryan, I think I understand what you want to do, but correct me if I'm wrong... Am I right that you use the client ID in the query string strquery4 to get the proper recordset? And you want to know how to get the client ID from a field in the document and pass it into the userform to build that query string? First, at the top of the userform code before the first Sub line, declare a public variable: Public ClientID As String Use that variable in the Userform_Initialize procedure to build the query string. Later in that same procedure, put in the SQL query code, using the proper field of the zd recordset in the combobox's .AddItem statements to build the list. One other thing: In the click procedure of the OK button, use Me.Hide instead of an Unload Me statement. In the macro that calls the userform's .Show method, set it up something like this: Dim UF As UserForm1 Set UF = New UserForm1 UF.ClientID = ActiveDocument.FormFields("ClientID").Result UF.Show Set UF = Nothing Because the userform and its public variable exist in memory as soon as the New statement executes, you can assign the variable's value before the .Show. When the .Show executes, the Userform_Initialize procedure runs first and populates the combobox, and only then does the userform appear on screen. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. >let me qualify a bit: >my sql code: [quoted text clipped - 38 lines] >> >> Bryan Hi Jay, The ClientID is not coming into the userform. I have put a message box in there to check. Here is my code from the call: Do Until strDocType <> "" Dim UF As UserForm1 Set UF = New UserForm1 UF.ClientID = ActiveDocument.FormFields("text1").Result UF.Show strDocType = UF.ComboBox1.Text MsgBox strDocType Loop Set UF = Nothing Here is my userform code that I tested with: Public ClientID As String 'Private Sub UserForm_Initialize() Sub UserForm_Initialize() MsgBox "ClientID " & ClientID ComboBox1.AddItem ("Manual Renewal") ComboBox1.AddItem ("Endorsement") ComboBox1.AddItem ("Misc") End Sub 'Private Sub CommandButton1_Click() Sub CommandButton1_Click() Dim i As Integer, Addressee As String Addressee = "" For i = 1 To ComboBox1.ColumnCount ComboBox1.BoundColumn = i Addressee = ComboBox1.Value Next i 'UserForm1.Hide Me.Hide End Sub I changed from Private to Public with no difference. my strDocType is coming back with a value. Hopefully this will help you shed some light on this. Thanks, Bryan > Hi Bryan, > [quoted text clipped - 80 lines] > >> > >> Bryan I'm not sure of what you are (or aren't) seeing. First, does the document have a formfield named "text1", and does it contain any text? Second, are you seeing the message boxes called from Userform_Initialize and from the calling macro? If so, what do they say? Third, you wrote "my strDocType is coming back with a value". Is that a typo that should have said "my strDocType is NOT coming back with a value"? How many columns does the combobox have? and why are you setting Addressee to each column, and then not doing anything with that value? > Hi Jay, > The ClientID is not coming into the userform. I have put a message [quoted text clipped - 128 lines] >>>> >>>> Bryan Hi Jay, 1) The form does contain "text1". Out of habit I have not changed the bookmark. There is a clientid value in that field before the userform 2) the message box I inserted in the doc(new) has clientid = '0000266266', the message box in the userform_initialize has nothing. 3) strDocType value coming back from the userform is whatever was selected from the drop-down (ie. Manual Renewal, Endorsement, or Misc.), that is the value from Addressee I would guess. As for columns - I figured out how to use a userform from this DG,so I am not sure how many colums. I guess it would be 1 as on my .AddItem I only have one value per line. Does that sound right? Hope this helps, Bryan > I'm not sure of what you are (or aren't) seeing. > [quoted text clipped - 142 lines] > >>>> > >>>> Bryan Oops, my mistake. Instead of Userform_Initialize, use the Userform_Activate procedure to populate the combobox. The ClientID public variable will be available there (Userform_Initialize is too early). Just change the name of the procedure and leave the same code inside it. A little hint to make things easier: After doing all the ComboBox1.AddItem statements, put in ComboBox1.ListIndex = 0 Then there won't be a blank entry at the top of the list, and you don't have to use the Do Until strDocType <> "" loop. If you only have one item per line, then forget about the ColumnCount loop and the .BoundColumn. But I think there's still some confusion about what's where. In these examples, ComboBox1 contains the three possible values of strDocType. If you have Addressee values (I guess that's what you're extracting from the database with the SQL query that involves ClientID), those have to go into a different combobox. One more hint: Make it an absolute rule to rename every control and form field to tell you what it contains or what it does. Change the name of the Text1 form field to ClientID, change ComboBox1 to DocType, change CommandButton1 to OKButton, etc. Leaving the default names just promotes the confusion that leads to bugs. More hints like this can be found at http://www.word.mvps.org/FAQs/MacrosVBA/MaintainableCode.htm. >Hi Jay, >1) The form does contain "text1". Out of habit I have not changed the [quoted text clipped - 160 lines] >> >>>> >> >>>> Bryan -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. Thanks Jay. Works like a charm! I guess I'm still confused on the colunmcount and the addressee. This was code I found on the DG before involving userforms. My thought was that the addressee was the pick from the combo box and the value returned in strDocType. As for the column loop part, are you saying all I need in this routine is the Me.Hide? Sub CommandButton1_Click() Dim i As Integer, Addressee As String Addressee = "" For i = 1 To ComboBox1.ColumnCount ComboBox1.BoundColumn = i Addressee = ComboBox1.Value Next i Me.Hide End Sub Thanks, Bryan > Oops, my mistake. Instead of Userform_Initialize, use the Userform_Activate > procedure to populate the combobox. The ClientID public variable will be [quoted text clipped - 191 lines] > Microsoft Word MVP FAQ: http://word.mvps.org > Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. Hi Bryan, As I hinted before, the userform we've discussed so far does only part of the job you described in your first post. We have it getting the ClientID from the field in the document, but not doing anything with that information. We have the user choosing a document type from the three choices in a combobox. But that's all it does... Your first post had some pseudocode for making a SQL query to a database, presumably to get all the records whose key is the value of ClientID in the document. Now you need to look in each of those records to get the policy number, and you need to store the policy numbers in a second combobox (NOT in the combobox that has the document types). The code to do the SQL query and to populate the policy combobox should be in the Userform_Activate procedure, after the code that populates the document type combobox. You're correct that in the CommandButton1_Click procedure, you do need the Me.Hide but not any of the other code you've already shown. However, you do need new code to do something with the selected policy number in the second combobox. I don't know what that "something" is, but I'll guess it's to make additional queries into the database to get information about the selected policy, and to write that information into the document. That new code could be in the CommandButton1_Click procedure, but it would be better to place it in the calling macro (which can get the policy number as something like UF.ComboBox2.Text). > Thanks Jay. > Works like a charm! [quoted text clipped - 238 lines] >> Email cannot be acknowledged; please post all follow-ups to the >> newsgroup so all may benefit. still have a question in loading the combo: here's my query selection result If Not zd.EOF Then zd.MoveFirst Do While Not zd.EOF strpol = zd("ahpol#").Value 'How do I additem? zd.MoveNext Loop End If Thanks, Bryan > Thanks Jay. > Works like a charm! [quoted text clipped - 215 lines] > > Microsoft Word MVP FAQ: http://word.mvps.org > > Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. addendum. Here's what I have strquery1 = "select pmprfx, pmplnr from prddta.papomf where prddta.papomf.pmclid = '" + ClientID + "'" Dim zd Set zd = CreateObject("ADODB.Recordset") zd.Open strquery1, objConn1, 2, 3 If Not zd.EOF Then zd.MoveFirst i = 0 Do While Not zd.EOF ComboBox1.AddItem (i) ComboBox.Column(0, i) = zd("pmprfx").Value + " " + zd("pmplnr").Value zd.MoveNext i = i + 1 Loop End If Thanks, Bryan > still have a question in loading the combo: > here's my query selection result [quoted text clipped - 230 lines] > > > Microsoft Word MVP FAQ: http://word.mvps.org > > > Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. That will probably work (although I can't test it because I don't have your database), but it can be simplified: zd.Open strquery1, objConn1, 2, 3 If Not zd.EOF Then zd.MoveFirst Do While Not zd.EOF ComboBox1.AddItem zd("pmprfx").Value + " " + zd("pmplnr").Value zd.MoveNext Loop End If > addendum. > Here's what I have [quoted text clipped - 19 lines] > Thanks, > Bryan  SignatureRegards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. works fine within my document_new. So as to use more than once, say upon entry into "Policy" form field, I made a seperate macro to call this UF. If I leave this in doc new it works fine but, when I call a macro the userform pops up outside of the form and does not return variables correctly from another sql. Can you not set this up as it's own macro? Here's my code if I call from doc_new..... ... call getUF ... sub getUF() Set UF = New UserForm1 UF.ClientID = ActiveDocument.FormFields("Text2").Result UF.Show strpolicy = UF.ComboBox1.Text strprefix = Left(strpolicy, 2) strpoln = Mid(strpolicy, 5, 9) strstatus = Mid(strpolicy, 22, 2) Set UF = Nothing ''' Set objConn2 = CreateObject("ADODB.Connection") objConn2.Open "Provider=IBMDA400.DataSource.1;Password=decs;Persist Security Info=True;User ID=decs;Data Source=s1033781" If strstatus = "En" Then strquery2 = "select pmtecn as cn, pmteyy as yy, pmtemm as mm, pmtedd as dd from prddta.papomf where prddta.papomf.pmprfx = '" + strprefix + "'" strquery2 = strquery2 & " and prddta.papomf.pmplnr = '" + strpoln + "'" Else strquery2 = "select pmexcn as cn, pmexyy as yy, pmexmm as mm, pmexdd as dd from prddta.papomf where prddta.papomf.pmprfx = '" + strprefix + "'" strquery2 = strquery2 & " and prddta.papomf.pmplnr = '" + strpoln + "'" End If Set ef = objConn2.Execute(strquery2) If Not ef.EOF Then strlicn = ef("cn").Value strliyy = ef("yy").Value strlimm = ef("mm").Value strlidd = ef("dd").Value If Len(strlicn) = 1 Then strlicn = "0" & strlicn End If If Len(strliyy) = 1 Then strliyy = "0" & strliyy End If If Len(strlimm) = 1 Then strlimm = "0" & strlimm End If If Len(strlidd) = 1 Then strlidd = "0" & strlidd End If strexyyyy = strlicn & strliyy strRD = strlimm & "/" & strlidd & "/" & strexyyyy ActiveDocument.FormFields("Text3").Result = Trim(strRD) End If Set ef = Nothing objConn2.Close Set objConn2 = Nothing ''' ActiveDocument.FormFields("Policy").Result = Left(strpolicy, 13) end sub my userform looks like this: Public ClientID As String Private Sub UserForm_Activate() strCLLen = Len(ClientID) Do Until strCLLen = 12 ClientID = "0" & ClientID strCLLen = Len(ClientID) Loop 'As/400 Set objConn1 = CreateObject("ADODB.Connection") objConn1.Open "Provider=IBMDA400.DataSource.1;Password=decs;Persist Security Info=True;User ID=decs;Data Source=s1033781" strquery1 = "select pmprfx, pmplnr, pmstat, pmnnrs, pmreni from prddta.papomf where prddta.papomf.pmclid = '" + ClientID + "'" strquery1 = strquery1 & " and prddta.papomf.poltyp in('70', '80')" Dim zd Set zd = CreateObject("ADODB.Recordset") zd.Open strquery1, objConn1, 2, 3 If Not zd.EOF Then zd.MoveFirst Do While Not zd.EOF strprfx = zd("pmprfx").Value strPol = CStr(zd("pmplnr").Value) strstat = zd("pmstat").Value strreason = zd("pmnnrs").Value strreni = zd("pmreni").Value Select Case strstat Case "E" stat = "Entered" Case "A" stat = "Active" Case "L" stat = "Lapsed" Case "C" stat = "Cancelled" Case "X" stat = "Expired" End Select If strreason = "03" And strstat <> "C" And strstat <> "L" Then stat = "Converted" End If If strreni = "Z" And strreason <> "" And strstat <> "C" And strstat <> "L" Then stat = "Non-Renew" End If ComboBox1.AddItem strprfx + " " + strPol + " " + stat 'ComboBox1.AddItem strprfx '+ " " + zd("pmplnr").Value zd.MoveNext Loop End If objConn1.Close Set objConn1 = Nothing ComboBox1.ListIndex = 0 End Sub Private Sub CommandButton1_Click() Dim i As Integer, Addressee As String Addressee = "" For i = 1 To ComboBox1.ColumnCount ComboBox1.BoundColumn = i Addressee = ComboBox1.Value Next i 'UserForm1.Hide Me.Hide End Sub As I said if I leave all inside doc_new it works fine but, not if I set up as a macro. Also if you have a change and since you're so well versed I created a new question on Adding a file (inserting same doc/file multiple times and populating info). Thanks, Bryan > That will probably work (although I can't test it because I don't have your > database), but it can be simplified: [quoted text clipped - 31 lines] > > Thanks, > > Bryan I don't know what the problem is. There shouldn't be any difference between running code in document_new or just calling it from there or from anywhere else. The one thing that could cause problems is if you left the userform in memory and tried to call it multiple times, or if you left a database connection open and then tried to open it again. But looking at your code, it appears that everything is cleaned up at the appropriate times. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. >works fine within my document_new. >So as to use more than once, say upon entry into "Policy" form field, I made [quoted text clipped - 189 lines] >> > Thanks, >> > Bryan I seen the error of my ways. In document_new I called a macro th do the UF and also put this Run macro in entry of form field "Policy", which is my 1st formfield, so it probably ended up calling the macro twice. In order to solve this as I would like to have this macro on entry of "Policy", how can I have formfield "ClientID" be highlighted for entry? I tried: ActiveDocument.Bookmarks("ClientID").Range.Fields(1).Result.Select but, this does not work, "Policy" is still where entry would start Thanks, Bryan > I don't know what the problem is. There shouldn't be any difference between > running code in document_new or just calling it from there or from anywhere [quoted text clipped - 205 lines] > >> > Thanks, > >> > Bryan I think you're over-engineering this setup. If you make sure the ClientID field is the first field in the template, it will automatically be highlighted when you make a new document from the template or when you reopen a saved document. In fact, it doesn't have to be the first one; all you need is the ClientID field to be somewhere before the Policy field, so ClientID is filled in before you get to Policy. Then assign the calling macro as the entry macro for the Policy field, and don't have any document_new. As an alternative, you could have the calling macro ask for the ClientID as the first thing it does, before calling the UF. The answer could be put into the ClientID field and also passed to the UF. If you do that, it would be a good idea to set the .Enabled property of the ClientID field to False so it couldn't be changed later. >I seen the error of my ways. In document_new I called a macro th do the UF >and also put this Run macro in entry of form field "Policy", which is my 1st [quoted text clipped - 217 lines] >> >> > Thanks, >> >> > Bryan -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. Would be nice but, this is a state endorsement form. Client ID as well as other info is populated on doc_new. Since each client can have multiple policies I am using the userform for them to select which policy and it's relate info they are sending. My thought was that after selecting a policy, running the macro on entry would give them an opportunity to select different policy. That's the reason for looking at setting the focus on a different form field. Thanks, Bryan > I think you're over-engineering this setup. > [quoted text clipped - 240 lines] > Microsoft Word MVP FAQ: http://word.mvps.org > Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.