How do you return to record you were working on after requery?

  • Thread starter Thread starter jpwgh
  • Start date Start date
J

jpwgh

I saw Linq Adams reply to Efandango's question regarding moving to a null
field after a requery. I tried the code provided in the answer but it didn't
work. My unique id field is numeric. I am not sure if that is why it didn't
work. What I am trying to do is go to the form I was working on after a
requery. I have seen a variety of code that does it, but I can't get it to
work. I think my problem might be with my unique field.

Any suggestions would be much appreciated. I have been struggling with this
for a while, but working on nothing but this for the last two days.
 
jpwgh said:
I saw Linq Adams reply to Efandango's question regarding moving to a null
field after a requery. I tried the code provided in the answer but it didn't
work. My unique id field is numeric. I am not sure if that is why it didn't
work. What I am trying to do is go to the form I was working on after a
requery. I have seen a variety of code that does it, but I can't get it to
work. I think my problem might be with my unique field.
Click to expand...


It's difficult to figure out what you did wrong until we can
see what you did (without asking us to chase down some other
thread and guess what you did to it in your form).

If you think it might have something to do with the unique
field, then you should post the important details about that
field in the table.
 
Since I don't know what you have tried, I will offer this suggestion.

Before you do the requery, save the unique key field value in a variable, do
the requery, then use the value of the variable to reposition your recordset

Dim lngKeyVal As Long

lngKeyVal = Me.txtKeyFeild

Me.Requery

With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

If that doesn't work, post back with more detail on your situation.
 
Thank you for your response. First of all, the field I am using for my
unique field is a numeric field. Some of the code I have seen mentioned it
was for a text field. I have been trying different code using the LastName
field because it was a text field. In the long run, it will not work, since
two different people can have the same last name.

Here is what I came up with when trying different code.

When I tried using your code, I got a runtime error 13, type mismatch. Part
of my problem may be I am alittle confused where to substitute my field names.
In place of "txtKeyField" , I used LastName. In place of [KeyField], I used
LastName.

The other code I tried:

Dim hldID
hldID = Me!LastName
Me.Requery
Me.RecordSet.FindFirst "LastName ="" & hldID & '""

Problem with that code, I wasn't sure where to put the ' vs. where to put
the ".

When I did "LastName = " ' & hldID & ""
or
"LastName=" ' & hldID & ' " "

I got run-time error 3077, missing operator.

When I used "LastName =" & hldID

I got run-time error 3070, MS Jet Database does not recognize (the name in
the LastName field of the record I was working on) as valid field name.

Thanks again for taking a look at my problem.
Since I don't know what you have tried, I will offer this suggestion.

Before you do the requery, save the unique key field value in a variable, do
the requery, then use the value of the variable to reposition your recordset

Dim lngKeyVal As Long

lngKeyVal = Me.txtKeyFeild

Me.Requery

With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

If that doesn't work, post back with more detail on your situation.
I saw Linq Adams reply to Efandango's question regarding moving to a null
field after a requery. I tried the code provided in the answer but it didn't
Click to expand...
[quoted text clipped - 5 lines]
Any suggestions would be much appreciated. I have been struggling with this
for a while, but working on nothing but this for the last two days.
Click to expand...
Click to expand...
 
This line:
Me.RecordSet.FindFirst "LastName ="" & hldID & '""

Should be:
Me.RecordSet.FindFirst "LastName ="'" & hldID & "'"

Expanded for readability it is:
Me.RecordSet.FindFirst "LastName =" ' " & hldID & " ' "

But if you have two same last names, you will only get the first one, which
may not have been the record you were on. For this to work correctly, you
need a unique field. I would suggest adding an autonumber field to your
table and using that. Be sure you create an index for the autonumber field
for performance.
--
Dave Hargis, Microsoft Access MVP


jpwgh via AccessMonster.com said:
Thank you for your response. First of all, the field I am using for my
unique field is a numeric field. Some of the code I have seen mentioned it
was for a text field. I have been trying different code using the LastName
field because it was a text field. In the long run, it will not work, since
two different people can have the same last name.

Here is what I came up with when trying different code.

When I tried using your code, I got a runtime error 13, type mismatch. Part
of my problem may be I am alittle confused where to substitute my field names.
In place of "txtKeyField" , I used LastName. In place of [KeyField], I used
LastName.

