Handleing a null in this string... 1

[WallT]

I got a lot of help from PHV on this originally thread702-1374042. But I am stuck now. I use the following to transfer data from one record in a subform to another. The subform is "singleform". And I use a command button if I want the same data transferred to the new record as follows:

OnClick:
Me.[txtServiceAddress].DefaultValue = "'" & Me.[txtServiceAddress].Value & "'"
Me.[txtServiceCity].DefaultValue = "'" & Me.[txtServiceCity].Value & "'"
DoCmd.GoToRecord , , acNewRec
Me.txtServiceID.SetFocus
End Sub

It transfers the data fine, however, if one of these fields is null I get the following error when I try to save the record:

Field 'tbl_Locations.ServiceCity' cannot be a zero-length string.

They are not mandatory fields and some fields are often left blank.

How can I handle the NULL in this case. I don't understand why transferring a NULL field suddenly makes it want to force data in that field. Thanks for any help.

 

[PHV]

A starting point:
If Trim(Me![txtServiceAddress] & "") <> "" Then
Me![txtServiceAddress].DefaultValue = "'" & Me![txtServiceAddress] & "'"
Else
Me![txtServiceAddress].DefaultValue = Null
End If

Another possible way:
Me![txtServiceCity].DefaultValue = "'" [!]+[/!] Me![txtServiceCity] [!]+[/!] "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WallT]

I tried:

Me![txtServiceCity].DefaultValue = "'" + Me![txtServiceCity] + "'"

Got this error: Invalid use of Null94

Not sure why. I am going to try the "If" method. Maybe even use DLookUp after the previous record is saved or something like that.

 

[PHV]

You may try this:

Me![txtServiceCity].DefaultValue = Nz("'" + Me![txtServiceCity] + "'", "")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WallT]

Tried everything...this defaultValue does not like Nulls. I am going to look into a way to use DlookUp once record one is saved. Maybe a check box that says DLookUp old location info when I click "Add New Record". You've been a lot of help. I thought the Nz would work, but I get the same errors.

 

[PHV]

My last suggestion (7 Jun 07 13:56) don't put Null as default value.
Have you tried it ?

 

[WallT]

Here's what I finally got to work:

If Not (IsNull(Me.txtSuiteApt)) Then
Me.txtSuiteApt.DefaultValue = "'" & Me.txtSuiteApt.Value & "'"
Else
Me.txtSuiteApt.DefaultValue = ""
End If
If Not (IsNull(Me.txtServiceAddress)) Then
Me.txtServiceAddress.DefaultValue = "'" & Me.txtServiceAddress.Value & "'"
Else
Me.txtServiceAddress.DefaultValue = ""
End If
DoCmd.GoToRecord , , acNewRec
Me.cboServerID.SetFocus