473,692 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Requery, Return to record

MS
Access 97

I want to requery the data being displayed on a form, then I want to return
to the record I was in.

Why doesn't this code work?

Private Sub CmdRefsh_Click( )
Dim RecId As Integer

RecId = Me.TxtID
Me.Requery

With Me.RecordsetClo ne
.FindFirst "[ID] = " & RecId
End With

End Sub


Any help?

Thnaks!
Nov 13 '05 #1
20 10664
Try this:

Dim RecId As Integer
RecId = Me.TxtID
Me.Requery

With me.RecordsetClo ne
.FindFirst "ID" & "=" & RecId
me.Bookmark = .Bookmark
me.RecordsetClo ne.Close
End With

MS wrote:
Access 97

I want to requery the data being displayed on a form, then I want to return to the record I was in.

Why doesn't this code work?

Private Sub CmdRefsh_Click( )
Dim RecId As Integer

RecId = Me.TxtID
Me.Requery

With Me.RecordsetClo ne
.FindFirst "[ID] = " & RecId
End With

End Sub


Any help?

Thnaks!


Nov 13 '05 #2
MS

"lauren quantrell" <la************ *@hotmail.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .
Try this:

Dim RecId As Integer
RecId = Me.TxtID
Me.Requery

With me.RecordsetClo ne
.FindFirst "ID" & "=" & RecId
me.Bookmark = .Bookmark
me.RecordsetClo ne.Close
End With

Perfect!

Thanks :-)
Nov 13 '05 #3
You don't want to close recordsetclone. Since you don't open recordsetclone,
you don't need to close it.

Neil

"lauren quantrell" <la************ *@hotmail.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .
Try this:

Dim RecId As Integer
RecId = Me.TxtID
Me.Requery

With me.RecordsetClo ne
.FindFirst "ID" & "=" & RecId
me.Bookmark = .Bookmark
me.RecordsetClo ne.Close
End With

MS wrote:
Access 97

I want to requery the data being displayed on a form, then I want to

return
to the record I was in.

Why doesn't this code work?

Private Sub CmdRefsh_Click( )
Dim RecId As Integer

RecId = Me.TxtID
Me.Requery

With Me.RecordsetClo ne
.FindFirst "[ID] = " & RecId
End With

End Sub


Any help?

Thnaks!

Nov 13 '05 #4
Suggest you dim RecID as Long. Integer only goes to 32,000. If you have more
records than that, the code won't work. Long goes to 2 trillion.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com
"MS" <Em***@Myemail. com> wrote in message
news:Fk******** ***********@new s-server.bigpond. net.au...

"lauren quantrell" <la************ *@hotmail.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .
Try this:

Dim RecId As Integer
RecId = Me.TxtID
Me.Requery

With me.RecordsetClo ne
.FindFirst "ID" & "=" & RecId
me.Bookmark = .Bookmark
me.RecordsetClo ne.Close
End With

Perfect!

Thanks :-)

Nov 13 '05 #5
MS

"PC Datasheet" <no****@nospam. spam> wrote in message
news:YA******** *********@newsr ead3.news.atl.e arthlink.net...
Suggest you dim RecID as Long. Integer only goes to 32,000. If you have
more
records than that, the code won't work. Long goes to 2 trillion.

Thanks for that.

Now, my code is.....

Private Sub CmdRefsh_Click( )
On Error GoTo Err_CmdRef

Dim RecId As Long

RecId = Me.TxtID

Me.Requery

With Me.RecordsetClo ne
.FindFirst "ID" & "=" & RecId
Me.Bookmark = .Bookmark
End With

Exit_CmdRef:
Exit Sub

Err_CmdRef:
MsgBox Err.Description & ". Moving to first record"

Resume Exit_CmdRef
End Sub
It works fine, except if I'm in the first record. If I run it while in the
first record on the form, it "returns" to that record, but then I get an
error (end of recordset) if I try to navigate to the next record.

Any ideas

Nov 13 '05 #6
In what way are you "navigating " to the next record? This code ends when it
returns you to the original record. You must be navigating through some
other means. If it's with the navigation buttons at the bottom of the form,
then you should go to the "new" record. If you're using a custom navigation
button, then the problem is probably there, and you should post the code
used there. But this code ends when it takes you back to the original
record, and, from what I understand, that part's working fine.

By the way, you can change:

.FindFirst "ID" & "=" & RecId

to simply:

.FindFirst "ID=" & RecId

Neil
"MS" <Em***@Myemail. com> wrote in message
news:28******** ***********@new s-server.bigpond. net.au...

