Jay
Thu May 08 07:33:14 PDT 2008
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 wrote:
> 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" wrote:
>
>> 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.
>>
>>
>> On Wed, 7 May 2008 10:12:27 -0700, bryan
>> <bryan@discussions.microsoft.com>
>> wrote:
>>
>>> 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" wrote:
>>>
>>>> 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 wrote:
>>>>> 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" wrote:
>>>>>
>>>>>> 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.
>>>>>>
>>>>>> On Tue, 6 May 2008 18:42:24 -0700, bryan
>>>>>> <bryan@discussions.microsoft.com> wrote:
>>>>>>
>>>>>>> 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
>>>>>>>
>>>>>>> "bryan" wrote:
>>>>>>>
>>>>>>>> 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
>>>>
>>>>
>>>>
>>
>> --
>> 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.