Invalid use of Null Value with INSERT command 1

[JAES]

I use the following code to take information from a group of unbound text boxes on a form and insert them into a table (tabActivity). My problem is that if any of the number variables are blank I get the Invalid use of a Null error. I have to make sure zero's are in all number fields for the Insert command to work.

Also, should I be setting the "v" variables based on the value of the text box (Me.) or use the Me. value directly in the INSERT command.

Thank you again for listening.

Jeff


Private Sub SubmitDaily_Click()
On Error GoTo Err_SubmitDaily_Click

Dim vAgentName, vCaseFileNumber, vlocation, vDrug, vActivity As String
Dim vDate As Date
Dim vRegHours, vOTHours, vFlexUsed, vCTOUsed, vSickUsed, vVacUsed, VWeekNum, vMonthNum, vFlexEarned, vFlexTaken, vCTOEarned, vCTOTaken, vVacationTaken, vSickTaken As Double

'Check to make sure a name is entered
If IsNull(inAgentName) Then
response = MsgBox("Please enter your name first", vbInformation)
inAgentName.SetFocus
Exit Sub
End If

vActivity = Me.inActivity
vAgentName = Me.inAgentName
vDate = Me.inDate
VWeekNum = Me.Text41
vMonthNum = Me.Text43
vRegHours = Me.inRegHours
vOTHours = Me.inOTHours
vCaseFileNumber = Me.inCaseFileNumber
vlocation = Me.inLocation
vDrug = Me.inDrug
vFlexEarned = Me.inFlexEarned
vFlexTaken = Me.inFlexTaken
vCTOEarned = Me.inCTOEarned
vCTOTaken = Me.inCTOTaken
vVacationTaken = Me.inVacationTaken
vSickTaken = Me.inSickTaken

'Insert all information into the tabActivity table
DoCmd.RunSQL "INSERT INTO tabActivity (actDate, actWeekNum, actMonthNum, actOTHours, actRegHours, actFlexUsed, actCTOUsed, actVacUsed, actSicUsed, actCTOEarned, actFlexearned, actCaseFileNumber, actLocation, actDrug, actActivity, actAgentName) VALUES ('" & vDate & "', " & VWeekNum & "," & vMonthNum & ", " & vOTHours & "," & vRegHours & "," & vFlexTaken & ", " & vCTOTaken & ", " & vVacationTaken & " , " & vSickTaken & ", " & vCTOEarned & ", " & vFlexEarned & ", '" & vCaseFileNumber & "', '" & vlocation & "', '" & vDrug & "', '" & vActivity & "', '" & vAgentName & "')"

'Requery Weekly and Monthly sub forms and clear all but name and date fields
Forms!frmTimeEntry!frmWeeklyActivity.Requery
Forms!frmTimeEntry!frmMonthlyActivity.Requery
Me.inActivity = " "
Me.inCaseFileNumber = " "
Me.inDrug = " "
Me.inLocation = " "
Me.inOTHours = 0
Me.inRegHours = 0
Me.inCTOEarned = 0
Me.inCTOTaken = 0
Me.inFlexEarned = 0
Me.inFlexTaken = 0
Me.inSickTaken = 0
Me.inVacationTaken = 0

Exit_SubmitDaily_Click:
Exit Sub

Err_SubmitDaily_Click:
MsgBox Err.Description
Resume Exit_SubmitDaily_Click

End Sub

 

[pbaldy]

You can use the Nz() function to replace Null with zero. Using variables vs the form reference directly in the insert is probably a style/personal preference. Generally, if I'm only going to use the value once I don't bother with a variable.

FYI, you realize that with this you've declared 4 Variants and 1 String, not 5 Strings?

Dim vAgentName, vCaseFileNumber, vlocation, vDrug, vActivity As String

Paul
MS Access MVP 2007/2008

 

[JAES]

Thanks pbaldy, I thought I could declare multiple variables on one line. Maybe that is why I get a Null error sometimes on vActivity! Oops.

What I was trying to do was eliminate the zeros from showing on the form. The Nz() function seemed to cure the NULL error and the invalid syntax on INSERT INTO command error I was getting but how can I suppress the zeros from showing on the form or report?
Thanks

 

[pbaldy]

You can declare multiple variables on one line, but you have to specify each type:

Dim vAgentName As String, vCaseFileNumber As String, vlocation As String, vDrug As String, vActivity As String

A string variable can't accept a Null, so you would get an error on that. I typically hide zeros with the Format property of the textbox. For numeric data types, it actually has 4 parts (positive, negative, zero & null), so a format like this will not display a zero:

#;(#);"";""

Paul
MS Access MVP 2007/2008

 

[JAES]

Sorry pbaldy, I forgot to thank you for your help. The format property fixed my problem of displaying zeros too.

Jeff

 

[pbaldy]

No problem; glad we got it sorted out.

Paul
MS Access MVP 2007/2008