UserForm field fill-in issue

[alborg]

I have a combobox on a UserForm that I want to use to "push" data to the
various fields. The problem I see is that no matter what I use- the
AfterUpdate event or the OnChange event, the fields don't get filled until
the enduser clicks on the form. How can I get the form to paint itself
automatically without this user input?

Here's the comboboxx code:

Private Sub PTNAME_AfterUpdate()
[DOB] = [PTNAME].Column(2)
[FNAME] = [PTNAME].Column(1)
[ACCT] = [PTNAME].Column(3)
[LNAME] = [PTNAME].Column(0)
End Sub

Any ideas?

 

[Doug Robbins - Word MVP]

The syntax that you are using ( [DOB] = [PTNAME].Column(2)) is foreign to
me.

To access the data from various columns of a combobox or listbox, you set
the .BoundColumn attribute of the combobox or listbox to that of the column
from which you want to obtain the data.

Here's an example

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[David Sisson]

This is a Word group.

Try posting your question to the Access group.

http://groups.google.com/group/microsoft.public.access.forms/topics?hl=en

 

[alborg]

Hi Doug/David:

David- I can't post on the Access forums as although the combobox is a MS
Access looking control, its behavior is totally different-

1) In MS Access you can "pull" the data onto textboxes that are linked to
the underlying form table; this doesn't exist in MS Word UserForms.
2) Because of #1, you have to resort to "push" the data and although the
"Change" event is the default, when the combobox changes, the fields to which
the data is pushed doesn't until you tap on the form again. I found a couple
of months ago that the "AfterUpdate" event works pretty much the same as the
"Change" event- both seem to make the form lose the focus necessitating a
form tap.

Doug- the VBA syntax is used frequently in MS Access and works in MS Word if
you invoke the MS Access ActiveX control (i.e. Tools-> References etc). It
works pretty much like your code.

In the following example-

Set rs = db.OpenRecordset("SELECT DISTINCTROW Demographics.Last,
Demographics.First, Demographics.BirthDate, Demographics.ChartID FROM
Demographics ORDER BY Demographics.Last;")

For this recordset, the "Demographics.Last" would turn out to be Column(0),
the "Demographics.First" would turn out to be Column(1), etc.

GOOD NEWS!

I figured out this problem after about a year of frustration. In MS Access,
there exists an "OnClick" event that doesn't exist in MS Word UserForms,
which is what threw me off. What DOES exist, though, is the "CLICK" event. So
this will automatically fill in the empty text fields without having to click
on the userform. Here's the code how it looks now:

Private Sub PTNAME_CLICK()
[DOB] = [PTNAME].Column(2)
[FNAME] = [PTNAME].Column(1)
[ACCT] = [PTNAME].Column(3)
[LNAME] = [PTNAME].Column(0)
End Sub

It's interesting how one word can change everything! Thanks for helping out...

Cheers,
Al