looping (1 Viewer)

[historyb]

Hi all,

I have a dilemma. I have an Access form that I need to auto fill multiple forms. These forms may not have all the bookmarks so I want to use a loop but I don't know which one to use. I know about IF/THEN, IF/ELSE, FOR/NEXT, CASE but I am stuck on which on. I need the loop to go to each one and see if it's there and if not then keep going and filling in the other bookmarks if they exist.

I hope that was not to muddled. Here is my code:

Private Sub cmd_Word_Click()

'Declare Variables
Dim sAccessAddress As String
Dim Salutation As String

'Build sAccessAddress
sAccessAddress = FirstName & " " & LastName & vbCrLf & Address & vbCrLf & StateOrProvince & " " & Zipcode

'Build sAccessSalutation
Salutation = FirstName & " " & LastName

'Declare and set Word object variables
Dim Wrd As Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify Path to Template
Dim sMergeDoc As String
sMergeDoc = Application.CurrentProject.Path & "\WordMergeDocument.dotx"

'Open Word using template and make Word visible
Wrd.Documents.Add sMergeDoc
Wrd.Visible = True

'Replace Bookmarks with Values
[COLOR=Red]With Wrd.ActiveDocument.Bookmarks
.Item("CurrentDate").Range.Text = Date
.Item("AccessAddress").Range.Text = sAccessAddress
.Item("Salutation").Range.Text = Salutation
End With[/COLOR]

'Open in PrintPreview mode, let user print
Wrd.ActiveDocument.PrintPreview

'Clean Up code
Set Wrd = Nothing
End Sub

In red is where I want to put a loop.

 

[historyb]

Anyone? I would be grateful for the help

 

[mdlueck]

Just guessing, you might have a similar situation to where in a program I developed I needed to conditionally set parameters each the correct way. I used individual IF statements to handle each specific parameter object correctly.

    If Me.quotenumber = "" Then
      .Parameters("@quotenumber").Value = Empty
    Else
      .Parameters("@quotenumber").Value = Me.quotenumber
    End If

    .Parameters("@quotedate").Type = adDBTimeStamp
    If Me.quotedate = "" Then
      .Parameters("@quotedate").Value = Empty
    Else
      .Parameters("@quotedate").Value = Me.quotedate
    End If

    If Me.poprice = 0 Then
      .Parameters("@poprice").Value = Empty
    Else
      .Parameters("@poprice").Value = Me.poprice
    End If

 

[historyb]

Thank you. That is what I had in mind to do but I wanted to make sure I was not overlooking something, I tend to make things more complicated than needed.

 

[historyb]

As an update I tried this:

'Replace Bookmarks with Values
With Wrd.ActiveDocument.Bookmarks

   If .Item("CurrentDate") Then
    .Item("CurrentDate").Range.Text = Date
   End If
    
.Item("AccessAddress").Range.Text = sAccessAddress
.Item("Salutation").Range.Text = Salutation
End With

This gave me an error of type 13 mismatched. I think I can do it than I keep not getting it I am sorry if I am imposing on anyone here

 

[PeterF]

Which line is higlighted in the debugger (you do not show the whole code)?
How did you declare your recordset, the type 13 mismatch is to my knowledge a result of mixing ADO and DAO.
You need to declare your recordset explicit like:
Dim RS as DAO.Recordset '(or ADODB.Recordset)

 

[historyb]

Post one shows the whole code, sorry about not highlighting the error above I highlighted it in red. It worked without the loop that I have highlighted in red, so do I have to do ADO or DAO for the loop to work because I didn't have to have that before?

'Replace Bookmarks with Values
With Wrd.ActiveDocument.Bookmarks

  [COLOR=Red] If .Item("CurrentDate") Then[/COLOR]     [COLOR=Red]<---- Here is the error[/COLOR]
    .Item("CurrentDate").Range.Text = Date
   End If
    
.Item("AccessAddress").Range.Text = sAccessAddress
.Item("Salutation").Range.Text = Salutation
End With

 

[mdlueck]

so do I have to do ADO or DAO for the loop to work because I didn't have to have that before?

Your code does not appear to do anything DB related. You appear to be automating Word via Automation.

'Declare and set Word object variables
Dim Wrd As Word.Application
Set Wrd = CreateObject("Word.Application")

I have not automated Word, so can not speak on that topic. I would check the syntax support for Word.Application objects.

 

[historyb]

Your code does not appear to do anything DB related. You appear to be automating Word via Automation.

Yes that is what I am doing. There is nothing DB related just throwing text into Word via Automation