Re: Passing Ms Word Data To Access by A_Classic_Man
A_Classic_Man
Mon Nov 26 10:02:26 PST 2007
On Nov 26, 2:54 am, "Doug Robbins - Word MVP"
<d...@REMOVECAPSmvps.org> wrote:
> 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" <rmerk...@cox.net> wrote in message
>
> news:32704e03-c399-4b32-b1fe-3a3eda0f54cd@v4g2000hsf.googlegroups.com...
>
>
>
> > On Nov 24, 3:46 pm, "Doug Robbins - Word MVP"
> > <d...@REMOVECAPSmvps.org> wrote:
> >> 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" <rmerk...@cox.net> wrote in message
>
> >>news:80bc87a7-2bed-4057-b3a5-14462c56fbf0@b15g2000hsa.googlegroups.com...
>
> >> > On Nov 24, 4:48 am, "Doug Robbins - Word MVP"
> >> > <d...@REMOVECAPSmvps.org> wrote:
> >> >> 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" <rmerk...@cox.net> wrote in message
>
> >> >>news:1b8f27e0-296f-46db-aee5-22b57a2882d2@d4g2000prg.googlegroups.com...
>
> >> >> >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- Hide quoted text -
>
> >> >> - 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- Hide quoted text -
>
> >> - 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- Hide quoted text -
>
> - 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