Invalid use of Null

[phillbaker]

i have this form in which when you press a button it opens up word and using bookmarks pulls in the data from the form. But if someone hasnt filled in one of the fields it brings up invalid use of null when i press the button how can i stop this.

i have pasted the code below. can anyone help

The code to do this is below:

Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
'Copy the Photo control on the Employees form.
'DoCmd.GoToControl "Photo"
'DoCmd.RunCommand acCmdCopy
'Start Microsoft Word 2003.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
'Open the document.
.Documents.Open ("\\server\Database\MyMerge.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("First").Select
.Selection.Text = (CStr(Forms!Clients!CFirstName))
.ActiveDocument.Bookmarks("Last").Select
.Selection.Text = (CStr(Forms!Clients!CLastName))
'.ActiveDocument.Bookmarks("HouseName").Select
'.Selection.Text = (CStr(Forms!Clients![CHouse Name]))
.ActiveDocument.Bookmarks("Street").Select
.Selection.Text = (CStr(Forms!Clients!CStreet))
.ActiveDocument.Bookmarks("Village").Select
.Selection.Text = (CStr(Forms!Clients!CVillage))
.ActiveDocument.Bookmarks("PostalTown").Select
.Selection.Text = (CStr(Forms!Clients![CPostal Town]))
.ActiveDocument.Bookmarks("PostCode").Select
.Selection.Text = (CStr(Forms!Clients!CPostCode))
.ActiveDocument.Bookmarks("County").Select
.Selection.Text = (CStr(Forms!Clients!CCounty))




'Paste the photo.
'.ActiveDocument.Bookmarks("Photo").Select
'.Selection.Paste
End With
'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
'objWord.ActiveDocument.PrintOut Background:=False
'Close the document without saving changes.
'objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'Quit Microsoft Word and release the object variable.
'objWord.Quit
'Set objWord = Nothing
Exit Sub
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
'If the Photo field is empty.
ElseIf Err.Number = 2046 Then
MsgBox "Please add a photo to this record and try again."
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub

 

[wazz]

.Selection.Text = Nz((CStr(Forms!Clients!CFirstName)),"")

nz function will transform a null to, basically, whatever you want. in this case, if it is null, make it a null string (""). i don't think that will create the same issue for you.

 

[namliam]

0) Please if you post code use [ code ] and [/ code ] around it (without the spaces)
That way your code is much more readable, luckily I didnt need it this time.

Two ways.
1)
Prior to running your code, make sure all fields are filled in and prompt the user if this fails

If Isnull(yourfield) then 
    msgbox "Yourfield is empty"
    exit sub
End If

2)
If the empty field is to be allowed replace the null value for an empty string using the NZ function around the fields that can be null.

 

[phillbaker]

Re: NZ Function

Hi
I tried using

.Selection.Text = Nz((CStr(Forms!Clients!CFirstName)),"") but this still throws up the error.

 

[wazz]

just played around with it and found a few things.

- your error handler worked for me, didn't have a problem;
- the code i posted should have been bracketed a bit differently;
- the real problem was with CStr: if you try to use it on a null it fires an error.

.Selection.Text = Nz(Forms!frmClients!CLastName, "")

should do it.