"PC Datasheet" <no****@nospam. spam> wrote in message
news:YA******** *********@newsr ead3.news.atl.e arthlink.net...
Suggest you dim RecID as Long. Integer only goes to 32,000. If you have
more
records than that, the code won't work. Long goes to 2 trillion.

Thanks for that.

Now, my code is.....

Private Sub CmdRefsh_Click( )
On Error GoTo Err_CmdRef

Dim RecId As Long

RecId = Me.TxtID

Me.Requery

With Me.RecordsetClo ne
.FindFirst "ID" & "=" & RecId
Me.Bookmark = .Bookmark
End With

Exit_CmdRef:
Exit Sub

Err_CmdRef:
MsgBox Err.Description & ". Moving to first record"

Resume Exit_CmdRef
End Sub
It works fine, except if I'm in the first record. If I run it while in the
first record on the form, it "returns" to that record, but then I get an
error (end of recordset) if I try to navigate to the next record.

Any ideas

Nov 13 '05 #7
I sent you the code "ID" & "=" & recID only because I do this sort of
thing from a public function where I re-use the code for many different
forms where I need the functionality to return to the selected record
before a requery:

For example, on the form I may have an On_Click procedure that opens
something, something like this:

Private Sub CmdRefsh_Click( )
Dim myID as long
myID = me.RecID

'run some code here that plays with the record, then requeries the
records...

Call ReturnAfterRequ ery(me.formname ,"RecID",myI D)

End Sub

' put the function below in a module which can then be called from any
form:

Function ReturnAfterRequ ery(myFormName as String, myFieldName as
String, myID as Long)

'myID is the currently selected record
'myFieldName is the name of the ID field of the recordset that will be
requeried
'myFormName is the name of the form that is being requeried

Me.Requery
With Forms(myFormNam e).RecordsetClo ne
Forms(myFormNam e).FindFirst myFieldName & "=" & myID
Forms(myFormNam e).Bookmark = .Bookmark
End With
End function


Neil wrote:
In what way are you "navigating " to the next record? This code ends when it returns you to the original record. You must be navigating through some other means. If it's with the navigation buttons at the bottom of the form, then you should go to the "new" record. If you're using a custom navigation button, then the problem is probably there, and you should post the code used there. But this code ends when it takes you back to the original record, and, from what I understand, that part's working fine.

By the way, you can change:

.FindFirst "ID" & "=" & RecId

to simply:

.FindFirst "ID=" & RecId

Neil
"MS" <Em***@Myemail. com> wrote in message
news:28******** ***********@new s-server.bigpond. net.au...

"PC Datasheet" <no****@nospam. spam> wrote in message
news:YA******** *********@newsr ead3.news.atl.e arthlink.net...
Suggest you dim RecID as Long. Integer only goes to 32,000. If you have more
records than that, the code won't work. Long goes to 2 trillion.

Thanks for that.

Now, my code is.....

Private Sub CmdRefsh_Click( )
On Error GoTo Err_CmdRef

Dim RecId As Long

RecId = Me.TxtID

Me.Requery

With Me.RecordsetClo ne
.FindFirst "ID" & "=" & RecId
Me.Bookmark = .Bookmark
End With

Exit_CmdRef:
Exit Sub

Err_CmdRef:
MsgBox Err.Description & ". Moving to first record"

Resume Exit_CmdRef
End Sub
It works fine, except if I'm in the first record. If I run it while in the first record on the form, it "returns" to that record, but then I get an error (end of recordset) if I try to navigate to the next record.

Any ideas


Nov 13 '05 #8
Oops,
I saw it as it was saving...
Tahe out the "Me.requery ."
It should read:

Function ReturnAfterRequ ery(myFormName as String, myFieldName as
String, myID as Long)

'myID is the currently selected record
'myFieldName is the name of the ID field of the recordset that will be
requeried
'myFormName is the name of the form that is being requeried

With Forms(myFormNam e).RecordsetClo ne
Forms(myFormNam e).FindFirst myFieldName & "=" & myID
Forms(myFormNam e).Bookmark = .Bookmark
End With
End function

lauren quantrell wrote:
I sent you the code "ID" & "=" & recID only because I do this sort of
thing from a public function where I re-use the code for many different forms where I need the functionality to return to the selected record
before a requery:

For example, on the form I may have an On_Click procedure that opens
something, something like this:

Private Sub CmdRefsh_Click( )
Dim myID as long
myID = me.RecID

