User form / vba / word table database

[Guest]

I've finished my user form now, but it would be a nice touch if it could
also put some entries into a word table in a separate file thereby creating
a simple database. I realize that excel or access might seem more preferable
for that but for a number of reasons I'd opt for word.

The best way for me to go about it would be by using some sample code with
similar functionality to base my macro upon.

If somebody has a piece of such code that (s)he can share or point me to
some relevant info online that would be greatly appreciated.

Sergey

 

[Doug Robbins - Word MVP]

Here is some code from a userform where the data that is entered into the
form, in addition to being used to create document variables in the document
that is created from the template that contains the userform, is also used
to populate a row in a table in a log document:

Private Sub cmdContinue_Click()
Dim logrange As Range, logtable As Table
Dim LogNum As Long, rownum As Long
Dim NewDoc As Document
On Error GoTo CATCH

Set NewDoc = ActiveDocument
With NewDoc
cmbCompany.BoundColumn = 1
.Variables("varCompanyCode") = cmbCompany.Value
If txtAddressee = "" Then
.Variables("varAddressee").Value = " "
Else
.Variables("varaddressee").Value = Trim(txtAddressee)
End If
If cmbCompany.Value <> "OT" Then
cmbCompany.BoundColumn = 2
.Variables("varcompany").Value = Trim(cmbCompany.Value)
Else
If txtCompany = "" Then
.Variables("varCompany").Value = " "
Else
.Variables("varCompany").Value = Trim(txtCompany)
End If
End If
If txtstreet = "" Then
.Variables("varaddress").Value = " "
Else
.Variables("varaddress").Value = Trim(txtstreet)
End If
If txtCity = "" Then
.Variables("varcity").Value = " "
Else
.Variables("varcity").Value = Trim(txtCity)
End If
If TxtState = "" Then
.Variables("varstate").Value = " "
Else
.Variables("varstate").Value = Trim(TxtState)
End If
If txtZip = "" Then
.Variables("varzip").Value = " "
Else
.Variables("varzip").Value = Trim(txtZip)
End If
If txtCountry = "" Then
.Variables("varcountry").Value = " "
Else
.Variables("varcountry").Value = Trim(txtCountry)
End If
If txtSubject = "" Then
.Variables("varSubject").Value = " "
Else
.Variables("varsubject").Value = Trim(txtSubject)
End If
If txtSignatory = "" Then
.Variables("varsignatory").Value = " "
Else
.Variables("varsignatory").Value = Trim(txtSignatory)
End If
If txtSalutation = "" Then
.Variables("varSalutation").Value = " "
Else
.Variables("varSalutation").Value = Trim(txtSalutation)
End If
End With

If Val(NewDoc.Variables("varLogNum").Value) = 0 Then
With Word.Application
' .Visible = False
Dim Log As Word.Document
Set Log = .Documents.Open("c:\Documents and Settings\Doug
Robbins\logout.doc")
End With

With Log
Dim i As Long
cmbCompany.BoundColumn = 8
i = cmbCompany.Value
Set logtable = .Tables(i)
rownum = logtable.Rows.Count
Set logrange = logtable.Cell(rownum, 1).Range
logrange.End = logrange.End - 1
LogNum = logrange
LogNum = LogNum + 1
logtable.Rows.Add
rownum = rownum + 1
logtable.Cell(rownum, 1).Range = LogNum
NewDoc.Variables("varLogNum").Value = Format(LogNum, "000#")
NewDoc.Fields.Update
cmbCompany.BoundColumn = 1
NewDoc.SaveAs "C:\Documents\L-GAPB-" & cmbCompany.Value & "-" &
Format(LogNum, "000#")
With logtable
.Cell(rownum, 2).Range = Format(Date, "MMM dd, yyyy")
.Cell(rownum, 3).Range = NewDoc.Variables("varaddressee").Value
.Cell(rownum, 4).Range = NewDoc.Variables("varcompany").Value
.Cell(rownum, 5).Range = NewDoc.Variables("varsubject").Value
.Cell(rownum, 6).Range = NewDoc.Variables("varsignatory").Value
.Cell(rownum, 7).Range = NewDoc.FullName
End With
.Save
.Close
End With

Set Log = Nothing
NewDoc.Bookmarks("text").Range.Select
Else
NewDoc.Save
End If

Me.hide

EXITHERE:
On Error Resume Next
Word.Application.Visible = True
Exit Sub

CATCH:
Err.Raise Err.Number, Err.Source, Err.Description
Resume EXITHERE





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