Accessdata into word combobox

[Lone K.]

Hi,

I got some code from Dough to get data from an Access database to a combobox
in word, and it worked perfectly.

But now I need the code that does following:
I pick an item from one list from a combobox in word (data from Access) and
then I automatically want it to get the next fields in the record to another
combo or list box (can also be a textbox).

So far I have following code - It does not get another field automatically
but operate individually.

Private Sub UserForm_Activate()
'Henter data fra database til Userformen - En record ad gangen.
'allocate memory for the database object as a whole and for the active
record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
'Open a database
Set myDataBase = OpenDatabase("f:\Skabeloner\Udvikling\LOK\ICN.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Produkt", dbOpenForwardOnly)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not myActiveRecord.EOF
cboProdukt.AddItem myActiveRecord.fields("Produktnavn")
'access the next record
myActiveRecord.MoveNext
Loop
'Then close the database
myActiveRecord.Close
myDataBase.Close

Set myDataBase = OpenDatabase("f:\Skabeloner\Udvikling\LOK\ICN.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Produkt", dbOpenForwardOnly)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not myActiveRecord.EOF
cboProduktbeskrivelse.AddItem
myActiveRecord.fields("Produktbeskrivelse")
myActiveRecord.MoveNext
Loop
'Then close the database
myActiveRecord.Close
myDataBase.Close

I hope someone can help me.

Thanks, Lone

 

[Doug Robbins - Word MVP]

Hi Lone,

The following code will load all of the fields from the Access table into
mutliple columns in the List Box.

Private Sub UserForm_Activate()
'allocate memory for the database object as a whole and for the active
record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Integer, j As Integer, m As Integer, n As Integer
'Open a database
Set myDataBase = OpenDatabase("E:\Access97\Ely\ResidencesXP.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)
'Get the number of fields in the table
j = myActiveRecord.Fields.Count
'Get the number of Records in the table
'Loop through all the records in the table until the end-of-file marker is
reached
i = 0
Do While Not myActiveRecord.EOF
i = i + 1
'access the next record
myActiveRecord.MoveNext
Loop
myActiveRecord.Close
'Set the number of columns in the listbox
ListBox1.ColumnCount = j
' Define an array to be loaded with the data
Dim MyArray() As Variant
'Load data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 2
Set myActiveRecord = myDataBase.OpenRecordset("Owners",
dbOpenForwardOnly)
m = 0
Do While Not myActiveRecord.EOF
MyArray(m, n) = myActiveRecord.Fields(n + 1)
m = m + 1
myActiveRecord.MoveNext
Loop
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

You can determine which columns are displayed by setting the widths of the
columns to either 0 then it won' be displayed or some other appropriate
value.

You can reference the data in a column of the selected record by referring
to it's column number (they start from 0) and it's row number which will be
the ListIndex property of the ListBox.

e.g. If you wanted the data from the second field in the Access table to be
displayed in a TextBox1, you would use

TextBox1 = ListBox1.Column(1, ListBox1.ListIndex)

in the Click() event for the ListBox, i.e. ListBox1_Click()

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Lone K.]

Thanks, but now nothing happens when I place the code in my template.. I
have experienced it before with exactly this code, no errors, but also no
data in the combobox. Can you help me? Here is the changed code:

Private Sub UserForm_Activate2()
' henter alle data fra database til cboprodukt i flere kolonner.
'allocate memory for the database object as a whole and for the active
record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Integer, j As Integer, m As Integer, n As Integer
'Open a database
Set myDataBase = OpenDatabase("f:\Skabeloner\Udvikling\LOK\ICN.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Produkt", dbOpenForwardOnly)
'Get the number of fields in the table
j = myActiveRecord.fields.count
'Get the number of Records in the table
'Loop through all the records in the table until the end-of-file marker is
reached
i = 0
Do While Not myActiveRecord.EOF
i = i + 1
'access the next record
myActiveRecord.MoveNext
Loop
myActiveRecord.Close
'Set the number of columns in the listbox
cboProdukt.ColumnCount = j
' Define an array to be loaded with the data
Dim MyArray() As Variant
'Load data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 2
Set myActiveRecord = myDataBase.OpenRecordset("Produkt", dbOpenForwardOnly)
m = 0
Do While Not myActiveRecord.EOF
MyArray(m, n) = myActiveRecord.fields(n + 1)
m = m + 1
myActiveRecord.MoveNext
Loop
Next n
' Load data into ListBox1
cboProdukt.List() = MyArray
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

 