The other code I tried:

Dim hldID
hldID = Me!LastName
Me.Requery
Me.RecordSet.FindFirst "LastName ="" & hldID & '""

Problem with that code, I wasn't sure where to put the ' vs. where to put
the ".

When I did "LastName = " ' & hldID & ""
or
"LastName=" ' & hldID & ' " "

I got run-time error 3077, missing operator.

When I used "LastName =" & hldID

I got run-time error 3070, MS Jet Database does not recognize (the name in
the LastName field of the record I was working on) as valid field name.

Thanks again for taking a look at my problem.
Since I don't know what you have tried, I will offer this suggestion.

Before you do the requery, save the unique key field value in a variable, do
the requery, then use the value of the variable to reposition your recordset

Dim lngKeyVal As Long

lngKeyVal = Me.txtKeyFeild

Me.Requery

With Me.RecordsetClone
.FindFirst "[KeyField] = " & lngKeyVal
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

If that doesn't work, post back with more detail on your situation.
I saw Linq Adams reply to Efandango's question regarding moving to a null
field after a requery. I tried the code provided in the answer but it didn't
Click to expand...
[quoted text clipped - 5 lines]
Any suggestions would be much appreciated. I have been struggling with this
for a while, but working on nothing but this for the last two days.
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your reply. This was my first question. Sorry. I did a really
poor job in describing my problem. There is a more detailed description of
my problem in my reply below.

Thanks for the input. I will try to do better in the future.
 
Thank you, thank you, thank you!!!. I was going crazy trying to get this to
work. I used a different field which is unique and it worked.
This line:
Me.RecordSet.FindFirst "LastName ="" & hldID & '""

Should be:
Me.RecordSet.FindFirst "LastName ="'" & hldID & "'"

Expanded for readability it is:
Me.RecordSet.FindFirst "LastName =" ' " & hldID & " ' "

But if you have two same last names, you will only get the first one, which
may not have been the record you were on. For this to work correctly, you
need a unique field. I would suggest adding an autonumber field to your
table and using that. Be sure you create an index for the autonumber field
for performance.
Thank you for your response. First of all, the field I am using for my
unique field is a numeric field. Some of the code I have seen mentioned it
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
I answer so many posts, here and elsewhere, in a given week, that I can't
begin to recall the one you referred to in your original post, but I'm sure
the code would have been for a text field, like Klatuu's

FindFirst "LastName =" ' " & hldID & " ' "

which, as you found out doesn't work for a numeric datatype.

Klatuu's second code

FindFirst "[KeyField] = " & lngKeyVal

is the correct syntax for a numeric field.

This same type syntax is used when giving criteria for such things as DCount()
and DLookup() as well as criteria for opening a form to a certain record.
 
jpwgh said:
Thank you, thank you, thank you!!!. I was going crazy trying to get this to
work. I used a different field which is unique and it worked.
Click to expand...


Slight typo in there. For a Text field, expanded for
readability, it could be either:

Me.RecordSet.FindFirst "LastName = ' " & hldID & " ' "

Me.RecordSet.FindFirst "LastName =" " " & hldID & " " " "

or, totally foolproof, either:

Me.RecordSet.FindFirst "LastName = ' " & Replace(hldID,
" ' ", " ' ' ") & " ' "

Me.RecordSet.FindFirst "LastName = " " " & Replace(hldID,
" " " ", " " " " " ") & " " " "
 
jpwgh,

