Run-Time Error 3709 (1 Viewer)

[NauticalGent]

Good morning all,

I have a continuous form that I would like to filter with a combo-box in the forms header.

The combo box is populated with the table's primary field [ID] which is an Autonumber. When I click the caret on the combo box, it provides a list of ID's but when I select one, I get this error message "Run-time error 3709, the search key was not found in any record"

The code is:

Private Sub Combo 31_AfterUpdate()
If Me.Combo31 & ""<> "" Then
     Me.Filter = "[ID]= '"& Me.Combo31 & "'"
Else
     Me.Filter = ""
End If
Me.FilterOn = True
End Sub

I have googled this issue and tried some of the suggestions, included compacting and repairing the database, but nothing has worked.

Any assistance would be appreciated!

 

[JHB]

...
Private Sub Combo 31_AfterUpdate()
If Me.Combo31 & ""<> "" Then
Me.Filter = "[ID]= '"& Me.Combo31 & "'"
Else
Me.Filter = ""
End If
Me.FilterOn = True
End Sub

What are you attempting to do in the marked code line?

 

[GinaWhipp]

Is *ID* a part of the Recordsource?

 

[spikepl]

table's primary field [ID] which is an Autonumber.


Me.Filter = "[ID]= '"& Me.Combo31 & "'"

 

[NauticalGent]

JHB, I am trying to filter the form to just that one record if a user needs to. Normally the form would have about 200+ records that meet the record set.

GinaWhipp, ID is part of the record source.

Spikepl, good catch and I was hoping that was the mistake. However, when I looked at the code, I do in fact have the single quotes where you have them, I just didn't copy the code over correctly. The code is on a classified system so cut and paste is not an option.

 

[JHB]

I would think it should be:

If Me.Combo31 <> "" Then
and not
If Me.Combo31[B][COLOR=Red] & ""[/COLOR][/B]<> "" Then

 

[NauticalGent]

Actually, I think Spikepl was trying to tell me that since my field is a number, the single quotes within the double quotes are not needed. I had to leave work early to attend to an urgent matter, but I will try this first thing in the morning. More to follow...

 

[jdraw]

PMFJI,
You have understood spike's post completely.

 

[NauticalGent]

Ok, I'll bite; what is PMFJI?

 

[jdraw]

Pardon Me For Jumping In

I didn't know if spike was about to answer, and didn't want to intercede.

quotes around strings/text 'this is a string'
octothorpes/hashtag around dates #21/03/2015#

 

[NauticalGent]

Spikepl's input was spot-on! Gotta say, I was a little put out at first because I felt it was a little cryptic. But I with my feeble brain being forced to suss out why he/she chose to use the red font in strategic places, I will never forget it, that's for sure.

Jdraw, I issue you a full pardon for "jumping in", and thanks to you, I now have a forth alternative for the # sign.

In all seriousness, thanks to all for chiming in, it's sites like this that makes all the "head banging" worth it....

 

[vbaInet]

But I with my feeble brain being forced to suss out why he/she chose to use the red font in strategic places, I will never forget it, that's for sure.

Definitely she

By the way, if you're setting a filter string to "" turn the filter off.