'run some code here that plays with the record, then requeries the
records...

Call ReturnAfterRequ ery(me.formname ,"RecID",myI D)

End Sub

' put the function below in a module which can then be called from any form:

Function ReturnAfterRequ ery(myFormName as String, myFieldName as
String, myID as Long)

'myID is the currently selected record
'myFieldName is the name of the ID field of the recordset that will be requeried
'myFormName is the name of the form that is being requeried

Me.Requery
With Forms(myFormNam e).RecordsetClo ne
Forms(myFormNam e).FindFirst myFieldName & "=" & myID
Forms(myFormNam e).Bookmark = .Bookmark
End With
End function


Neil wrote:
In what way are you "navigating " to the next record? This code ends when it
returns you to the original record. You must be navigating through

some
other means. If it's with the navigation buttons at the bottom of the form,
then you should go to the "new" record. If you're using a custom navigation
button, then the problem is probably there, and you should post the

code
used there. But this code ends when it takes you back to the

original
record, and, from what I understand, that part's working fine.

By the way, you can change:

.FindFirst "ID" & "=" & RecId

to simply:

.FindFirst "ID=" & RecId

Neil
"MS" <Em***@Myemail. com> wrote in message
news:28******** ***********@new s-server.bigpond. net.au...

"PC Datasheet" <no****@nospam. spam> wrote in message
news:YA******** *********@newsr ead3.news.atl.e arthlink.net...
> Suggest you dim RecID as Long. Integer only goes to 32,000. If
you have> more
> records than that, the code won't work. Long goes to 2 trillion.
>
Thanks for that.

Now, my code is.....

Private Sub CmdRefsh_Click( )
On Error GoTo Err_CmdRef

Dim RecId As Long

RecId = Me.TxtID

Me.Requery

With Me.RecordsetClo ne
.FindFirst "ID" & "=" & RecId
Me.Bookmark = .Bookmark
End With

Exit_CmdRef:
Exit Sub

Err_CmdRef:
MsgBox Err.Description & ". Moving to first record"

Resume Exit_CmdRef
End Sub
It works fine, except if I'm in the first record. If I run it
while
in the first record on the form, it "returns" to that record, but then I get an error (end of recordset) if I try to navigate to the next record.

Any ideas


Nov 13 '05 #9
Neil wrote:
You don't want to close recordsetclone. Since you don't open recordsetclone,
you don't need to close it.


Exactly.

Also, if you have any other code that uses recordsetclone without a
requery, keep in mind that the bookmark for recordsetclone stays where
you last left off in .findfirst. If on the next time you use
recordsetclone to search for something, if the next record is BEFORE the
last, the findfirst starts where it last left off and goes to eof and
you won't find it.

For any use of a recordsetclone, it might be good practice to add in a
..movefirst at ythe end of your procedure, just in case.

In MS's case, since s/he is requerying before using recordsetclone, the
recordsetclone is reconstructed and starts at the beginning anyway.
But, IMO, it's still good practice to "reset" and .movefirst. I find
rsclones to be very useful sometimes.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2373
by: Tony Stoker | last post by:
I have a .Net web app that adds a record to a SQL database. After the user adds their record I want to have a link that will link them to their new record! The recordID is a AutoNumber in the SQL server... How do I return the recordID after I have added the record?
6
14396
by: CoolRiyaz | last post by:
I want to return record number with my query output. I dont' want to use IDENTITY function and temp table for this purpose as I am dealing with very huge data. any suggestions...
0
1364
by: vinodkus | last post by:
I am beginner in ASP 1how can I return a recordset through a function in asp. 2>Suppose there are two buttons in asp. I want if I presss the first button then it should go to page1.asp and if I press the second button then it should go to page2.asp. Is it possible by any way. Please Help Me Thank You.
1
1910
by: Peader | last post by:
Hi, I'm trying to complete a form that will search previous results entered in a table and return the row items based on a ID number. My current code is below: Private Sub cmd22_Click() Dim strStudentRef As String Dim strSearch As String DoCmd.ShowAllRecords DoCmd.GoToControl ("ID")
2
1311
by: atrain | last post by:
Hi, I understand that MS Access is not really meant for multi-users if robust security is the ultimate concern...but I'm trying to create a work-around where a user logs in and is prompted to enter two parameters to pull up their individual record. For example, they would put in an ID number and a Password...these two parameters are used to lookup in a table the exact record that matches both the ID number and password and returns information...
0
8603
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8955
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8800
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7627
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5818
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4323
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4556
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2970
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2236
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.