Save and copy to new record

[brose]

Hello,



I am using the code below to copy values from the current record and populate to new record. It works great unless one of the fields is null. How can I evaluate for and skip over fields with null value?


Private Sub CopyNew_Click()
If Me.doValidate = False Then Exit Sub
DoCmd.RunCommand acCmdSaveRecord
Dim ID As Long

ID = Me.ID
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Provider = DLookup("Provider", "[2018]", "ID=" & ID)
DATE = DLookup("DATE", "[2018]", "ID=" & ID)
[Patient Number] = DLookup("[Patient Number]", "[2018]", "ID=" & ID)
[Pt FirstName] = DLookup("[Pt FirstName]", "[2018]", "ID=" & ID)
[Pt LastName] = DLookup("[Pt LastName]", "[2018]", "ID=" & ID)
DOB = DLookup("DOB", "[2018]", "ID=" & ID)
Cancer = DLookup("Cancer", "[2018]", "ID=" & ID)
[APPT Type (FU, DR, DC, NP)] = DLookup("[APPT Type (FU, DR, DC, NP)]", "[2018]", "ID=" & ID)
[MA Verified By] = DLookup("[MA Verified By]", "[2018]", "ID=" & ID)
[MA Verification Date] = DLookup("[MA Verification Date]", "[2018]", "ID=" & ID)
[Tracking #] = DLookup("[Tracking #]", "[2018]", "ID=" & ID)
[Status] = DLookup("Status", "[2018]", "ID=" & ID)





End Function

[Minty]

Wrap each Dlookup() in a Nz() function with a default replacement value for the null e.g.

Provider = Nz(DLookup("Provider", "[2018]", "ID=" & ID),"YourDefaultForProvider")