input box invalid use of null error

[Christine Vollberg via AccessMonster.com]

Ok guys you are the best, I have another issue. I am using an input box to
get a number to use in a loop. If no number is entered or the cancel
button is pushed it gives me an invalid use of null error, is there another
way to do this and check for nulls and cancel using the input box.

here is what Ii am using:

RepeatValue = InputBox("How Many Counts?", Counts)

 

[Brendan Reynolds]

The InputBox function can never return a Null value, Christine. It returns a
string, and a string can not be Null. If you press the Enter key without
typing anything, or click the Cancel button, the InputBox function returns
an empty string, not a Null. I don't know where your problem Null value is
coming from, but one thing is certain - it is *not* the return value of the
InputBox function. Look for it instead in the code before or after the call
to the InputBox function.

 

[Christine Vollberg via AccessMonster.com]

This is the code I am using

Private Sub cmdDupREc_Click()
Dim count As Integer
Dim sSql As String
Dim DupSql As String
Dim charseq As String
Dim DupSql2 As String
Dim DupSql3 As String
Dim DupSql4 As String
Dim RepeatValue As Integer

Dim db As DAO.Database

Set db = DBEngine(0)(0)

RepeatValue = InputBox("How Many Counts?", Counts)

charseq = CLng(DMax("[ChargeSeqNum]", "tblDefChargesSentence", "
[CaseNo]= '" & Me![CaseNo] & "' And [DefendantId]=" & Me![DefendantId])) + 1


count = RepeatValue - 1
Do While count > 0
DupSql = "INSERT INTO tblDefChargesSentence ( DefendantId,
CaseNo, ChargeCode, ChargeSeqNum, FiledOffense, LastName, FirstName" + IIf
(IsNull(SID), " ", ",SID") + IIf(IsNull(ATN), " ", ",ATN") + IIf(IsNull
(DateOfOffense), " ", ",DateOfOffense") + IIf(IsNull(DateOfArrest), " ",
",DateOfArrest") + IIf(IsNull(ArDate), " ", ",ArDate") + IIf(IsNull
(ConvictionDate), " ", ",ConvictionDate") + IIf(IsNull(PAttorney), " ",
",PAttorney") + IIf(IsNull(PAttorney2), " ", ",PAttorney2") + IIf(IsNull
(DAttorney), " ", ",DAttorney") + IIf(IsNull(DAttorney2), " ",
",DAttorney2")
DupSql2 = IIf(IsNull(TrialBy), " ", ",TrialBy") + IIf(IsNull
(DefPlea), " ", ",DefPlea") + IIf(IsNull(DateOfPlea), " ", ",DateOfPlea") +
IIf(IsNull(TypeChargeFM), " ", ",TypeChargeFM") + IIf(IsNull(ChargeTypeORA)
, " ", ",ChargeTypeORA") + ") values ( " + Str(DefendantId) + ",'" + CaseNo
+ "','" + ChargeCode + "','" + charseq + "','" + FiledOffense + "','" +
LastName + "','" + FirstName + "'" + IIf(IsNull(SID), " ", ",'" + SID + "'")
+ IIf(IsNull(ATN), " ", ",'" + ATN + "'")
DupSql3 = IIf(IsNull(DateOfOffense), " ", ",'" + Format
(DateOfOffense, "mm/dd/yyyy") + "'") + IIf(IsNull(DateOfArrest), " ", ",'"
+ Format(DateOfArrest, "mm/dd/yyyy") + "'") + IIf(IsNull(ArDate), " ", ",'"
+ Format(ArDate, "mm/dd/yyyy") + "'") + IIf(IsNull(ConvictionDate), " ",
",'" + Format(ConvictionDate, "mm/dd/yyyy") + "'") + IIf(IsNull(PAttorney),
" ", ",'" + PAttorney + "'") + IIf(IsNull(PAttorney2), " ", ",'" +
PAttorney2 + "'") + IIf(IsNull(DAttorney), " ", ",'" + DAttorney + "'")
DupSql4 = IIf(IsNull(DAttorney2), " ", ",'" + DAttorney2 + "'") +
IIf(IsNull(TrialBy), " ", ",'" + TrialBy + "'") + IIf(IsNull(DefPlea), " ",
",'" + DefPlea + "'") + IIf(IsNull(DateOfPlea), " ", ",'" + Format
(DateOfPlea, "mm/dd/yyyy") + "'") + IIf(IsNull(TypeChargeFM), " ", "," +
Str(TypeChargeFM)) + IIf(IsNull(ChargeTypeORA), " ", "," + Str
(ChargeTypeORA)) + ")"
sSql = DupSql + DupSql2 + DupSql3 + DupSql4
'display sql statement for testing purposes only
sql_text = sSql
db.Execute sSql, dbFailOnError
charseq = charseq + 1
count = count - 1
If count = 0 Then
Exit Do
End If

Loop
end sub

 

[Christine Vollberg via AccessMonster.com]

I am so sorry must be brain dead tonight it gives me a type mismatch error
not a invalid use of null error.

 

[Christine Vollberg via AccessMonster.com]

I am so sorry must be brain dead tonight it gives me a type mismatch error
not a invalid use of null error.

 

[Brendan Reynolds]

You're attempting to assign the return value (a String) to a variable that
is declared as Integer.

You can use the CInt() function to convert a string to an integer -
provided, that is, the string can be evaluated as a number. Try something
like ...

Dim UserResponse As String

UserResponse = InputBox("How many counts?", "Counts")
If IsNumeric (UserResponse) Then
RepeatValue = CInt(UserResponse)
Else
MsgBox "Please enter a number."
End If

 

[Christine Vollberg via AccessMonster.com]

Your awesome, works great thanks for the help.