Run time error 2001

[sajitk]

Dear All,

I have been having a problem with the following code:

Private Sub Solar_Lantern_No_AfterUpdate()
Dim vCheckEntry As Boolean
 vCheckEntry = DLookup("[Solar Lantern No]", "Beneficiary", "[Solar Lantern No] = " & Me.Solar_Lantern_No)
  If vCheckEntry = True Then
    MsgBox "Solar Lantern NO. already exists. Please change it.", vbInformation, "Beneficiary"
    Cancel = True
    Me.Solar_Lantern_No.SetFocus
    Else
    Me.Name_of_the_Beneficiary.SetFocus
  End If
End Sub

The error occurs of the highlighted line. My basic idea is to check whether the primary key (Solar Lantern No) is not duplicated.

Can anyone tell me where the problem is...??

Thaning you in advance
Sajit

[FishVal]

Hi, Sajit.

• I guess [Solar_Lantern_No] form control is not bound to a correspondent [Solar_Lantern_No] table field assuming [Solar_Lantern_No] table field is really set as PK. Am I right?
• "Cancel=True" will work in event handler receiving "Cancel" argument by reference, for example in BeforeUpdate event handler. In AfterUpdate event handler it will throw an error or will do nothing depending on whether explicit variable declaration is enforced in the module or not.
• DLookUp() function doesn't return Boolean, it returns a Variant "first" value of a field in record satisfying given criteria, if no records found it returns Null
• The syntax of the criteria passed to DLookUp() is right for Number type table field. If [Solar_Lantern_No] table field is set as Text, then single quotes wrapping the value, [Solar_Lantern_No] table field compared to, have to be added.

Regards,
Fish

[sajitk]

Thanks Fish,

Can u give me an example of how to write this function when a text field is used...

Sajit

Hi, Sajit.

• I guess [Solar_Lantern_No] form control is not bound to a correspondent [Solar_Lantern_No] table field assuming [Solar_Lantern_No] table field is really set as PK. Am I right?
• "Cancel=True" will work in event handler receiving "Cancel" argument by reference, for example in BeforeUpdate event handler. In AfterUpdate event handler it will throw an error or will do nothing depending on whether explicit variable declaration is enforced in the module or not.
• DLookUp() function doesn't return Boolean, it returns a Variant "first" value of a field in record satisfying given criteria, if no records found it returns Null
• The syntax of the criteria passed to DLookUp() is right for Number type table field. If [Solar_Lantern_No] table field is set as Text, then single quotes wrapping the value, [Solar_Lantern_No] table field compared to, have to be added.

Regards,
Fish

[FishVal]

Quotes (') and Double-Quotes (") - Where and When to use them

[sajitk]

Now it says "Type mismatch"

sajit

Quotes (') and Double-Quotes (") - Where and When to use them

[FishVal]

Well. And what is the type of [Solar_Lantern_No] table field?

P.S. In post #2 I've listed possible problems with code including those not causing the fault with DLookUp() function. Also I've made some assumptions as for info you've not provided (I mean particularly table metadata). So the list is not supposed to be applied as is but in accordance with form/table settings. :)