HELP! error 3709 search key not found

[Sue]

I have done something and I am clueless. Please be aware
I am self-taught.

I get the above message on my form when I try to select a
record via combo box. My code:

*******************************************************
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

rs.FindFirst "[InvoiceNumber] = " & Me!
[InvoiceNumber] & ""

Me.Bookmark = rs.Bookmark
*******************************************************

This began, seemingly, all of a sudden. I cannot seem to
make it go away. My tables are referential integrity
enforced, cascading deletes, and indexes.

Why has this started to occur? HELP only speaks to
indexes, but it worked moments before.

Will someone aid me please?
Thanks.
Sue

 

[Dirk Goldgar]

I have done something and I am clueless. Please be aware
I am self-taught.

I get the above message on my form when I try to select a
record via combo box. My code:

*******************************************************
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

rs.FindFirst "[InvoiceNumber] = " & Me!
[InvoiceNumber] & ""

Me.Bookmark = rs.Bookmark
*******************************************************

This began, seemingly, all of a sudden. I cannot seem to
make it go away. My tables are referential integrity
enforced, cascading deletes, and indexes.

Why has this started to occur? HELP only speaks to
indexes, but it worked moments before.

Will someone aid me please?
Thanks.
Sue

Is "frm Main" the name of the form this code is running on? Is "Invoice
Number" the name of the combo box? I'm a little puzzled as to the
purpose of the lines:

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

in the code behind a "search" combo box, so maybe there's something I
don't understand, or have guess wrong about.. Could you explain,
please?

Is the combo box a bound control; that is, does it have its Control
Source property set to a field in the form's Record Source?

 

[Sue]

Thanks for answering, Dirk.

I was advised to use the code several years ago -- via
this newsgroup. Forgot who.

Its an unbound, I am merely trying to get to a specific
record for the customer I am in and call the invoice form
getting only that customer's invoices. This combo box
just allows me to select the one to view.

Thanks.
Sue.

-----Original Message-----

I have done something and I am clueless. Please be aware
I am self-taught.

I get the above message on my form when I try to select a
record via combo box. My code:

*******************************************************
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

rs.FindFirst "[InvoiceNumber] = " & Me!
[InvoiceNumber] & ""

Me.Bookmark = rs.Bookmark
*******************************************************

This began, seemingly, all of a sudden. I cannot seem to
make it go away. My tables are referential integrity
enforced, cascading deletes, and indexes.

Why has this started to occur? HELP only speaks to
indexes, but it worked moments before.

Will someone aid me please?
Thanks.
Sue

Is "frm Main" the name of the form this code is running on? Is "Invoice
Number" the name of the combo box? I'm a little puzzled as to the
purpose of the lines:

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

in the code behind a "search" combo box, so maybe there's something I
don't understand, or have guess wrong about.. Could you explain,
please?

Is the combo box a bound control; that is, does it have its Control
Source property set to a field in the form's Record Source?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Thanks for answering, Dirk.

I was advised to use the code several years ago -- via
this newsgroup. Forgot who.

Ah, but does it make sense in this context? We'll see.

Its an unbound, I am merely trying to get to a specific
record for the customer I am in and call the invoice form
getting only that customer's invoices. This combo box
just allows me to select the one to view.

I'm confident we can solve your problem, but I need additional
clarification. You didn't answer these questions of mine:

1. Is "frm Main" the name of the form this code is running on? (If not,
please give the name of the form)

2. Is "Invoice Number" the name of the combo box? (If not, please give
the name of the combo box).

To them, I'll add these:

3. Is this an MDB file or an ADP file that you're working with?

4. What is the RecordSource of the form? Does it contain a field named
"InvoiceNumber"?

5. What is the RowSource of the combo box? Does the Bound Column of the
combo box correspond to the InvoiceNumber field in that RowSource?

6. What do you mean by ...

I am merely trying to get to a specific
record for the customer I am in and call the invoice form
getting only that customer's invoices.

Where do customers come into it? All I've seen reference to far is
invoices. What do you mean by "call the invoice form"? The code you've
shown is to navigate to a record on the current form, not to open a new
one.

 

[Sue]

I've actually, I think, successfully gone a different
route...but I want to understand what happened. In case...

Answers to your questions:

1. Yes
2. Yes
3. MDB
4. A query based on invoices table
5. Here may be the rub: its rowsource is a query that,
again, I got on here a long time ago, that is used to
select client's invoices. See below.
6. My default form is customer. Once a customer is
selected user may open Invoice form which opens to a
record for that client. The combo box is my way to select
any other of that client's invoices.

It all works fine, but I was doing some designing of the
Invoice form and got that message in view mode when I
tried to select an invoice.

Thanks for the help.
Sue

 

[Dirk Goldgar]

I've actually, I think, successfully gone a different
route...but I want to understand what happened. In case...

Answers to your questions:

4. A query based on invoices table
5. Here may be the rub: its rowsource is a query that,
again, I got on here a long time ago, that is used to
select client's invoices. See below.

It would have helped if you'd posted the SQL of the query. I'll try to
make a reasonable guess without that. I'm going to assume that it has a
WHERE criterion containing a reference to a control containing the
current customer's primary key, so that the combo box shows only the
invoices for that customer. There's probably code somewhere that
requeries the combo if the customer changes.

6. My default form is customer. Once a customer is
selected user may open Invoice form which opens to a
record for that client. The combo box is my way to select
any other of that client's invoices.

It all works fine, but I was doing some designing of the
Invoice form and got that message in view mode when I
tried to select an invoice.

