Invalid use of Null

[jmarcrum]

Hi all!

I have a form with a combo box. If the user forgets to select something from the combo box and hits the "Save" command button, an error pops up that says Run-time error '94': Invalid use of Null. What am i doing wrong? Here's my code...

Dim Opportunity As String
Dim message As String
 
cmbTeaching.SetFocus
Opportunity = cmbTeaching.Value
 
'Check if a teaching opportunity was selected; if not, ask the user to select one
If cmbTeaching = "" Then
    MsgBox ("Please select an Opportunity")
    Exit Sub
End If

Thanks in advance for your help!!!

[JKing]

Hi all!

I have a form with a combo box. If the user forgets to select something from the combo box and hits the "Save" command button, an error pops up that says Run-time error '94': Invalid use of Null. What am i doing wrong? Here's my code...

Dim Opportunity As String
Dim message As String
 
cmbTeaching.SetFocus
Opportunity = cmbTeaching.Value
 
'Check if a teaching opportunity was selected; if not, ask the user to select one
If cmbTeaching = "" Then
    MsgBox ("Please select an Opportunity")
    Exit Sub
End If

Thanks in advance for your help!!!

Hi there,

You were trying to set Opportunity to the value of a combobox before validating the combobox. You need to check for null first and then set Opportunity.

Private Sub Command0_Click()
 
Dim Opportunity As String
 
Dim message As String
 
Me.cmbTeaching.SetFocus
 
'Check if a teaching opportunity was selected; if not, ask the user to select one
 
If IsNull(Me.cmbTeaching) Then
 
    MsgBox ("Please select an Opportunity")
 
    Exit Sub
 
Else
 
    Opportunity = Me.cmbTeaching.Value
 
End If
 

[jmarcrum]

Hi there,

You were trying to set Opportunity to the value of a combobox before validating the combobox. You need to check for null first and then set Opportunity.

Private Sub Command0_Click()
 
Dim Opportunity As String
 
Dim message As String
 
Me.cmbTeaching.SetFocus
 
'Check if a teaching opportunity was selected; if not, ask the user to select one
 
If IsNull(Me.cmbTeaching) Then
 
    MsgBox ("Please select an Opportunity")
 
    Exit Sub
 
Else
 
    Opportunity = Me.cmbTeaching.Value
 
End If
 

I changed my code to that and it threw a different error. "the expression On Click you entered as the event property setting produced the following error: Method or data member not found.

Visual Basic for Applications (VBA) encountered a problem while attempting to access a property or method. The problem may be one of the following:
A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base article 283806.
An Expression is misspelled.
Check all expressions used in event properties for correct spelling.
A user-defined function is declared as a sub or as a private function in a module.
Expressions can resolve a user-defined function only if the function is declared as one of the following:
A public function in a module
A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8 update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function properly when security is set to Medium or High. To obtain the latest version of Microsoft Jet, go to Windows Update.

[jmarcrum]

does anyone know how to fix this?

[JKing]

I think that maybe a copy and paste error on my part.

I had made a little mock up button on a form to test your scenario and ensure accuracy of my response. However upon copy and pasting to the forum I accidentally included the "Private Sub Command0_Click".

Try removing the "Private Sub Command0_Click" and you should be fine. If not post the code you are now using.

[jmarcrum]

I think that maybe a copy and paste error on my part.

I had made a little mock up button on a form to test your scenario and ensure accuracy of my response. However upon copy and pasting to the forum I accidentally included the "Private Sub Command0_Click".

Try removing the "Private Sub Command0_Click" and you should be fine. If not post the code you are now using.

I didn't paste that part of the code. The error is coming from the code you wrote... Here's what I have now....

Private Sub cmdSaveClose_Click()
'*************************************************************'
'Author:                         '
'Date: 2/26/2008                                              '
'Desc: Assign a new Opportunity to the selected member        '
'Modifications:                                               '
'                                                             '
'*************************************************************'
Dim Opportunity As String
Dim message As String
 
 Me.cmbTeaching.SetFocus
 
'Check if a teaching opportunity was selected; if not, ask the user to select one 
If IsNull(Me.cmbTeaching) Then 
    MsgBox ("Please select an Opportunity")
    Exit Sub
Else
    Opportunity = Me.cmbTeaching.Value
End If
 
'Make sure the Opportunity does not already exsist
If OpportunityExists(Me.cmbTeaching.Value, Forms!frmMenMemberInfo!MemberID.Value) Then
    MsgBox "This Opportunity is already assigned to this member."
    Exit Sub
End If
 
'Save the Record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
'Requery the form
Forms!frmMenMemberInfo!frmMenTeaching.Form.RecordSource = "SELECT tblMenDuties.Member, tblMenDuties.MenTeachingOpportunityID, tblMenTeachingOpportunities.MenTeachingOpportunity, tblMenDuties.MemberID FROM tblMenDuties INNER JOIN tblMenTeachingOpportunities ON tblMenDuties.MenTeachingOpportunityID = tblMenTeachingOpportunities.MenTeachingOpportunityID WHERE (((tblMenDuties.Member)=[Forms]![frmMenMemberInfo]![Member]));"
 
DoCmd.Close
 
Exit Sub
 
End Sub
 
Public Function OpportunityExists(MenTeachingOpportunityID As Integer, MemberID As Integer) As Boolean
'*************************************************************'
'Author:                                '
'Date: 2/26/2008                                              '
'Desc: Returns True if Opportunity passed in exists in        '
'      tblMenDuties; False otherwise                          '
'Modifications:                                               '
'                                                             '
'*************************************************************'
Dim strSQL As String
Dim dbs_curr As Database
Dim record As Recordset
 
Set dbs_curr = CurrentDb
 
strSQL = "SELECT tblMenDuties.* FROM tblMenDuties WHERE (((tblMenDuties.MenTeachingOpportunityID)=" & MenTeachingOpportunityID & ") AND ((tblMenDuties.MemberID)=" & MemberID & "));"
Set record = dbs_curr.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)
 
If Not record.EOF Then
    OpportunityExists = True
Else
    OpportunityExists = False
End If
 
End Function

[Megalog]

Maybe it's because 'Opportunity' is supposed to be a string, yet you're assigning an integer to it? Although I would imagine the error you receive would be about a data type mismatch. You could get rid of that alltogether and use:

 
'Check if a teaching opportunity was selected; if not, ask the user to select one 
If IsNull(Me.cmbTeaching) Then 
    MsgBox ("Please select an Opportunity")
    Exit Sub
Else
    'Make sure the Opportunity does not already exsist
    If OpportunityExists(Me.cmbTeaching.Value, Forms!frmMenMemberInfo!MemberID.Value) Then
        MsgBox "This Opportunity is already assigned to this member."
        Exit Sub
    End If
End If
 
 

[jmarcrum]

Maybe it's because 'Opportunity' is supposed to be a string, yet you're assigning an integer to it? Although I would imagine the error you receive would be about a data type mismatch. You could get rid of that alltogether and use:

 
'Check if a teaching opportunity was selected; if not, ask the user to select one 
If IsNull(Me.cmbTeaching) Then 
    MsgBox ("Please select an Opportunity")
    Exit Sub
Else
    'Make sure the Opportunity does not already exsist
    If OpportunityExists(Me.cmbTeaching.Value, Forms!frmMenMemberInfo!MemberID.Value) Then
        MsgBox "This Opportunity is already assigned to this member."
        Exit Sub
    End If
End If
 
 

YOU ARE A GENIOUS!!!!

WORKED LIKE A CHARM!!!

Thanks for yall's help! That was takin me forever!!