Populate userform combox with access database

  • Thread starter Thread starter Mdmax
  • Start date Start date
M

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

My web site www.gmayor.com

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


Mdmax-clulessnoob said:
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.

Graham Mayor said:
See http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

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

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Click to expand...
Click to expand...
 
Back
Top