DAO Multiple lists relate to one field

[Kerri]

Hi,

I'm in Word 2003 and I have TWO DAO excel spreadsheets that I want to
use in my VBA User Form. One is the Authors information (Name,
initials, email, title, phone, location...) . That pulls the correct
info into the fields of the form. The cmbOfficeLocation.valule field
is the field the second database needs to access in order to insert
the correct address in the document.

The second database includes the office locations info (Branch,
Address, City...Phone, etc.)

I tried to pull the second database into the cmbOfficeLocatoin.value
field. and it works fine as long as the author/location doesn't
change. Then the .column 7 properly refers to the second table when I
want it to refer to the first table.

How do I get it to pull the data from one data record into the next
data record?
"Phoenix" = "Phoenix"
cmbLOCATION.Value = cmbOfficeLocation.Value
I get: "602-111-1111" = "Phoenix"

Because the phone is referenced in Column 7 of the Location database
and Phoenix is referenced in column 7 of the Authors database.

Thank you for your time.
Kerri


Private Sub UserForm_Initialize()
'loads the Data info
'=====Load Authors data from Excel table=====
'This populates the combo box for Office Location
cmbOfficeLocation.AddItem "Colorado Springs"
cmbOfficeLocation.AddItem "Denver"
cmbOfficeLocation.AddItem "Phoenix"
cmbOfficeLocation.AddItem "Scottsdale"
cmbOfficeLocation.AddItem "St. Louis"
cmbOfficeLocation.AddItem "Steamboat Springs"
cmbOfficeLocation.AddItem "Vail"

'Contains a list of Authors and preferences
'NOTE: Must Reference "Microsoft DAO 3.6 Object Library" by going to
"Tools | References..." for Word 2003
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("c:\_Auth\AuthList.xls", False, False,
"Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `Authors`") '**Authors is
Named Range in the Excel Spreadsheet.
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
cmbAuthorsInitials.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
cmbAuthorsInitials.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
'=======End Authors Excel Datafile=================
End Sub

 

[Doug Robbins - Word MVP]

See the Cascading List Boxes section of the following page of fellow MVP
Greg Maxey's website:

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

--
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, originally posted via msnews.microsoft.com

 

[Kerri]

Thank you for the link I was able to get it to work by having it load
on OK command but I will check out the info also.

I do have another question:

Does Excel DAO have a row limit that it will pull into combolist? My
Dbase has 390 records and is 270km in size?


Thanks again,
Kerri
-----------------------------------

 

[Kerri]

I think I figured it out. Is it possible the Userform wouldn't pull
in excel data that contained the two following items?

1. Hyperlinks for email messages. Once I removed them (I did a paste
special - unformatted into word, then back into excel)
2. One of the columns that contained numbers had n/a in the field.
Once I removed the complete list pulled into the form.

thank you

 

[Doug Robbins - Word MVP]

Try adding the IMEX=1 switch to you OpenDatabase command as follows:

Set db = OpenDatabase(DataSource, False, False, "Excel 12.0; IMEX=1;")


--
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, originally posted via msnews.microsoft.com
I think I figured it out. Is it possible the Userform wouldn't pull
in excel data that contained the two following items?

1. Hyperlinks for email messages. Once I removed them (I did a paste
special - unformatted into word, then back into excel)
2. One of the columns that contained numbers had n/a in the field.
Once I removed the complete list pulled into the form.

thank you