Stop error from occurring

[Stockwell43]

hello,

I have a button on my form that will carry over some field information from
the current record to a new record. Here is what I have at the very top of my
code for the form:
Option Compare Database
Private ctrl1 As String 'CustLast
Private ctrl2 As String 'DrawType
Private ctrl3 As String 'CloserName
'Private ctrl4 As String 'CustFirst
Private ctrl5 As String 'LoanNumber

And here is the code behind the button:

Private Sub cmdCopy_Click()
ctrl1 = CustLast
ctrl2 = DrawType
ctrl3 = CloserName
'ctrl4 = CustFirst
ctrl5 = LoanNumber
DoCmd.GoToRecord acForm, "frmconstructionsloans", acNewRec 'goes to new
record
CustLast = ctrl1
DrawType = ctrl2
CloserName = ctrl3
'CustFirst = ctrl4
LoanNumber = ctrl5
End Sub

It works fine except for one thing, if ALL the fields are not filled in
let's say DrawType is blank. When you click the button an error message pops
up saying: Run time error 94, invalid use of null.

How can I stop this from popping up? I am almost sure the users will for get
to fill in all four field and if this pops up it will show my code. Does
anyone have any ideas??

Thanks!!!

 

[boblarson]

Just use the NZ function to give an empty string:

for example - LoanNumber = Nz(ctrl5,"")

or if it is a number then you can use

LoanNumber = Nz(ctrl5,0)

or if you just want to keep it null if null then

If Not IsNull(ctrl5) Then LoanNumber = ctrl5

 

[John Spencer (MVP)]

Or Dim the variables as Variants so they can hold null values.

Private ctrl1 As Variant 'CustLast
Private ctrl2 As Variant 'DrawType
Private ctrl3 As Variant 'CloserName
'Private ctrl4 As Variant 'CustFirst
Private ctrl5 As Variant 'LoanNumber

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Stockwell43]

Hi Bob,

Thank you for replying. I tried to place the code behind the button and
comment out what I had. If the field is blank, it carries over blank but if
the field is filed in, it still carries over blank.

Did I do something wrong or place this in the wrong place?

Thanks!

 

[boblarson]

What is the actual code you wound up using? You need to ensure that the
names are actually the names you have.

 

[Stockwell43]

I used your code. I commented out Loan Number on my code and replaced it with
your Loan Number code exactly. Was it only suppose to go behind the button or
should I have done something else?

 

[Stockwell43]

Hi John,

So are you saying to replace your code with the code I have underneath
Option Compare Database or replce it with the code behind the button or both?

Thanks!!

 

[John Spencer (MVP)]

I'm saying that the minimal code change for your code would be the following.
Variants can store null values and string values. If you try to assign a
null to a variable typed as a string you will get the "Invalid use of Null" error.

Option Compare Database
Private ctrl1 As VARIANT 'CustLast
Private ctrl2 As VARIANT 'DrawType
Private ctrl3 As VARIANT 'CloserName
'Private ctrl4 As VARIANT 'CustFirst
Private ctrl5 As VARIANT 'LoanNumber

And here is the code behind the button:

Private Sub cmdCopy_Click()
ctrl1 = CustLast
ctrl2 = DrawType
ctrl3 = CloserName
'ctrl4 = CustFirst
ctrl5 = LoanNumber
'go to new record
DoCmd.GoToRecord acForm, "frmconstructionsloans", acNewRec
CustLast = ctrl1
DrawType = ctrl2
CloserName = ctrl3
'CustFirst = ctrl4
LoanNumber = ctrl5
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Stockwell43]

Got it!

Not sure what I didn't do the first time but I tried exact and works fine.
Thank you for your help!!