Once you have decided you are going to use a text field in your FindFirst,
or Where clause, you then have to build up the string, putting the ' in the
right place or using the tripplet (""") of double qoutes. I have an easier
method. I created a function fnQuotes( ) that will wrap the text in
quotation marks, and makes your code so much easier to read. So instead of
one of the examples below, you would type:

Me.Recordset.FindFirst "LastName = " & fnQuotes(hldID)

I use this function any type I need to wrap a text string in quotes. The
biggest plus in this for me is that it is easy to read when I am trying to
debug my code. Realistically, I think it is actually less keystrokes than
the other methods, and you don't have to squint to see whether you have the
right number or right type of single/double quotes.

You will need to put fnQuotes in a code module accessible throughout your
database.

Public Function fnQuotes(TextToQuote As Variant, Optional Delimeter As
Variant = Null)

'This function accepts a variant
'The replace function replaces quotation marks in the text with single
quotes
Delimeter = Nz(Delimeter, Chr$(34))
fnQuotes = Delimeter & Replace(Nz(TextToQuote, ""), Chr$(34), Chr$(39))
& Delimeter

End Function

HTH
Dale

jpwgh via AccessMonster.com said:
Thank you, thank you, thank you!!!. I was going crazy trying to get this
to
work. I used a different field which is unique and it worked.
This line:
Me.RecordSet.FindFirst "LastName ="" & hldID & '""

Should be:
Me.RecordSet.FindFirst "LastName ="'" & hldID & "'"

Expanded for readability it is:
Me.RecordSet.FindFirst "LastName =" ' " & hldID & " ' "

But if you have two same last names, you will only get the first one,
which
may not have been the record you were on. For this to work correctly, you
need a unique field. I would suggest adding an autonumber field to your
table and using that. Be sure you create an index for the autonumber
field
for performance.
Thank you for your response. First of all, the field I am using for my
unique field is a numeric field. Some of the code I have seen mentioned
it
Click to expand...
[quoted text clipped - 56 lines]
Any suggestions would be much appreciated. I have been struggling
with this
for a while, but working on nothing but this for the last two days.
Click to expand...
Click to expand...
Click to expand...
 
Dale said:
Once you have decided you are going to use a text field in your FindFirst,
or Where clause, you then have to build up the string, putting the ' in the
right place or using the tripplet (""") of double qoutes. I have an easier
method. I created a function fnQuotes( ) that will wrap the text in
quotation marks, and makes your code so much easier to read. So instead of
one of the examples below, you would type:

Me.Recordset.FindFirst "LastName = " & fnQuotes(hldID)

I use this function any type I need to wrap a text string in quotes. The
biggest plus in this for me is that it is easy to read when I am trying to
debug my code. Realistically, I think it is actually less keystrokes than
the other methods, and you don't have to squint to see whether you have the
right number or right type of single/double quotes.

You will need to put fnQuotes in a code module accessible throughout your
database.

Public Function fnQuotes(TextToQuote As Variant, Optional Delimeter As
Variant = Null)

'This function accepts a variant
'The replace function replaces quotation marks in the text with single
quotes
Delimeter = Nz(Delimeter, Chr$(34))
fnQuotes = Delimeter & Replace(Nz(TextToQuote, ""), Chr$(34), Chr$(39))
& Delimeter
Click to expand...


Dale,

I like the function idea, but won't there be a problem if
the caller specifies Chr(39) in the optional parameter?

It seems to me that it should be:

fnQuotes = Delimeter & Replace(Nz(TextToQuote, ""), _
Delimeter, Delimeter & Delimeter) & Delimeter
 
Linq,

It was my post...

I was trying to return to a last worked on field based on a 'partially
filled' record, specifically I was trying to go back to either the last
completed field, or the very next one that would be empty, awaiting input
data.

It is a form with two main fields:
Run_point_Venue_A
AnswerA

the user is presented with a continous form of half completed addresses,
which are represented by the 'Run_point_Venue_A' field, the task is to match
them with the 'AnswerA' which is offered via the Combo_Answer_A dropdown.

So at the start of the session all the 'AnswerA' fields are blank, and
gradually as the user works down the form, they are filled in.

The reason for wanting to go back to the previous current record is so that
I can run the update (score) query, and place the user right back where he
needs to be.

Here is my current code:

Private Sub Combo_Answer_A_AfterUpdate()
Dim varID As Variant
With CodeContextObject
If (Combo_Answer_A = Run_point_Address_A) Then
answer_box = -1
End If
If (Combo_Answer_A <> Run_point_Address_A) Then
answer_box = 0
End If
If (answer_box = -1) Then
Tempscore = Tempscore + 1
End If

End With
Dim lngKeyVal As Text

lngKeyVal = Me.Point_Quiz_ID

Me.Requery

With Me.RecordsetClone
.FindFirst "[Point_Quiz_ID] =" ' " & hldID & " ' "

If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub


....which works fine in terms of keeping score, updating the field, etc. but
when I requery the form, it goes back to the first record. How can I make it
requery (add the scores total) and still go back to either:

The last worked on field
or the next (blank) field
 
