Word 2000 and Access 2000 database with a twist

[Henrik B.]

I have a access 2000 database that contains 2 tables called Movietable and
Typetable.

The Typetable contains 2 fields - one which is autonummeric and the other
which is a textfield.

The Movietable contains 7 fields - one which is autonummeric and the others
which are a combination of text and nummeric fields.

In word 2000 I have a combobox called combo1 which is filled with data from
the Typetable. Now I want to pick on of the items in combo1 and find all the
corresponding data in the Movietable. This data is then to be presentet in a
combobox called combo2.

I just cant get this to work in the right way. Could any of you out there
guide me in the right direction!

Any help is very appreciated!

Henrik.

 

[Henrik B.]

Hi Doug.

Thanks for the help, but it doesn't work 100% - sorry!!

When I do what you write. I get the information in the combobox, but I cant
pick the information. When i pick one of the entryes it doesn't pop up in
the combobox - why?

When I was playing around with the code before I managed to get the last
record in the base up in the combobox, but not the other records?

Does any of you know what is wrong with my form or code?

Again any help is very appreciated!

Henrik.

 

[Doug Robbins - Word MVP]

Hi Henrik,

Are you saying that you have been able to populate combobox2 with the
required information, but that you cannot select an item in that combobox?

That sounds like it's something wrong with the combobox rather than the
code.

If you want to send the template to me I will take a look at it.

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

Hope this helps
Doug Robbins - Word MVP

 

[Henrik B.]

Hi Doug.

I had another go at it this morning.... I deleted the combobox because as
you said, the code looked ok, so there had to be something wrong with the
combobox.

I created a new combo and then the code was working 100% - what was wrong??
Who can tell with computers, but it worked.....

So once again you helped me out Doug! Hopefully I'll be able to do the same
for you some day

Thanks again - have a nice day!

Henrik

 

[Doug Robbins - Word MVP]

Hi Lone,

Are you wanting to do the same thing as Henrik, i.e. load a combobox with
only those records in which one field matches what is select in another
combobox. or just populate a combobox (or listbox) with all of the records
from a table. Here are a couple of routines for that. The first loads the
data from all of the fields into a multicolumn control while the second just
loads the data from one particular field:

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


Private Sub UserForm_Initialize()
'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("E:\Access97\Ely\ResidencesXP.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not myActiveRecord.EOF
ListBox1.AddItem myActiveRecord.Fields("Owner")
'access the next record
myActiveRecord.MoveNext
Loop
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

Note that to use these routines, you must establish a reference, in your
template, to the Microsoft DAO #.# Object Library.

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]

Hi Doug,

Thank you - it works - and very fast. I just wanted all the records from
all the fields, and have therefore used the first example.

Thanks again,

Lone