Position in Continuous Forms

  • Thread starter Thread starter Graham Payton
  • Start date Start date
G

Graham Payton

Hi


I have a continuous form which updates on a timer every 3 minutes.

Is there any way that I can stored the top row of the form that I currently
on. As soon as the update runs it scrolls the continuous form right down to
the bottom record.

I thought if I could store the row I was on, let the timer update, and then
move back to the row then it would save keep having to scroll up and down
all the time.

Any help would be much appreciated.


Thanks


Graham
 
Hi


I have a continuous form which updates on a timer every 3 minutes.

Is there any way that I can stored the top row of the form that I currently
on. As soon as the update runs it scrolls the continuous form right down to
the bottom record.

I thought if I could store the row I was on, let the timer update, and then
move back to the row then it would save keep having to scroll up and down
all the time.
Click to expand...

In the timer code capture the unique record id prior to doing the
requery. Do the requery and then use the forms
RecordsetClone.FindFirst method to locate the record. Then set the
form's Bookmark = to the RecordsetClones Bookmark.

- Jim
 
Jim Allensworth said:
In the timer code capture the unique record id prior to doing the
requery. Do the requery and then use the forms
RecordsetClone.FindFirst method to locate the record. Then set the
form's Bookmark = to the RecordsetClones Bookmark.

- Jim
Click to expand...

Hi Jim


That's great, thanks for that.

I'm just having a bit of trouble getting the records unique id. I want to
use the persons name that is attached to the record. But can't seem to get
the details for the row currently viewing at the top of my continuous form.

Any ideas please


Thanks


Graham
 
Hi Jim


That's great, thanks for that.

I'm just having a bit of trouble getting the records unique id. I want to
use the persons name that is attached to the record. But can't seem to get
the details for the row currently viewing at the top of my continuous form.
Click to expand...
Graham whatever is unique depends on the record source. Usually, we
are talking about a primary key - or whatever field defines the
record. Names can be problematic (there are quite a few Grahams and
Jims out there) so if you can include in the form's recordsource the
field for the PersonID, or whatever you might use, then you can easily
use that.

- Jim
 
You can get the "current record" information in your VBA code. That may or
may not be the record at the top of the list of records shown, but it is
really all you have.

Larry Linson
Microsoft Access MVP
 
The only problem with the CurrentRecord property is that it it
different with each instance of the recordset. If a new record is
created at the top of the order then *that* would be the new # 1
record when requeried.

Admittedly it is usually not an issue, but if the app. is refreshing
presumably it is for a reason.

- Jim
 
Hi


Thanks to Jim and Larry for their input so far. Much appreciated guys!

I think I should put a bit more detail here to try to help explain what I am
trying to do.

I am running an ADP which is connected to a database on my SQL server.
Every three minutes a query runs which returns an ADODB recordset containing
information about all the users that are currently working on my database.
This can sometimes be up to 60 users at any one time!

Set cnn = CurrentProject.Connection
Set cmdSQL.ActiveConnection = cnn
cmdSQL.CommandText = "GetCanvassersCallRates '" & strTheDate & "'"
Set rstCanvassersCallRates = cmdSQL.Execute()

I then set this recordset current for my form. And finally set the text
boxes on the unbound form to point to the relevant fields of the recordset.

Set Me.Recordset = rstCanvassersCallRates
Me!txtCanvasserName.ControlSource = "CanvasserName"
Me!txtComputer_Name.ControlSource = "Computer_Name"
Me!txtExtension_No.ControlSource = "Extension_No"
Me!txtFirstCall.ControlSource = "First_Call"
Me!txtLastCall.ControlSource = "Last_Call"
Me!txtCallsMade.ControlSource = "Calls_Made"
Me!txtBracketsLost.ControlSource = "Brackets_Lost"
Me!txtTimeWorked.ControlSource = "Time_Worked"
Me!txtCallAverage.ControlSource = "Call_Average"

As the query sorts the recordset the way that I want it to then I could find
the record that I was on by doing some sort of .move or .find on the
recordset. Unfortunately the .FindFirst doesn't work on my recordset as
this is a DAO command and my recordset it ADODB.

I think I really just need some way of finding out how many lines down the
side scrollbar has gone. Then before I do the update I can loop through the
recordset that many times and store the CanvasserName field. Run the
update. And then do some sort of .find, as previously mentioned, on the
forms recordset.

I hope this helps give a bit more detail.

It is almost working, so the final bits of help would be very much
appreciated.


Thanks again in advance


Graham
 
Hi Stephen


Thanks for the reply.

I have tried this property. Got it from your website actually :o)

It only appears to list the top record if you have it selected though. If
you are just simply scrolling through it then it doesn't seem to work.

I also tried some of the other code that came with the example, using
functions. But this crashed my ADP horribly and it hasn't opened up
properly again since. Good job I made a backup ;o)

I'm not sure that there is an answer to my question here.

The only thing I am thinking of is to set the screen to the top record to
start with. Then have a variable to store what position I am at.

Using the OnMouseWheel event, or whatever it is called, increase or decrease
this value. This will then tell me what record in the recordset that I am
on. (But unfortunately this will only work on scrolling the mouse wheel!)

From here I can loop through the recordset to that value and then grab the
CanvasserName to match that record.

Then all I will need to do is refresh the recordset. Loop through again to
find the CanvasserName that I previously had, set the scroll value to be
that record, then do some sort of .Find command on my recordset to jump to
it.

Sounds a bit long winded, but I think the theory is sound.

I just can't seem to get the .Find or whatever to work. Have you got any
ideas about that? I am using an ADODB recordset which is returned from a
stored procedure.


Thanks again for the help


Cheers


Graham
 
To allow for the user not selecting a record but simply repositioning
the SCrollBar then use the SetGetSB samples from the MDB you downloaded.
They will work without the user having to select a current/specific row.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Back
Top