Marshall Barton said:
jpwgh said:
I saw Linq Adams reply to Efandango's question regarding moving to a null
field after a requery... I tried the code provided in the answer but it didn't
work.
Click to expand...


It's difficult to figure out what you did wrong until we can
see what you did (without asking us to chase down some other
thread and guess what you did to it in your form).
--
Marsh
MVP [MS Access]
Click to expand...


Marshall,

It was my post... (and Linq Adams reply didn't work for me either)

I was trying to return to a last worked on field based on a 'partially
filled' record, specifically I was trying to go back to either the last
completed field, or the very next one that would be empty, awaiting input
data.

It is a form with two main fields:
Run_point_Venue_A
AnswerA

the user is presented with a continous form of half completed addresses,
which are represented by the 'Run_point_Venue_A' field, the task is to match
them with the 'AnswerA' which is offered via the Combo_Answer_A dropdown.

So at the start of the session all the 'AnswerA' fields are blank, and
gradually as the user works down the form, they are filled in.

The reason for wanting to go back to the previous current record is so that
I can run the update (score) query, and place the user right back where he
needs to be.

Here is my current code:

Private Sub Combo_Answer_A_AfterUpdate()
Dim varID As Variant
With CodeContextObject
If (Combo_Answer_A = Run_point_Address_A) Then
answer_box = -1
End If
If (Combo_Answer_A <> Run_point_Address_A) Then
answer_box = 0
End If
If (answer_box = -1) Then
Tempscore = Tempscore + 1
End If

End With
Dim lngKeyVal As Text

lngKeyVal = Me.Point_Quiz_ID

Me.Requery

With Me.RecordsetClone
.FindFirst "[Point_Quiz_ID] =" ' " & hldID & " ' "

If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub


....which works fine in terms of keeping score, updating the field, etc. but
when I requery the form, it goes back to the first record. How can I make it
requery (add the scores total) and still go back to either:

The last worked on field
or the next (blank) field
 
efandango said:
Marshall Barton said:
jpwgh said:
I saw Linq Adams reply to Efandango's question regarding moving to a
null
field after a requery... I tried the code provided in the answer but it
didn't
work.
Click to expand...


It's difficult to figure out what you did wrong until we can
see what you did (without asking us to chase down some other
thread and guess what you did to it in your form).
--
Marsh
MVP [MS Access]
Click to expand...


Marshall,

It was my post... (and Linq Adams reply didn't work for me either)

I was trying to return to a last worked on field based on a 'partially
filled' record, specifically I was trying to go back to either the last
completed field, or the very next one that would be empty, awaiting input
data.

It is a form with two main fields:
Run_point_Venue_A
AnswerA

the user is presented with a continous form of half completed addresses,
which are represented by the 'Run_point_Venue_A' field, the task is to
match
them with the 'AnswerA' which is offered via the Combo_Answer_A dropdown.

So at the start of the session all the 'AnswerA' fields are blank, and
gradually as the user works down the form, they are filled in.

The reason for wanting to go back to the previous current record is so
that
I can run the update (score) query, and place the user right back where he
needs to be.

Here is my current code:

Private Sub Combo_Answer_A_AfterUpdate()
Dim varID As Variant
With CodeContextObject
If (Combo_Answer_A = Run_point_Address_A) Then
answer_box = -1
End If
If (Combo_Answer_A <> Run_point_Address_A) Then
answer_box = 0
End If
If (answer_box = -1) Then
Tempscore = Tempscore + 1
End If

End With
Dim lngKeyVal As Text

lngKeyVal = Me.Point_Quiz_ID

Me.Requery

With Me.RecordsetClone
.FindFirst "[Point_Quiz_ID] =" ' " & hldID & " ' "

If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub


...which works fine in terms of keeping score, updating the field, etc.
but
when I requery the form, it goes back to the first record. How can I make
it
requery (add the scores total) and still go back to either:

The last worked on field
or the next (blank) field
Click to expand...

This line has two problems:

..FindFirst "[Point_Quiz_ID] =" ' " & hldID & " ' "

First of all your quotes are mixed up. It ought to read:

..FindFirst "[Point_Quiz_ID] ='" & hldID & "'"

Second, it looks to me like the value to be searched for is held in the
variable lngKeyVal, not hldID

So try replacing the line with:

..FindFirst "[Point_Quiz_ID] ='" & lngKeyVal & "'"
 
I made those changes, but after the requery, it still goes back to the first
record...


my code:

Private Sub Combo_Answer_A_AfterUpdate()

With CodeContextObject
If (Combo_Answer_A = Run_point_Address_A) Then
answer_box = -1
End If
If (Combo_Answer_A < Run_point_Address_A) Then
answer_box = 0
End If
If (answer_box = -1) Then
Tempscore = Tempscore + 1
End If

End With

Dim lngKeyVal As String

lngKeyVal = Me.Point_Quiz_ID

Me.Requery

With Me.RecordsetClone
.FindFirst "[Point_Quiz_ID] ='" & lngKeyVal & "'"

If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub




Stuart McCall said:
efandango said:
Marshall Barton said:
jpwgh wrote:

I saw Linq Adams reply to Efandango's question regarding moving to a
null
field after a requery... I tried the code provided in the answer but it
didn't
work.


It's difficult to figure out what you did wrong until we can
see what you did (without asking us to chase down some other
thread and guess what you did to it in your form).
--
Marsh
MVP [MS Access]
Click to expand...


Marshall,

It was my post... (and Linq Adams reply didn't work for me either)

I was trying to return to a last worked on field based on a 'partially
filled' record, specifically I was trying to go back to either the last
completed field, or the very next one that would be empty, awaiting input
data.

It is a form with two main fields:
Run_point_Venue_A
AnswerA

the user is presented with a continous form of half completed addresses,
which are represented by the 'Run_point_Venue_A' field, the task is to
match
them with the 'AnswerA' which is offered via the Combo_Answer_A dropdown.

So at the start of the session all the 'AnswerA' fields are blank, and
gradually as the user works down the form, they are filled in.

The reason for wanting to go back to the previous current record is so
that
I can run the update (score) query, and place the user right back where he
needs to be.

Here is my current code:

Private Sub Combo_Answer_A_AfterUpdate()
Dim varID As Variant
With CodeContextObject
If (Combo_Answer_A = Run_point_Address_A) Then
answer_box = -1
End If
If (Combo_Answer_A <> Run_point_Address_A) Then
answer_box = 0
End If
If (answer_box = -1) Then
Tempscore = Tempscore + 1
End If

End With
Dim lngKeyVal As Text

lngKeyVal = Me.Point_Quiz_ID

Me.Requery

With Me.RecordsetClone
.FindFirst "[Point_Quiz_ID] =" ' " & hldID & " ' "

If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub


...which works fine in terms of keeping score, updating the field, etc.
but
when I requery the form, it goes back to the first record. How can I make
it
requery (add the scores total) and still go back to either:

The last worked on field
or the next (blank) field
Click to expand...

This line has two problems:

..FindFirst "[Point_Quiz_ID] =" ' " & hldID & " ' "

First of all your quotes are mixed up. It ought to read:

..FindFirst "[Point_Quiz_ID] ='" & hldID & "'"

Second, it looks to me like the value to be searched for is held in the
variable lngKeyVal, not hldID

So try replacing the line with:

..FindFirst "[Point_Quiz_ID] ='" & lngKeyVal & "'"
Click to expand...
 
efandango said:
I made those changes, but after the requery, it still goes back to the
first
record...


my code:

Private Sub Combo_Answer_A_AfterUpdate()

With CodeContextObject
If (Combo_Answer_A = Run_point_Address_A) Then
answer_box = -1
End If
If (Combo_Answer_A < Run_point_Address_A) Then
answer_box = 0
End If
If (answer_box = -1) Then
Tempscore = Tempscore + 1
End If

End With

Dim lngKeyVal As String

lngKeyVal = Me.Point_Quiz_ID

Me.Requery

With Me.RecordsetClone
.FindFirst "[Point_Quiz_ID] ='" & lngKeyVal & "'"

If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub
Click to expand...

Well all I can suggest is that you place a breakpoint on the Me.ReQuery line
and, when it breaks, check the value of lngKeyVal. If it isn't what you
expect then something elsewhere in the form is either setting Point_Quiz_ID
incorrectly or not setting it when it should be.

I say that because the code is just fine and ought to work perfectly.
 
Back
Top