My use of "FindFirst" is not working

[Ray C]

I use the following to find a record and set the Bookmark in a Forms
Recordset. This should set all of the Fields that are displayed in my form to
the appropriate record. I use this Utility in other parts of my code and it
seems to work well. All the variables are passed to it from the calling
routine.

If intType = 1 Then strCriteria = BuildCriteria(strField, dbInteger, varInfo)
If intType = 2 Then strCriteria = BuildCriteria(strField, dbText, varInfo)

With frm.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
frm.Bookmark = .Bookmark
util_Populate = True
Else
util_Populate = False
End If
End With

The problem is that (in this particulsr scenario) it does not work and I can
not figure out what I am doing wrong.
My Table is a "Stock" table and the Serial number Fieild is a "String"
Field. I have various Serial Numbers stored in that Fielad and whilst some
are Numeric and some are Alpha Numeric, they are all stores as a String
Field. I therefore Call the routine with "intType" set to 2 (String) and
"varInfo" set the Serial Numberthat I want to find.
If I set Varinfo to "12ABC34" the criterion is ("SerialNo = "12ABC34") and
the record is found (the function returns True).
If I set Varinfo to "12ABC34" the criterion is ("SerialNo = "1234") and the
record is not found (the function returns False.
Both the records are definately in the Table but the Function seems to
discriminate between a Serial Number Field that contains data with Alpha
Characters in it and a Serial Number Field that contains data does not have
Alpha Characters in it even though they are all stored as strings.
Incidentally. If I pass "1234" to the function and deliberately tell it that
the variable being passed id a Number ( intType = 1) I get a "Type missmatch
error as would be expected.
Can anyone throw any light on what is happening here and what I should be
doing to correct it?

Thanks for any help Ray C

 

[ruralguy via AccessMonster.com]

These two lined seem to contradict each other. Can you explain?

If I set Varinfo to "12ABC34" the criterion is ("SerialNo = "12ABC34") and
the record is found (the function returns True).
If I set Varinfo to "12ABC34" the criterion is ("SerialNo = "1234") and the
record is not found (the function returns False.

 

[Ray C]

Sorry, I have dyslexic fingers. It's a Typo and should read :-

If I set Varinfo to "12ABC34" the criterion is ("SerialNo = "12ABC34") and
the record is found (the function returns True).
If I set Varinfo to "1234" the criterion is ("SerialNo = "1234") and the
record is not found (the function returns False.

The table contains different records, each with these respecive Serial
Numbers but my routine will only find one.

in other words, any Serial No (that exists in the Table) with an alpha
character in it will be found, and any Serial No (that exists in the Table)
without an alpha character in it will not be found.

Sorry RayC

 

[ruralguy via AccessMonster.com]

OK, that makes more sense. Thanks. As for the problem, what you are doing
should work unless somehow the RecordSet has the other serial number records
filtered out.

Sorry, I have dyslexic fingers. It's a Typo and should read :-

If I set Varinfo to "12ABC34" the criterion is ("SerialNo = "12ABC34") and
the record is found (the function returns True).
If I set Varinfo to "1234" the criterion is ("SerialNo = "1234") and the
record is not found (the function returns False.

The table contains different records, each with these respecive Serial
Numbers but my routine will only find one.

in other words, any Serial No (that exists in the Table) with an alpha
character in it will be found, and any Serial No (that exists in the Table)
without an alpha character in it will not be found.

Sorry RayC

These two lined seem to contradict each other. Can you explain?

[quoted text clipped - 44 lines]

 

[Ray C]

Hi thanks for the replyI know it should work but it desn't. As far as I am
aware, I do not have any filters applied that would take out all the records
that do not have an Alpha Character in them. How would i do that if I wanted
to (which I don't)?

Any suggestions would be more than welcome.

RayC

OK, that makes more sense. Thanks. As for the problem, what you are doing
should work unless somehow the RecordSet has the other serial number records
filtered out.

Sorry, I have dyslexic fingers. It's a Typo and should read :-

If I set Varinfo to "12ABC34" the criterion is ("SerialNo = "12ABC34") and
the record is found (the function returns True).
If I set Varinfo to "1234" the criterion is ("SerialNo = "1234") and the
record is not found (the function returns False.

The table contains different records, each with these respecive Serial
Numbers but my routine will only find one.

in other words, any Serial No (that exists in the Table) with an alpha
character in it will be found, and any Serial No (that exists in the Table)
without an alpha character in it will not be found.

Sorry RayC

These two lined seem to contradict each other. Can you explain?

[quoted text clipped - 44 lines]

Thanks for any help Ray C

 

[ruralguy via AccessMonster.com]

I don't know how I would create the filter I described even if I wanted to,
it was just a thought. Maybe we're focused on the wrong area. Maybe it has
to do with where you are invoking this code. From where is it being called?
I'm still trying to think of some sort of diagnostic code to add so we can
troubleshoot.

Hi thanks for the replyI know it should work but it desn't. As far as I am
aware, I do not have any filters applied that would take out all the records
that do not have an Alpha Character in them. How would i do that if I wanted
to (which I don't)?

Any suggestions would be more than welcome.

RayC

OK, that makes more sense. Thanks. As for the problem, what you are doing
should work unless somehow the RecordSet has the other serial number records

[quoted text clipped - 21 lines]

 

[Ray C]

I realy appreciate your respnse. I have tried everything I know (which isn't
much) to sort the problem but I am getting no where.
Can I do anything else to help you help me?

Regards RayC

I don't know how I would create the filter I described even if I wanted to,
it was just a thought. Maybe we're focused on the wrong area. Maybe it has
to do with where you are invoking this code. From where is it being called?
I'm still trying to think of some sort of diagnostic code to add so we can
troubleshoot.

Hi thanks for the replyI know it should work but it desn't. As far as I am
aware, I do not have any filters applied that would take out all the records
that do not have an Alpha Character in them. How would i do that if I wanted
to (which I don't)?

Any suggestions would be more than welcome.

RayC

OK, that makes more sense. Thanks. As for the problem, what you are doing
should work unless somehow the RecordSet has the other serial number records

[quoted text clipped - 21 lines]

Thanks for any help Ray C

 

[ruralguy via AccessMonster.com]

Have you tried creating a new, empty db with Name AutoCorrect turned OFF and
importing everything into the new db and see how it works? If that fails you
can Compact and Repair the db and zip it up and send it to Rural Guy at Wild
Blue dot Net if you want. You can strip any sensitive data if you need to
but leave enough test data to show the problem.

I realy appreciate your respnse. I have tried everything I know (which isn't
much) to sort the problem but I am getting no where.
Can I do anything else to help you help me?

Regards RayC

I don't know how I would create the filter I described even if I wanted to,
it was just a thought. Maybe we're focused on the wrong area. Maybe it has

[quoted text clipped - 16 lines]

 

[Ray C]

I realy appreciate youir help on this.
When you say db, I assume that you mean my Stock Table, or do you mean the
whole thing? I will try re-doing everything from sctratch (I need to do that
anyway as my code is very messy) but I don't understand "name Autocorrect" or
how to turn it off. I will hunt arround and se what I can find.
Thanks for you very kind offer of help. This will take me a few days to
complete, would you mind if I contact you directly via e-mail next time or
would you prefer this forum?
Thanks once again RayC

Have you tried creating a new, empty db with Name AutoCorrect turned OFF and
importing everything into the new db and see how it works? If that fails you
can Compact and Repair the db and zip it up and send it to Rural Guy at Wild
Blue dot Net if you want. You can strip any sensitive data if you need to
but leave enough test data to show the problem.

I realy appreciate your respnse. I have tried everything I know (which isn't
much) to sort the problem but I am getting no where.
Can I do anything else to help you help me?

Regards RayC

I don't know how I would create the filter I described even if I wanted to,
it was just a thought. Maybe we're focused on the wrong area. Maybe it has

[quoted text clipped - 16 lines]

Thanks for any help Ray C

 

[ruralguy via AccessMonster.com]

When I said a new db, I meant a new MDB. The go to Tools>Options...>General
Tab and turn OFF Name AutoCorrect. Importing everything from the old db
takes seconds. Let's stay with this forum a while longer and see if we can
get it to work.

I realy appreciate youir help on this.
When you say db, I assume that you mean my Stock Table, or do you mean the
whole thing? I will try re-doing everything from sctratch (I need to do that
anyway as my code is very messy) but I don't understand "name Autocorrect" or
how to turn it off. I will hunt arround and se what I can find.
Thanks for you very kind offer of help. This will take me a few days to
complete, would you mind if I contact you directly via e-mail next time or
would you prefer this forum?
Thanks once again RayC

Have you tried creating a new, empty db with Name AutoCorrect turned OFF and
importing everything into the new db and see how it works? If that fails you

[quoted text clipped - 13 lines]

 

[Ray C]

Hi
Done that but no change.
RayC

When I said a new db, I meant a new MDB. The go to Tools>Options...>General
Tab and turn OFF Name AutoCorrect. Importing everything from the old db
takes seconds. Let's stay with this forum a while longer and see if we can
get it to work.

I realy appreciate youir help on this.
When you say db, I assume that you mean my Stock Table, or do you mean the
whole thing? I will try re-doing everything from sctratch (I need to do that
anyway as my code is very messy) but I don't understand "name Autocorrect" or
how to turn it off. I will hunt arround and se what I can find.
Thanks for you very kind offer of help. This will take me a few days to
complete, would you mind if I contact you directly via e-mail next time or
would you prefer this forum?
Thanks once again RayC

Have you tried creating a new, empty db with Name AutoCorrect turned OFF and
importing everything into the new db and see how it works? If that fails you

[quoted text clipped - 13 lines]

Thanks for any help Ray C

 

[ruralguy via AccessMonster.com]

Well Ray, I guess it is time to zip up the db and send it to me. Use the
email addy I already posted.

Hi
Done that but no change.
RayC

When I said a new db, I meant a new MDB. The go to Tools>Options...>General
Tab and turn OFF Name AutoCorrect. Importing everything from the old db

[quoted text clipped - 16 lines]

 

[ruralguy via AccessMonster.com]

Post back here when it is on its way so I'll know to look for it.

Well Ray, I guess it is time to zip up the db and send it to me. Use the
email addy I already posted.

Hi
Done that but no change.

[quoted text clipped - 5 lines]

 

[Dale Fye]

Ray,

Can you post the code in your BuildCriteria( ) function:

Dale