Here is some code that does the sort of thing that you want. In the
Initialize event, a series of comboboxes are populated with data from
various tables in an Access database. In the Change or Exit events from
some of those comboboxes, other comboboxes are loaded with data from other
tables based on the .Value of the nominated .BoundColumn in the combobox
that is Changed or Exited. In Exit event of one of the comboboxes, .Values
from the various columns in that combobox for the selected item are inserted
into Textboxes on the form. In the cmdContinue_Click event, data from the
various controls on the form is used to create variables in the document so
that the data is displayed in Docvariable fields
Public db As DAO.Database
Public rs As DAO.Recordset
Public numrecs As Long
Public DataSource As String
'Created by Doug Robbins
Private Sub cmbAddressee_Exit(ByVal Cancel As MSForms.ReturnBoolean) With
cmbAddressee
.BoundColumn = 5
txtPosition.Text = .Value
.BoundColumn = 6
txtAddress1.Text = .Value
.BoundColumn = 7
txtAddress2.Text = .Value
.BoundColumn = 8
txtCity.Text = .Value
.BoundColumn = 9
txtState.Text = .Value
.BoundColumn = 10
txtZip.Text = .Value
.BoundColumn = 11
txtCountry.Text = .Value
End With
End Sub
Private Sub cmbCompany_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If cmbCompany.ListIndex > -1 Then
cmbAddressee.Enabled = True
cmbCompany.BoundColumn = 1
'Open the database - modify the path as required
Set db = OpenDatabase(DataSource)
'Retrieve the recordset
Set rs = db.OpenRecordset("Select * from ztblCompanyContact WHERE
[CCCOCode] = '" & cmbCompany.Value & "'")
rs.AddNew
'Determine the number of retrieved records
With rs
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With
'Set the number of columns equal to the number of fields in the
recordset
cmbAddressee.ColumnCount = rs.Fields.Count
'Load the cmbCompany combobox with the retrieved records
cmbAddressee.Column = rs.GetRows(numrecs)
rs.Close
'Cleanup
db.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub
Private Sub cmbType1_AfterUpdate()
Dim T1 As String
cmbType2.Enabled = True
cmbType1.BoundColumn = 1
T1 = cmbType1.Text
'Open the database - modify the path as required
Set db = OpenDatabase(DataSource)
'Retrieve the recordset Set rs = db.OpenRecordset("Select * from ztblType2
WHERE [T1Code] = '" & T1 & "'")
rs.AddNew
'Determine the number of retrieved records With rs
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With
'Set the number of columns equal to the number of fields in the recordset
cmbType2.ColumnCount = rs.Fields.Count
'Load the cmbCompany combobox with the retrieved records
cmbType2.Column = rs.GetRows(numrecs)
rs.Close '
Cleanup
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
Private Sub cmbType2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
cmbType2.BoundColumn = 2
If cmbArea.ListIndex > -1 And cmbDiscipline.ListIndex > -1 And
cmbPackage.ListIndex > -1 And cmbType1.ListIndex > -1 _
And cmbType2.ListIndex > -1 Then
Set db = OpenDatabase(DataSource)
'Retrieve the recordset for the selected Combination
Set rs = db.OpenRecordset("Select * from tblDocuments WHERE [DocArea] =
'" & cmbArea _
& "' And [DocDisc] = '" & cmbDiscipline & "' And [DocPackage] = '" &
cmbPackage _
& "' And [DocType1] = '" & cmbType1 & "' And [DocType2] = '" & cmbType2
& "'")
'Determine the number of records
With rs
If .RecordCount > 0 Then
.MoveLast
numrecs = .RecordCount
.MoveFirst
Else
numrecs = 0
End If
End With
'The sequence number for document being generated is one more than the
number of records
Sequence = numrecs + 1
txtSequence.Text = Format(Sequence, "0000")
rs.Close
'Cleanup
db.Close
Set rs = Nothing
Set db = Nothing
Else
MsgBox "You must select an item from each Dropdown."
End If
End Sub
Private Sub cmdContinue_Click()
'store the information about this document in the database
Set db = OpenDatabase(DataSource)
'Retrieve the recordset
Set rs = db.OpenRecordset("Select * from tblDocuments")
With rs
.AddNew
!DocArea = cmbArea.Text
!DocDisc = cmbDiscipline.Text
!DocPubDate = Format(Date, "MM-dd-yy")
!DocRegDate = Format(Date, "MM-dd-yy")
!DocPackage = cmbPackage.Text
!DocType1 = cmbType1.Text
!DocType2 = cmbType2.Text
!DocSeq = Val(txtSequence.Text)
!DocOrigCo = "GAC"
!DocOwner = txtSignatory.Text
!DocTitle = txtSubject.Text
cmbCompany.BoundColumn = 1
!DocToComp = cmbCompany.Value
!DocToContact = cmbAddressee.Text
.Update
End With
'Transfer the information from the form into the document With
ActiveDocument
.Variables("varReference").Value = cmbArea.Text & "-" &
cmbDiscipline.Text _
& cmbPackage.Text & "-" & cmbType1.Text & cmbType2.Text & "-" &
txtSequence.Text
.Variables("varAddressee").Value = cmbAddressee.Text
.Variables("varCompany").Value = cmbCompany.Text
.Variables("varAddress1").Value = txtAddress1.Text
.Variables("varAddress2").Value = txtAddress2.Text
.Variables("varCity").Value = txtCity.Text
.Variables("varState").Value = txtState.Text
.Variables("varZip").Value = txtZip.Text
.Variables("varCountry").Value = txtCountry.Text
.Variables("varSalutation").Value = txtSalutation.Text
.Variables("varSubject").Value = txtSubject.Text
.Variables("varSignatory").Value = txtSignatory.Text
.Variables("varTitle").Value = txtSigTitle
.SaveAs "P:\9_Client_GAC\9.62_Administration\Data
Management\Correspondence\" & .Variables("varReference").Value & ".doc"
.Range.Fields.Update
End With
Me.hide
End Sub
Private Sub UserForm_Initialize()
DataSource = "P:\9_Client_GAC\9.12_Project_Information\Register File
Number\DocReg_be.mdb"
cmbType2.Enabled = False
cmbAddressee.Enabled = False
'Open the database - modify the path as required
Set db = OpenDatabase(DataSource)
'Retrieve the recordset
Set rs = db.OpenRecordset("Select * from ztblArea")
rs.AddNew
'Determine the number of retrieved records With rs
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With
'Set the number of columns equal to the number of fields in the recordset
cmbArea.ColumnCount = rs.Fields.Count
'Load the cmbCompany combobox with the retrieved records
cmbArea.Column = rs.GetRows(numrecs) rs.Close
Set rs = db.OpenRecordset("Select * from ztblDiscipline")
rs.AddNew
'Determine the number of retrieved records
With rs
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With
'Set the number of columns equal to the number of fields in the recordset
cmbDiscipline.ColumnCount = rs.Fields.Count
'Load the cmbCompany combobox with the retrieved records
cmbDiscipline.Column = rs.GetRows(numrecs)
rs.Close
Set rs = db.OpenRecordset("Select * from ztblPackage")
rs.AddNew
'Determine the number of retrieved records With rs
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With
'Set the number of columns equal to the number of fields in the recordset
cmbPackage.ColumnCount = rs.Fields.Count
'Load the cmbCompany combobox with the retrieved records
cmbPackage.Column = rs.GetRows(numrecs)
rs.Close
Set rs = db.OpenRecordset("Select * from ztblCompany")
rs.AddNew
'Determine the number of retrieved records With rs
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With
'Set the number of columns equal to the number of fields in the recordset
cmbCompany.ColumnCount = rs.Fields.Count
'Load the cmbCompany combobox with the retrieved records
cmbCompany.Column = rs.GetRows(numrecs)
rs.Close
Set rs = db.OpenRecordset("Select * from ztblPackage")
rs.AddNew
'Determine the number of retrieved records With rs
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With
'Set the number of columns equal to the number of fields in the recordset
cmbPackage.ColumnCount = rs.Fields.Count
'Load the cmbCompany combobox with the retrieved records
cmbPackage.Column = rs.GetRows(numrecs)
rs.Close
Set rs = db.OpenRecordset("Select * from ztblType1")
rs.AddNew
'Determine the number of retrieved records With rs
.MoveLast
numrecs = .RecordCount
.MoveFirst
End With
'Set the number of columns equal to the number of fields in the recordset
cmbType1.ColumnCount = rs.Fields.Count
'Load the cmbCompany combobox with the retrieved records
cmbType1.Column = rs.GetRows(numrecs)
rs.Close
'Cleanup
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
--
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
mkuch said:
I am working on a Word 2003 form that I am setting up a VBA userform. One
of
the things I would like to do is enter a customer number in a text box in
he
userform then have the userform go to a database on a server, look for the
customer number in either an existing table or query, read a column named
"Ship_To_Nbr" in the table or query, take the Ship_To_Nbr back to the user
form in the form of a combobox. The user would select the Ship_To_Nbr from
the combobox. The userform would then go back to the database and retreive
the ship to address information from the database and populate 6 text
boxes
in the userform. The textboxes in the userform would be Name, Addr1,
Addr2,
City, State, Country.
I have been able to get all of the userform working except this last part.
Any help would be greatly appreciated!
Thank you in advance.