I wish you could tell me what sort of "designing" you were doing.

What happens if you delete or comment out these lines from the combo
box's event procedure (I hope, BTW, that you're using the AfterUpdate
event, but you may be using the Click or even the Change event):

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

I really don't see the point of them, and maybe they're messing things
up.

 

[Andi Mayer]

I have done something and I am clueless. Please be aware
I am self-taught.

I get the above message on my form when I try to select a
record via combo box. My code:

*******************************************************
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

rs.FindFirst "[InvoiceNumber] = " & Me!
[InvoiceNumber] & ""

Me.Bookmark = rs.Bookmark
*******************************************************

Dirk and Sue
you both might go into the wrong direction

on 2000 and 2002 the error tells me:
?error(3709)
Application-defined or object-defined error

is this rs a DAO-object? it feels like an ADO?

Sue try first:

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

with me.recordsetClone
.FindFirst "[InvoiceNumber] = " & Me![InvoiceNumber] & ""
Me.Bookmark = .Bookmark
End With

 

[Sue]

Thanks, Andi. Please see response to Dirk, below.

-----Original Message-----

I have done something and I am clueless. Please be aware
I am self-taught.

I get the above message on my form when I try to select a
record via combo box. My code:

*******************************************************
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

rs.FindFirst "[InvoiceNumber] = " & Me!
[InvoiceNumber] & ""

Me.Bookmark = rs.Bookmark
*******************************************************

Dirk and Sue
you both might go into the wrong direction

on 2000 and 2002 the error tells me:
?error(3709)
Application-defined or object-defined error

is this rs a DAO-object? it feels like an ADO?

Sue try first:

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

with me.recordsetClone
.FindFirst "[InvoiceNumber] = " & Me![InvoiceNumber] & ""
Me.Bookmark = .Bookmark
End With

word "manfred" to the first 10 lines in the message

 

[Guest]

Dirk,

I tried Andi's, with and without the requery statements,
and the bahaviour was the same as yours -- with and
without those statements. I saw no difference in the
result, just two methods.

In response to yours . . .

1. The SQL does reference the customer's primary key and
works as desired when changing customers.

2. I was designing the invoice form for VBA and control
cosmetics.

3. After removing the requeries, the invoice form does
not jump to the first record on its way to the selection.
That's much better...duh!

I did restore my db and redid the work that I thought had
caused the error. None has been encountered since.

I am curious, but if you have tired of this issue I will
understand.

Thanks for the help,
Sue

-----Original Message-----

I've actually, I think, successfully gone a different
route...but I want to understand what happened. In case...

Answers to your questions:
working with?
3. MDB
4. A query based on invoices table
5. Here may be the rub: its rowsource is a query that,
again, I got on here a long time ago, that is used to
select client's invoices. See below.

It would have helped if you'd posted the SQL of the query. I'll try to
make a reasonable guess without that. I'm going to assume that it has a
WHERE criterion containing a reference to a control containing the
current customer's primary key, so that the combo box shows only the
invoices for that customer. There's probably code somewhere that
requeries the combo if the customer changes.

6. My default form is customer. Once a customer is
selected user may open Invoice form which opens to a
record for that client. The combo box is my way to select
any other of that client's invoices.

It all works fine, but I was doing some designing of the
Invoice form and got that message in view mode when I
tried to select an invoice.

I wish you could tell me what sort of "designing" you were doing.

What happens if you delete or comment out these lines from the combo
box's event procedure (I hope, BTW, that you're using the AfterUpdate
event, but you may be using the Click or even the Change event):

Forms![frm Main]![InvoiceNumber].Requery
Forms![frm Main].Requery

I really don't see the point of them, and maybe they're messing things
up.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Dirk and Sue
you both might go into the wrong direction

on 2000 and 2002 the error tells me:
?error(3709)
Application-defined or object-defined error

But

?accesserror(3709)
The search key was not found in any record.

 

[Dirk Goldgar]

Dirk,

I tried Andi's, with and without the requery statements,
and the bahaviour was the same as yours -- with and
without those statements. I saw no difference in the
result, just two methods.

In response to yours . . .

1. The SQL does reference the customer's primary key and
works as desired when changing customers.

2. I was designing the invoice form for VBA and control
cosmetics.

3. After removing the requeries, the invoice form does
not jump to the first record on its way to the selection.
That's much better...duh!

I did restore my db and redid the work that I thought had
caused the error. None has been encountered since.

I am curious, but if you have tired of this issue I will
understand.

I'm somewhat confused at this point as to what is and isn't working. I
would say that if you are working in a MDB file, this would be more
efficient, and might catch an error if no match is found:

' Find the record that matches the control.

With Me.RecordsetClone
.FindFirst "InvoiceNumber = " & Me!InvoiceNumber
If .NoMatch Then
MsgBox "Sorry, that invoice number was not found.", _
vbInformation, "Not Found"
Else
Me.Bookmark = .Bookmark
End If
End With

That's pretty much the same as what Andi Mayer posted, except that I've
added an error trap.

 

[Andi Mayer]

?accesserror(3709)

interesting: in Access2000 I get an empty string

2002 gives me your error-string

 

[Dirk Goldgar]

interesting: in Access2000 I get an empty string

2002 gives me your error-string

I guess they hadn't assigned that error code yet. I still don't know
what Sue is doing that raises that particular error, though.

 

[Andi Mayer]

I guess they hadn't assigned that error code yet. I still don't know
what Sue is doing that raises that particular error, though.

corrupted table????