rs.NoMatch doenst seem to work on string searches.

[darksniperx]

Hi,



I have the following code which should return "No entry found" if there was nothing found. But instead I am getting default ms access error message.

Although the code works on numeric searches, it just doesn't want to work on string searches.

Is there anything I can or check to find out why it doesn't work.

Private Sub Combo96_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 If Len(Combo96 & "") > 0 Then
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[sdutentId] = " & Str(Nz(Me![Combo96], 0))
    If rs.NoMatch Then
        MsgBox "No entry found"
    Else
        Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
    DoCmd.GoToControl "Combo96"
    End If
End Sub

[RuralGuy]

Strings need to be enclosed in quotes. Try:
rs.FindFirst "[sdutentId] = '" & Nz(Me![Combo96], 0) & "'"

[darksniperx]

I have just tried it, still there is no change.

If you want I can post a copy of the database, if that is going to make it easier.

[RuralGuy]

Sure, go ahead and post the zipped up db.

[MAF4Fam6]

Is it possible the sdutentID field name is misspelled which may be causing the problem? Should it read: StudentID ?

-RC

[darksniperx]

sdutentID, is the actuall spelling, I misspelled it when I have created the table, too late to change it now.

If record exists -> the code finds it and displays it.
If record does not exist -> I get default ms access error which I want to get rid off.

[darksniperx]

Here is the database.

Sorry for double post, didn't find attach option in edit section.

[RuralGuy]

OK, so your find is now working. I do not know what error you are getting but why are you trying to go to "Combo96" in the AfterUpdate event of that very control? That will not work.

[darksniperx]

After-update code was partially generated by access.

Combo96 searching always worked, it just gave a weird error when it could not find the search string.

What I wanted to do, is to change the weird error that ms access generates, and provide something more useful.

That's why I have modified combo96, to catch all of the issues. Except that noMatch doesnt seem to work.

[RuralGuy]

You have it set to LimitToList, right? Are you aware that Recordset.Clone is a method of an ADO Recordset and RecordsetClone is a property of a DAO Recordset and NoMatch is DAO? You should not really mix them.

[darksniperx]

Nope, I wasnt aware of that, how can I implement limit to list to fix the issue.

Thank you!

[RuralGuy]

You *DO* have it set to YES and it is a property on the Data tab of the control. What error are you trying to intercept?

[darksniperx]

Hi,

LimitToList was already set to Yes.

Error:
"The text you have entered isn't an item in the list" that the default msaccess error that I want to change.

[RuralGuy]

Now we are getting somewhere! That is the error you get when you type something into the ComboBox and LimitToList is set to YES. You will need to change your bound column if you want to set that property to NO.

[darksniperx]

As soon as I change bound column to any other number than 1, ms access crashes.