Blank field problem when exporting with bookmarks

[phillsheen]

Hi,
Im using some Visual Basic code Ive cobbled together from this site to export some data from access to word using bookmarks. The code I have used works great apart from one little problem. If a field is left blank, which in some cases it will be, then I get a 'Type Mismatch' Error.

This is the code I have used :

Private Sub Command192_Click()
'Private Sub cmd_Word_Template_Click()
'Declare the follwing
Dim objWord As Object

'Set word as an application and make it invisible
Set objWord = CreateObject("Word.Application")
objWord.Visible = False 'True is visible

'path and name of the template your are using.
objWord.Documents.Add ("Y:\Letters\Chasing letters\autoletter.doc")

'This is for the bookmark that you created in the template
objWord.ActiveDocument.Bookmarks("chasefirstname").select

'This is the field in access that containts the data that has to be entered at the
'bookmark
objWord.Selection.Text = Forms![members]![FirstName]


'This is for the bookmark that you created in the template
objWord.ActiveDocument.Bookmarks("chaselastname").select

'This is the field in access that containts the data that has to be entered at the
'bookmark
objWord.Selection.Text = Forms![members]![Surname]


'This is for the bookmark that you created in the template
objWord.ActiveDocument.Bookmarks("chasesal").select

'This is the field in access that containts the data that has to be entered at the
'bookmark
objWord.Selection.Text = Forms![members]![Sal]


'This is for the bookmark that you created in the template
objWord.ActiveDocument.Bookmarks("chasehouseno").select

'This is the field in access that containts the data that has to be entered at the
'bookmark
objWord.Selection.Text = Forms![members]![House / Flat]


'This is for the bookmark that you created in the template
objWord.ActiveDocument.Bookmarks("chasestreet").select

'This is the field in access that containts the data that has to be entered at the
'bookmark
objWord.Selection.Text = Forms![members]![Street]

'Word (or the document that you created with the template, will now open)
objWord.Visible = True


Set objWord = Nothing

End Sub

Im sure Im missing just a little bit of code that will help me out but I have no idea what it is.

Cheers
Phill

 

[PeterF]

I think the 'Type Mismatch' error is because Word can't handle null values so changing your code to something like:

objWord.Selection.Text = Forms![members]![Street][COLOR="Red"] & ""[/COLOR]

should resolve your error.

By concatenating a field value with a ZLS it will always be a string.

 

[Guus2005]

I would use the Nz function because it is faster than concatenating a ZLS and it gives you the opportunity to add a default.

objWord.Selection.Text = Nz(Forms![members]![Street])

Share and Enjoy!