combo box, drop down - dynamic populate

[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
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

 

[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

 

[Jay Freedman]

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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[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

 

[Jay Freedman]

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?

 

[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

 

[Jay Freedman]

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.

 

[bryan]

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

 

[Jay Freedman]

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).

 

[bryan]

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

 

[bryan]

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

 

[Jay Freedman]

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

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

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[bryan]

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

 

[Jay Freedman]

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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[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

 

[Jay Freedman]

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.

 

[bryan]

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