Access checkbox yes/no sends or sends send batch of text to word..

  • Thread starter Thread starter bilbo+
  • Start date Start date
B

bilbo+

At the moment i use the following code to simply export some data from my
access form to my word template and fil lthe appropriate gaps...

e.g.

doc.Bookmarks("JobNo").Select

objWord.Selection.TypeText Forms!Mjobs!("JobNo")


what i want to do now is that if there is a checkbox (of which there will be
15) that has a "yes" tick then access will send a specific paragraph of text
to the word document. The reson for this is that at the moment i am sending
customer info to the word docu ment, now i want to select the 'items' in
access using checkboxs and this will export specific spec data to the word
document. The question is what code do i have to use for this and where can i
store the specification data? in the code or elsewhere?

Thanks in advance,

William Kingston

P.S. here is the entire code -

Option Compare Database
Public Function fProcedure()

Dim objWord As Word.Application

Dim doc As Word.Document

Dim bolOpenedWord As Boolean


On Error Resume Next

Set objWord = GetObject(, "Word.Application")

If Err.Number = 429 Then

Set objWord = CreateObject("Word.Application")

bolOpenedWord = True

End If

objWord.Visible = True

On Error GoTo 0



strPath = CurrentDb().Name

Do

lngInStr = InStr(lngInStr + 1, strPath, "\")

Loop While (InStr(lngInStr + 1, strPath, "\") <> 0)

strPath = Left(strPath, lngInStr)

strPath = strPath & "quotetemplate.dot"

Set doc = objWord.Documents.Add(strPath)




doc.Bookmarks("JobNo").Select

objWord.Selection.TypeText Forms!Mjobs!("JobNo")

doc.Bookmarks("Firstname").Select

objWord.Selection.TypeText Forms!Mjobs!("FirstName")

doc.Bookmarks("Lastname").Select

objWord.Selection.TypeText Forms!Mjobs!("LastName")

doc.Bookmarks("Company").Select

objWord.Selection.TypeText Forms!Mjobs!("CompanyName")

doc.Bookmarks("Hiredays").Select

objWord.Selection.TypeText Forms!Mjobs!("Days")

doc.Bookmarks("User").Select

objWord.Selection.TypeText CurrentUser()

If objWord.ActiveWindow.View.SplitSpecial = wdPaneNone Then
objWord.ActiveWindow.ActivePane.View.Type = wdPrintView
Else
objWord.ActiveWindow.View.Type = wdPrintView
End If


objWord.Activate


On Error Resume Next

strPathFolder = "\\Server\CKS Database\CKS\Data\" & Forms!Mjobs!("JobNo")
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir (strPathFolder)
MkDir (strPathFolder & "\Services")
MkDir (strPathFolder & "\Drawings")
MkDir (strPathFolder & "\Invoices")
MkDir (strPathFolder & "\Sales")
MkDir (strPathFolder & "\Suppliers")
MkDir (strPathFolder & "\Emails")
MkDir (strPathFolder & "\Emails\PKL")
MkDir (strPathFolder & "\Emails\Client")
MkDir (strPathFolder & "\Emails\Misc")
End If

strPathData = "\\Server\CKS Database\CKS\Data\" & Forms!Mjobs!("JobNo")
strPathDataFilename = "\" & "Quote 01." & Forms!Mjobs!("JobNo") & "." &
CurrentUser() & "." & Format(Date, "mm.yyyy") & ".doc"

If Dir(strPathData & strPathDataFilename) = "" Then
doc.SaveAs FileName:=strPathData & strPathDataFilename
Else
overwrite = MsgBox(prompt:=strPathData & strPathDataFilename & " already
exists, " & _
"would you like to overwrite", buttons:=vbOKCancel)
If overwrite = vbOK Then
doc.SaveAs FileName:=strPathData & strPathDataFilename
End If
End If







On Error GoTo 0

doc.Close False

Set doc = Nothing


If bolOpenedWord = True Then

objWord.Quit

End If

Set objWord = Nothing

End Function
 
It all starts with the data. What data do you have, and what table
structure?

It may be possible to use a query to get the "specific paragraph", but
without some idea of how your data is organized...?!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Im not quite sure what you're after! basically at the moment I have lots of
customer information and job information in the record and when i want to
'quote' a new client i click quote and it only inputs things like their name
and company etc using the code I pasted before. In this word document that
the information goes into I have a couple of pages of different pieces of
equipment and their spec which I delete as appropriate to what the client
wants. I thought it would be a lot easier to just have a quoting section on
their access record with checkboxes next to the 15 different items so that if
the client wants a quote for items 1 , 4 and 5 i tick the box next to them,
click make quote and it only puts in those 3 item specs... Does this make
more sense? If not let me know what else you need to help! Im really
struggling... Thanks,

Will
 
Right off the top of my head, I can (creatively) imagine four different ways
to organize data that I believe would be similar to what you've described.
.... and each different way would require a different approach to doing what
you're asking.

"Checkboxes" are controls on forms. The underlying data and its structure
(your table definitions) will contrain (or enable) different ways to get the
information you seem to be after.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Ok if i can explain how I would imagine it to work that may help... In my
mind for each checkbox that has a 'true' value the relevant spec will be sent
to the word doc. So just as JobNo is sent automatically to the worddoc to the
relevant bookmark so to would the true checkbox. So in my mind as opposed to
the other merges which are

doc.Bookmarks("JobNo").Select
in the merge it would say somethign like doc.Bookmarks("checkbox1").Select

and for the next bit...

something which would mean If Forms!Mjobs!("check1") = true then
objword.selection.typetext ("1 x Hood Dishwasher")

Am I being a bit clearer or making this more complicated...?

Thanks,

Will
 
Your descriptions seem to focus on "how do I do it this way", rather than
"what" or "why".

Hopefully one of the other newsgroup volunteers can address your issue.

At this point, I'm not sure I understand your data well enough to offer
"how" ideas...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top