Populate userform combox with access database

[Mdmax]

What is the best way to populate a userform combobox (msword2003), wit
data from an access2000 database named Docs.mdb?

The fields within Docs.mdb are: Last, Name, Credential, Number
Building, City, State, Zip. These fields (for a specific doctor) wil
then print to a report

 

[Graham Mayor]

See http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Mdmax-clulessnoob]

Hi Graham, thank you for the link.

However, the question I posted on 15-9-09 (which, nobody wanted to answer),
lists the code that I had worked out for myself, as taken (verbatim) from
greg's tutorial, named "Access Example 2".

I could not get "Access Example 1" to work for me, but the second (#2) works
perfectly, except that I can get only "one" field to print to the report (the
"first" field...), It contains a list of last names.

The combobox fills perfectly with all the fields, and I can choose a row
complete with all the listings, but only the 1st field prints to the report.

The code I am using is within the: Private Sub
UserForm_Initialize()/end sub statement

(enabled library is: “Microsoft DAO 3.6 Object Libraryâ€)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
'Open the database to retrieve data
Set db = OpenDatabase("C:\Databases\Docs.mdb")
'Define the first recordset
Set rs = db.OpenRecordset("SELECT * FROM Docs ORDER BY [Last]")
'Determine the number of records in the recordset
With rs
..MoveLast
NoOfRecords = .RecordCount
..MoveFirst
End With
'Set the number of ListBox columns = number of fields in the recordset
ComboBoxDocs.ColumnCount = rs.Fields.Count
'Load the ListBox with the retrieved records
ComboBoxDocs.Column = rs.GetRows(NoOfRecords)
'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing



The data then should transfer to the report via a simple bookmark after the:
Private Sub cmdOK_Click()
user form section, the bookmark is called:

ActiveDocument.Bookmarks("AllDocs").Range.Text = ComboBoxDocs.Text

The codes will only transfer a single name (from the first field of the
database table). I'm sure I am (again) just missing something simple in all
this, but any advice is greatly appreciated.

 

[Mdmax-clulessnoob]

I should mention (in reference to my 1st reply to you), that my access
database, Docs.mdb, has the following fields:

An "SQL query" returned:

SELECT Docs.Last, Docs.Name, Docs.Credential, Docs.Number, Docs.Building,
Docs.City, Docs.State, Docs.Zip
FROM Docs;

The source file (Docs.mdb) has a table (named: Docs), with 7 fields, which
are: Last, Name, Credential, Number, Building, City, State, and Zip.

Sorry, I left that out.

 

[Graham Mayor]

Greg is usually watching, but as this is related to code fom his web site,
use the link on his web page to question him about his code.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Mdmax-clulessnoob]

Greg Maxey helped me, based on email querys that I made at his website:
http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

I am using his MSAccess example #2.

The code that finally works for me (with Greg's help) is:

within the form initialize() section

'You need to set a reference in your project to the “Microsoft DAO 3.51 (or
3.6) Object Libraryâ€.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
'Open the database to retrieve data
Set db = OpenDatabase("C:\Databases\Docs.mdb")
'Define the first recordset
Set rs = db.OpenRecordset("SELECT * FROM Docs ORDER BY [Last]")
'Determine the number of records in the recordset
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
'Set the number of ListBox columns = number of fields in the recordset
ComboBoxDocs.ColumnCount = rs.Fields.Count
'Load the ListBox with the retrieved records
ComboBoxDocs.Column = rs.GetRows(NoOfRecords)
'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

------------------

The (MSWord) document bookmark references are within the
CommandButton1_click() section, as follows:

If ComboBox1.ListIndex = -1 Then
ActiveDocument.Bookmarks("AllDocs").Range.Text = ComboBoxDocs.Text
Else
ActiveDocument.Bookmarks("AllDocs").Range.Text = ComboBoxDocs.Text & " " &
ComboBoxDocs.Column(1) & " " & ComboBoxDocs.Column(2) & " " &
ComboBoxDocs.Column(3) & " " & ComboBoxDocs.Column(4) & " " &
ComboBoxDocs.Column(5) & " " & ComboBoxDocs.Column(6) & " " &
ComboBoxDocs.Column(7) & " "
End IF

The first (IF) section handles a manual entry in the combobox (described as
index minus 1), the next (Else) section handles info from the database (index
0-7)

The data from the userform then prints to the MSword document at the
specified bookmark "AllDocs"

Thanks Greg! http://gregmaxey.mvps.org

Hi Graham, thank you for the link.

However, the question I posted on 15-9-09 (which, nobody wanted to answer),
lists the code that I had worked out for myself, as taken (verbatim) from
greg's tutorial, named "Access Example 2".

I could not get "Access Example 1" to work for me, but the second (#2) works
perfectly, except that I can get only "one" field to print to the report (the
"first" field...), It contains a list of last names.

The combobox fills perfectly with all the fields, and I can choose a row
complete with all the listings, but only the 1st field prints to the report.

The code I am using is within the: Private Sub
UserForm_Initialize()/end sub statement

(enabled library is: “Microsoft DAO 3.6 Object Libraryâ€)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
'Open the database to retrieve data
Set db = OpenDatabase("C:\Databases\Docs.mdb")
'Define the first recordset
Set rs = db.OpenRecordset("SELECT * FROM Docs ORDER BY [Last]")
'Determine the number of records in the recordset
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
'Set the number of ListBox columns = number of fields in the recordset
ComboBoxDocs.ColumnCount = rs.Fields.Count
'Load the ListBox with the retrieved records
ComboBoxDocs.Column = rs.GetRows(NoOfRecords)
'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing



The data then should transfer to the report via a simple bookmark after the:
Private Sub cmdOK_Click()
user form section, the bookmark is called:

ActiveDocument.Bookmarks("AllDocs").Range.Text = ComboBoxDocs.Text

The codes will only transfer a single name (from the first field of the
database table). I'm sure I am (again) just missing something simple in all
this, but any advice is greatly appreciated.

See http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>