How do I get a form to return to the same record after I requery. At the moment every time I requery the form returns to the first record in the recordset. T the moment I am using:

Me.Requery

On Dec 21, 5:43*am, FireyColin <colin.mard... (AT) btopenworld (DOT) com> wrote:
I typically grab the ID prior to requery, requery, then search for the
ID.

Thankyou Patrick

I've been trying that using the following code:

Dim StrValue1 As String

StrValue1 = [Forms]![frmPolicy]![txtPolicyNo]

DoCmd.OpenForm "frmPolicy", acNormal, , , "policy_no = StrValue1"

but just comes back with 'Type mismatch'

On Wed, 21 Dec 2011 05:44:43 -0800 (PST), FireyColin
<colin.mardell (AT) btopenworld (DOT) com> wrote:

I stink at string syntax, but maybe this will work:

"[policy_no] = ' " & strValue1 &" ' "

-pw

On Dec 21, 11:58*am, PW <emailaddyin... (AT) ifIremember (DOT) com> wrote:
My rule of thumb is
Strings get surrounded in qutes
Dates get surrounded in #
Numbers don't get surrounded

So PW's response would be correct since Firey defined the value as a
string

"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

PW's syntax should work; but why is the OP reopening the form after the
requery?
I find recordsetclone much more reliable.

dim strValue1 as string
dim rsClone as recordset

StrValue1 = me.txtPolicyNo

me.requery

set rsClone = me.Recordsetclone
rsClone.FindFirst "[PolicyNo] = '" & StrValue1 & "'"

if me.nomatch then
msgbox "Could not find Policy number: " & strValue1
else
me.bookmark = rsClone.bookmark
endif
set rsclone = nothing

On Wed, 21 Dec 2011 10:34:37 -0800 (PST), Patrick Finucane
<patrickfinucanetx (AT) gmail (DOT) com> wrote:

No way!!! :-)

I am going to write down your rules and stick them on my monitor
(since they won't stick to the inside of my head! <g> )! There are
some things in life I just have a persistent mental block with and
Jet/variable Syntax is one of them :-)

-paulw

On Wed, 21 Dec 2011 14:05:21 -0600, "Ron Paii" <None (AT) none (DOT) com> wrote:

Hey - it's right out of Getz's Access 97 Handbook! :-) I wish he went
into more explanation though.

"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

I think the explanation would be in how SQL and VBA handles strings more
then Access. In this case StrValue1 is a local variable so when you attempt
use it in a form filter "policy_no = StrValue1", SQL has no access to the
variable. But with "[policy_no] = ' " & strValue1 &" ' ", you are
concatenating the string stored in strValue1 into the SQL. The extra quotes
you are adding are required by SQL so it doesn't try to interpret the
strValue1 string as a SQL statement.

Patrick Finucane wrote:
find it ...
Ah! Here it is:
To decide whether or not to delimit the data, look at the datatype of the
FIELD - NOT THE DATA.
1. If it's a numeric field, you must supply it with numeric data, which
means you MUST NOT delimit the data by putting quotes around it.
2. If it's a character/text field, then you must supply string data by
delimiting the data either with single or double quotes. If the data
contains literal quotes, you must escape them by doubling them. This means
that if you use single quotes (apostrophes) for your string delimiters, and
the data contains an apostrophe, then you must replace the apostrophe with
two apostrophes, like this:
Update tbl set textfield = 'O''Malley'
In Access, you can use double quotes for your delimiters, so this will work
as well:
Update tbl set textfield = "O'Malley"
Note: you don't have to escape the apostrophe in O'Malley when you use
double quotes as the delimiter. However, you will need to escape the double
quotes when assigning this statement to a variable:
sSQL = "Update tbl set textfield = ""O'Malley"""
So most people will use the single quotes and escape the apostrophe:
sName = "O'Malley"
sSQL = "Update tbl set textfield = '" & Replace(sName,"'","''") & "'"
debug.print sSQL

3. If it's a date/Time field, then the delimiters depend on the type of
database. Since you are using Access, then you must delimit the data with
hash marks (#). Additionally, you must supply the data in US format
(m/d/yyyy) or in ISO (yyyy-mm-dd), with the latter being the more
recommended.

4.Lastly, if you are using LIKE, you need to be aware that you must use %
and _ as the wildcards, not * and ?. This is true no matter what database
you are using

And then, when you think you have it right and it still does not work,
debug.print it (you always assign the results of your concatenations to
variables
don't you) to see the result of your concatenation. If you've done it
correctly, you will have a statement that you can copy and paste from the
Immediate window into the SQL View of an Access Query Builder and
run without modification (unless you need to replace the wildcards with the
Jet
wildcards).