[Doug Robbins - Word MVP]

Hi Lone,

The sub would need to be called

Private Sub UserForm_Activate()

not

Private Sub UserForm_Activate2()

There is no Activate2 event

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Lone K.]

Hi Doug,

Sorry, I had looked at the code too long that day......

Now it all works and I can sleep at night again.

Thank you again, it is most helpfull to get the codelines and explanations.

/Lone

 

[Lone K.]

Hi Doug,

Now I got problems again..

The data in the database is written with linebreaks (a very long text) and
when it returns to word in the listbox the breaks is stated with the
formatting sign for linebreak and when I put it in my document it goes
wrong.

The following code shows how I get it into the document, and when the
formatting sign is there, it takes the next productname and save rest of the
description to put in last, that is, the productname("PRODUKTADD1") is
written, then the produktdescription.("PRODUKTBESKRIVELSEADD1") is written
until the "break", then everything after the "break" is written after the
last productname. How to get the whole text under PRODUKTBESKRIVELSEADD1 in
one place?

It is difficult to explain, but I hope you understand the problem. I thought
that I somehow could define the break in a different way, in the database or
in vba? Or is it the way it is written into the document?

You have been a great help before, and I hope you can help me again,

Lone K

'set the productname and description in the right paragraph. Adds
bookmarks "on the run".
If ActiveDocument.Bookmarks.Exists("PRODUKT1") Then
Set rngRange = ActiveDocument.Bookmarks("PRODUKT1").Range 'placerer
makøren rigtig til at addere bogmærke
rngRange.Text = ""
rngRange.Select
End If

'1. bogmærke
If txtC1.Value <> "" Then
ActiveDocument.Bookmarks.Add ("PRODUKTADD1")
Selection.Font.Bold = True
ActiveDocument.Bookmarks("PRODUKTADD1").Range.Text = txtC1.Text
Selection.Paragraphs(1).Range.Select
char = Selection.EndOf(unit:=wdParagraph, Extend:=wdMove)
Selection.MoveLeft unit:=wdCharacter, count:=1
WordBasic.Insert Chr(10)
Selection.Font.Bold = False
ActiveDocument.Bookmarks.Add ("PRODUKTBESKRIVELSEADD1")
ActiveDocument.Bookmarks("PRODUKTBESKRIVELSEADD1").Range.Text =
txtk1.Text

'set cursor in the end of what just written and add a linebreak
'The variable "char" will contain the number of characters moved.
Selection.Paragraphs(1).Range.Select
char = Selection.EndOf(unit:=wdParagraph, Extend:=wdMove)
Selection.MoveLeft unit:=wdCharacter, count:=1
WordBasic.Insert Chr(10)
WordBasic.Insert Chr(10)
ActiveDocument.Bookmarks.Add ("PRODUKT1")

Set rngRange = ActiveDocument.Bookmarks("PRODUKT1").Range 'place the
pointer right to place the bookmark
rngRange.Text = ""
rngRange.Select
End If

'2. bogmærke
If txtC2.Value <> "" Then
ActiveDocument.Bookmarks.Add ("PRODUKTADD1")
Selection.Font.Bold = True
ActiveDocument.Bookmarks("PRODUKTADD1").Range.Text = txtC2.Text
Selection.Paragraphs(1).Range.Select
char = Selection.EndOf(unit:=wdParagraph, Extend:=wdMove)
Selection.MoveLeft unit:=wdCharacter, count:=1
WordBasic.Insert Chr(10)
Selection.Font.Bold = False
ActiveDocument.Bookmarks.Add ("PRODUKTBESKRIVELSEADD1")
ActiveDocument.Bookmarks("PRODUKTBESKRIVELSEADD1").Range.Text =
txtk2.Text

'set cursor in the end of what just written and add a linebreak
'The variable "char" will contain the number of characters moved.
Selection.Paragraphs(1).Range.Select
char = Selection.EndOf(unit:=wdParagraph, Extend:=wdMove)
Selection.MoveLeft unit:=wdCharacter, count:=1
WordBasic.Insert Chr(10)
WordBasic.Insert Chr(10)
ActiveDocument.Bookmarks.Add ("PRODUKT1")

Set rngRange = ActiveDocument.Bookmarks("PRODUKT1").Range 'place the
pointer right to place the bookmark
rngRange.Text = ""
rngRange.Select
End If

And so on................