Run Time error '94'

[bavj]

Hello,

I have written a code for preventing data entry. But I am getting a
"Run Time error '94': Invalid use of Null"

The code I have written is:

Private Sub Mobile_Phone_AfterUpdate()
Dim NewMobile_Phone As String
Dim stLinkCriteria As String
Dim ClientID As Integer

NewMobile_Phone = Me.Mobile_Phone.Value
stLinkCriteria = "[Mobile_Phone] = " & "'" & NewMobile_Phone & "'"

If Me.Mobile_Phone = DLookup("[Mobile_Phone]", "tblClient", stLinkCriteria) Then
MsgBox "This client " & NewMobile_Phone & ", has already been entered in database." _
& vbCr & vbCr & "Please check client details again.", vbInformation, "Duplicate Information"
Me.Undo
End If

ClientID = DLookup("[Client_ID]", "tblClient", stLinkCriteria)
Me.DataEntry = False
DoCmd.FindRecord ClientID, , , , , acCurrent

End Sub

Please help me solve this problem

 

[TJPoorman]

What line are you getting the error on?

 

[pr2-eugin]

DLookup will return a Null value if the criteria is not met. Since ClientID is declared as Integer, when you try to assign it with a Null it will throw the Invalid use of Null error.

To overcome this scenario, you need to either declare the ClientID as Variant type or ecnclose the DLookUp with a Nz function.

Private Sub Mobile_Phone_AfterUpdate()
    Dim NewMobile_Phone As String
    Dim stLinkCriteria As String
    Dim ClientID As Integer

    NewMobile_Phone = Me.Mobile_Phone.Value
    stLinkCriteria = "[Mobile_Phone] = '" & NewMobile_Phone & "'"

    If Me.Mobile_Phone = [COLOR=Red][B]Nz([/B][/COLOR]DLookup("[Mobile_Phone]", "tblClient", stLinkCriteria)[B][COLOR=Red], "NA")[/COLOR][/B] Then
        MsgBox "This client " & NewMobile_Phone & ", has already been entered in database." & vbCr & vbCr & _
               "Please check client details again.", vbInformation, "Duplicate Information"
        Me.Undo
    [COLOR=Red][B]Else[/B][/COLOR]
        ClientID = [COLOR=Red][B]Nz([/B][/COLOR]DLookup("[Client_ID]", "tblClient", stLinkCriteria[COLOR=Red][B]), 0)[/B][/COLOR]
        Me.DataEntry = False
        DoCmd.FindRecord ClientID, , , , , acCurrent
    End If
End Sub

 

[bavj]

the error is on:

ClientID = DLookup("[Client_ID]", "tblClient", stLinkCriteria)

 

[bavj]

The code you have sent me works but it does not take me to the record where that data is stored. how can i do that?

 

[pr2-eugin]

What happens if the Client table does not have the phone number? Also could you explain a little bit on what you are trying to do? Why DataEntry is set to False? What does Me.Undo do?

 

[bavj]

Then it should store it as a new record. But i tried adding a mobile number which is already in the system.

 

[pr2-eugin]

Okay the form setup is Bound form with just one control? The reason I ask is because there are other ways like Form BeforeUpdate or checking the entry before opening the data entry form etc.

 

[bavj]

Could you please suggest a code then?

 

[bavj]

I have got a database for contact details. I currently have 54000 entries in excel. I have transferred this data into the database. Now for new entries I want a code which can pick up duplicate entries and display that particular record so i can check all the details are up to date. If that record does not exist then add the record into the system. I am not much of a coder. I got this code also from the net. I really dont know how to work this one out. Please help me as I have been working on this for the last 3 weeks.

 

[pr2-eugin]

Well this is what I would do, create an unbound form that has nothing but one text box control and one button. The text box is where you will enter the Number. On click of the button you will check your Table and see if you have the record entered, if so open the Client Form (bound to the Client table) at that record, if not open the form to add new record.

This Code will go behind the button of the UNBOUND form,

Private Sub buttonName_Click()
    Dim clientID As Long
    
    clientID = Nz(DLookup("[Client_ID]", "tblClient", "[Mobile_Phone] = '" & Me.Mobile_Phone & "'"), 0)
    
    If clientID <> 0 Then
        If MsgBox("This client " & Me.Mobile_Phone & ", has already been entered in database." & vbCr & vbCr & _
                  "Do you wish to see this information?", vbQuestion + vbYesNo, "Duplicate Information") = vbYes Then
            ClientID = Nz(DLookup("[Client_ID]", "tblClient", "[Mobile_Phone] = '" & Me.Mobile_Phone & "'"), 0)
            DoCmd.OpenForm "BoundFormName", WhereCondition:="[Client_ID] = " & ClientID
        Else
            MsgBox "This number is not present in the Client table, you may enter the client information."
            DoCmd.OpenForm "BoundFormName", DataMode:=acFormAdd, OpenArgs:= Me.Mobile_Phone
    End If
End Sub

Then in the Bound Form you can check the open arg to enter the mobile number

Private Sub Form_Current()
    If Me.NewRecord Then Me.Mobile_Phone = Nz(Me.OpenArgs)
End Sub

 

[bavj]

sorry to bother you again now i am getting an error saying

"Compile Error Method or data member not found"

 

[pr2-eugin]

Which line is that error being raised? Did you make sure you have created a button called "buttonName" or any other name and made sure that you changed it to the name of your button? Also that the text box named as Mobile_Phone?

Please adapt the code to your design matches.

 

[bavj]

Hi,

Now i am getting a error of Compile Error: Block If without End If

I don't know why i am getting this error. I have checked all the names of buttons and the text box.

Please help me.

 

[bavj]

i am getting the error on

Private Sub ButtonName_Click()