Passing Ms Word Data To Access

[A_Classic_Man]

I have built a MS Word 97 form that sends data in the bookmarks to a
table in Access 97 when I click a command button on the Word form.
With the help of Robert Morley, Thanks Rob, I am able to get the data
into the Access table. The problem is that if a bookmark is a Date/
Time or Number and contains no data, I get the run time error 13 "Type
Mismatch" message. If the bookmarks contain data or is a text field,
the data fills the Access table fields just fine. I have placed data
in all of the bookmarks and everything works.

What code do I need to add to get this working

I'm also wondering if the same thing will happen with the text
bookmarks that have no data.

Many thanks to all of you guys and gals who so freely contribute your
knowledge to help people like me who know just enough to be dangerous.
Your help has gotten me out of many a jam.

Ron

Here's the code

Private Sub cmdSendToAccess_Click()

Dim strfldPerAssign As String '(Text field)
Dim strfldAccNum As Long '(Text field)
Dim strfldEventNum As String '(Text field-contains numbers and
letters)
Dim strfldEventDate As Date '(Date/Time field)
Dim strfldReqDate As Date '(Date/Time field)
Dim strfldCompleted As String '(Text field)
Dim strfldApp1 As String '(Text field)
Dim strfldApp1Shift As String '(Text field)
Dim strfldStartTime1 As Date '(Date/Time field)
Dim strfldEndTime1 As Date '(Date/Time field)
Dim strfldTotalTime1 As Date '(Date/Time field)

Set ThisDoc = ActiveDocument
strfldPerAssign = ThisDoc.FormFields("fldPerAssign").Result
strfldAccNum = ThisDoc.FormFields("fldAccNum").Result
strfldEventNum = ThisDoc.FormFields("fldEventNum").Result
strfldEventDate = ThisDoc.FormFields("fldEventDate").Result
strfldReqDate = ThisDoc.FormFields("fldReqDate").Result
strfldCompleted = ThisDoc.FormFields("fldCompleted").Result
strfldApp1 = ThisDoc.FormFields("fldApp1").Result
strfldApp1Shift = ThisDoc.FormFields("fldApp1Shift").Result
strfldStartTime1 = ThisDoc.FormFields("fldStartTime1").Result
strfldEndTime1 = ThisDoc.FormFields("fldEndTime1").Result
strfldTotalTime1 = ThisDoc.FormFields("fldTotalTime1").Result

Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set MyDB = wrkjet.OpenDatabase("E:\App\WorkCompleted")
Set MyTbl = MyDB.OpenRecordset("JobAssignments")

With MyTbl
.AddNew

!PerAssign = strfldPerAssign
!accNum = CLng(strfldAccNum)
!EventNum = strfldEventNum
!EventDate = CDate(strfldEventDate)
!ReqDate = CDate(strfldReqDate)
!Completed = strfldCompleted
!App1 = strfldApp1
!App1Shift = strfldApp1Shift
!StartTime1 = CDate(strfldStartTime1)
!EndTime1 = CDate(strfldEndTime1)
!TotalTime1 = CDate(strfldTotalTime1)

.Update
End With

Set MyTbl = Nothing
Set MyDB = Nothing
Set wrkjet = Nothing

End Sub

 

[Doug Robbins - Word MVP]

Use and If...End If construction

If strfldEventDate <> "" Then
!EventDate = CDate(strfldEventDate)
End If

--
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

 

[A_Classic_Man]

Use and If...End If construction

If strfldEventDate <> "" Then
!EventDate = CDate(strfldEventDate)
End If

--
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


















- Show quoted text -

Hello Doug

I believe I may have sent a message to the wrong place. Sorry

I did as you suggested and continue to get the same error message.

Any ideas? I checked the Word bookmarks and the Access table fields
and the data types seem to be correct.

Would this work better if the data was passed to Access through a
command in Access instead of Word?

Thanks
Ron

 

[Doug Robbins - Word MVP]

Looking more closely at your code, you would probably need

If IsDate(ThisDoc.FormFields("fldEventDate").Result) then
strfldEventDate = ThisDoc.FormFields("fldEventDate").Result
End if

It is probably the

strfldEventDate = ThisDoc.FormFields("fldEventDate").Result

that is causing an error in the first place because you are trying to set
something that is not a date to a Date type variable.

There is really no need to declare all of those variable and assign data to
them.

--
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

 

[A_Classic_Man]

Looking more closely at your code, you would probably need

If IsDate(ThisDoc.FormFields("fldEventDate").Result) then
strfldEventDate = ThisDoc.FormFields("fldEventDate").Result
End if

It is probably the

strfldEventDate = ThisDoc.FormFields("fldEventDate").Result

that is causing an error in the first place because you are trying to set
something that is not a date to a Date type variable.

There is really no need to declare all of those variable and assign data to
them.

--
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












- Show quoted text -

Hello Again Doug

With your help, everything works.(Almost)
One last request. (I hope)
If a date bookmark has no date in it, what I get in the Access table
is 12/30/99. The time bookmarks return 00:00 which I can live with.

I tried removing the bookmarks that contained no data but that
requires the form to be unprotected.

What code do I need to add to skip over bookmarks that contain no data
as the data is added to the Access table?

Thanks again
Ron

 

[Doug Robbins - Word MVP]

Do both of the things that I suggested.

--
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

 

[Doug Robbins - Word MVP]

Or get rid of all of the data strings and assignment of values to them use
my first suggestion. Substituting the data string by corresponding
FormField .Result.

--
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

 

[A_Classic_Man]

Or get rid of all of the data strings and assignment of values to them use
my first suggestion. Substituting the data string by corresponding
FormField .Result.

--
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











- Show quoted text -

Doug
This did the trick. Thanks for your patience and help.
Ron

If IsDate(ThisDoc.FormFields("fldEventDate").Result) Then
fldEventDate = ThisDoc.FormFields("fldEventDate").Result
End If

AND

If fldEventDate <> "" Then
!EventDate = CDate(fldEventDate)
End If