Sending Word data into access

  • Thread starter Thread starter Jay B.
  • Start date Start date
J

Jay B.

I have a Word 97 document which has several drop down boxes and text boxes.
Once the choices are made I create a text file with the selections. I then
manually import this text file into a table in an Access database.

Is there a line(s) of code that I can incorporate into my Word document VB
that
would append data into an Access database table automatically instead of
going through this intermediate manual step.


Thanks in advance


Jay
 
Hi Jay

It should be possible to capture the data
directly from the combo and text boxes in the
Word document into the Access database by
hitting, say, a toolbar button that runs some
code.

1. Do you want sample code? If so...

2. What are the names of the combo and
text boxes in the Word document?

3. What are the names of the corresponding
Access fields?

4. What is the name of the table containing
those fields?

5. What is the path and name of the
database?

6. What versions of Access and Word are
you using?

7. Can the code be incorporated into just
one Word document?

Regards.
Geoff
 
Geoff,

Sample code would be great.

To make things simple

I already have code to take the combobox selections and populate variables.

The variables are Name, address1, address1, city, state, zip, logdate,
logtime.

The path of the table is c:\logfiles or a variable called path_db

The database is called Worklog.mdb

The Table is dailywork

The field names are the same as the variables

I'm using Word97 Access97

It would be great if I could just add the code to the existing Word VB code.

Thanks


Jay
 
Hi Jay,

Looks OK.
It's late here now, so I'll take a look tomorrow pm.

Geoff
Tue, 16 Sep 2003 00:44 am BST
 
Hi Jay

You should be able to incorporate this module as
necessary.

Geoff



Option Explicit

' Assumes Reference to Microsoft DAO Object Library
' (On Tools menu, References).

' Declare variables at top of module:
Dim mobjRS As DAO.Recordset
Dim Name As String
Dim Address1 As String
Dim Address2 As String
Dim City As String
Dim State As String
Dim Zip As String
Dim LogDate As Date
Dim LogTime As Date

Sub TestWriteDataToDatabase()

' YOU ASSIGN VALUES TO VARIABLES HERE
' (FROM COMBO/TEXTBOXES):
Name = "Jay"
Address1 = "JaysAddress1"
Address2 = ""
City = "JaysCity"
State = "JaysState"
Zip = "JaysZip"
LogDate = #9/16/03#
LogTime = #5:00:00 PM#

' CALL NEXT PROCEDURE TO WRITE DATA:
WriteDataToDatabase

End Sub

Sub WriteDataToDatabase()

Const strDBPathName As String = "c:\logfiles\worklog.mdb"
Const strDBTableName As String = "DailyWork"

Dim objDB As DAO.Database

On Error GoTo ErrorHandler

Set objDB = OpenDatabase(strDBPathName)
Set mobjRS = objDB.OpenRecordset(strDBTableName)

With mobjRS
.AddNew
' Call the next subprocedure (WriteTextField),
' passing fieldnames and values to subprocedure:
WriteTextField "Name", Name
WriteTextField "Address1", Address1
WriteTextField "Address2", Address2
WriteTextField "City", City
WriteTextField "State", State
WriteTextField "Zip", Zip
.Fields("LogDate") = LogDate
.Fields("LogTime") = LogTime
.Update
End With

Bye:
If Not mobjRS Is Nothing Then mobjRS.Close
Set mobjRS = Nothing
If Not objDB Is Nothing Then objDB.Close
Set objDB = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Description, vbOKOnly, "Error No: " & Err.Number
Resume Bye

End Sub

Private Sub WriteTextField( _
strFieldName As String, _
vntValue As Variant)

' This subprocedure ensures we don't write
' empty strings to the Text fields:
If vntValue <> vbNullString Then
mobjRS.Fields(strFieldName) = vntValue
End If
End Sub
 
Geoff,
Thanks for the code. It looks good. There are some things that I havn't
seen, but I get the point.

Thanks again.

Jay
 
Jay

Sounds as though you'll figure it out OK.
If you need further explanation/help, post again
to this thread.

Best regards.
Geoff
 
Back
Top