Full Version: Insert
UtterAccess Forums > Microsoft® Access > Access Forms
mike60smart
Hi Everyone

I have an Unbound textbox named "txtExtraYear"

I have the code below in the ONClick Event of a Command Button which will Insert a Blank record OK:

What I want to be able to do is check that the Textbox contains a Value. If it does not then display a message prompt and set focus back to the Control.

If it does contain a Value then run the Insert.

I tried inserting the following additional lines before the CurrentDb.Execute Line but it gives me the error:-

Invalid use of Null

If IsNull(Me.txtExtraYear) Then
MsgBox "You need to enter a Year and then try again", vbCritical
End If
Me.txtExtraYear.SetFocus


How can I modify the following code??

Any help appreciated.

The code is:-

Private Sub cmd1Blank_Click()
On Error GoTo Err_cmd1Blank_Click

Dim intMembershipID As Integer
Dim intMemNo As Long
Dim intYearofSubscription As String

intMembershipID = Me.MembershipID
intMemNo = Me.MemNo
intYearofSubscription = Me.txtExtraYear


CurrentDb.Execute "INSERT INTO tblYearlyPaymentDetails ( MembershipID, MemNo, YearOfSubscription) " _
& "Values ( " & Me!MembershipID & ", " & Me!MemNo & ", '" & Me!txtExtraYear & "');", dbFailOnError
Debug.Print

Me.[frmAnnualSubsSubform].Requery

Exit_cmd1Blank_Click:
Exit Sub

Err_cmd1Blank_Click:
MsgBox Err.Description
Resume Exit_cmd1Blank_Click


End Sub
Doug Steele
CODE
Private Sub cmd1Blank_Click()
On Error GoTo Err_cmd1Blank_Click
  
Dim strSQL As String
  
  If IsNull(Me.txtExtraYear) = True Then
    MsgBox "You need to enter a Year and then try again", vbCritical
    Me.txtExtraYear.SetFocus
  Else
    strSQL = "INSERT INTO tblYearlyPaymentDetails ( MembershipID, MemNo, YearOfSubscription) " _
      & "Values ( " & Me!MembershipID & ", " & Me!MemNo & ", '" & Me!txtExtraYear & "');"
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    Me.[frmAnnualSubsSubform].Requery
  End If
  
Exit_cmd1Blank_Click:
  Exit Sub
  
Err_cmd1Blank_Click:
MsgBox Err.Description
  Resume Exit_cmd1Blank_Click
  
End Sub


No point having intMembershipID, intMemNo and intYearofSubscription if you don't use them!
mike60smart
Hi Doug

Tried that and still the same error?
Doug Steele
You sure the error is coming from that routine? Doesn't make sense to me...

Try putting an Exit Sub right before the If statement. If you still get the error, it's not from that routine.
mike60smart
Hi Doug

Did that and still same error.

I then commented out the following Lines of Code:-

' If IsNull(Me.txtExtraYear) = True Then
' MsgBox "You need to enter a Year and then try again", vbCritical
' Me.txtExtraYear.SetFocus
' Else

Entered a Year in the Unbound Textbox and Run the Code and it inserts as expected.
Doug Steele
Very odd.

See whether it's the SetFocus line it's complaining about.

BTW, is this a compile error or a runtime error?
mike60smart
Hi Doug

Yes Very Odd indead.

I commented out the SetFocus line and still the same error.

Click to view attachment
Doug Steele
One last suggestion to try, and then I'm out of ideas for now.

Try changing the two occurrences of Me.txtExtraYear to Me!txtExtraYear.

If that still doesn't work, can you create a small sample database with just the one form, and whatever else is needed to support it? (Include some instructions of how to use the form if it isn't obvious, please!)
Larry Larsen
Hi Mike

May be worth a try, changing this line:
CODE
If IsNull(Me.txtExtraYear) = True Then


to
CODE
If IsNull(Me.txtExtraYear) Then


HTH's
thumbup.gif
Doug Steele
Curious as to why you'd suggest that, Larry.

In actual fact, there was a bug in earlier versions of Access where you had to include the = True (or = False), or Access would be unable to shut down properly.
Larry Larsen
Hi Doug

"Old-school".. again interesting which versions..!!

On reflection there are two questions.. IF & IsNull and IF is asking for either a True or False.

thumbup.gif
Doug Steele
Afraid I don't remember which versions, but the issue was simply including a function call that evaluated to True or False without including the = True or = False.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.