Navigating Recordset/Updating Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Newbie help needed

I'm trying to navigate a recordset and display the value of a field on a form as I navigate the recordset

I have one text box control (txtRandomID) on the form and a command button which calls the OnClick event below. My record opens fine and I seem to 'walk' the file okay... however the text box control remains empty and is not updated with the appropriate field value as the records change. What am I missing

Private Sub cmdADOTest_Click(

Dim rstImages As ADODB.Recordse
Dim lngCurrentRecordNumber As Lon
Dim lngCountTotalRecords As Lon
Set rstImages = New ADODB.Recordse

rstImages.Open "IMGCTRL", CurrentProject.Connection, adOpenStatic,
adLockReadOnl

MsgBox ("Recordset Opened"

Set Me.Recordset = rstImage

With rstImage
If .RecordCount > 0 The
.MoveFirs
Do Until .EO
Me.txtRandomID.Value = .Fields("RANDOMID"
.MoveNex
Loo
End I
.Clos
End Wit

MsgBox ("All Records Navigated"

Set rstImages = Nothin

End Sub
 
Your problem is that the code goes through the whole
recordset to EOF.

You have to stop reading the recordset when you reach the
value you want.

If you want to move through the recordset one record at a
time each time you click the button you will have to keep
track of the current position and move only one position
forward with each click.

If you refer to the recordset with a form level variable
declared outside the OnClick event and use a single
MoveNext inside the OnClick event you should get the
result you are after.

Rod Scoullar
 
Let me clarify a bit more. I actually do want to navigate through the entire recordset to EOF while performing certain actions on each record as the loop navigates each consecutive record. I want the text box on the form to display a field in the recordset (primary key) that lets me know at what point in the recordset the loop is at. For example

Open recordset
Start Of Loop
Display field value on form to let the user know at what stage the process is at
Perform miscellaneous action on record
Move to next record
Rinse and repeat until all records in file have been navigated
 
On a modern machine, Access will process several thousand records per
second, unless the processing is very intensive. However the textbox/screen
updates will only happen a few per second. Somehow you will have to slow
things down if you want to see individual records. Sprinkling a few
DoEvents and Refresh commands around might help. Alternatively perhaps you
could have two buttons - "process next record", and "process till next
click".

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
STEP said:
Let me clarify a bit more. I actually do want to navigate through the
Click to expand...
entire recordset to EOF while performing certain actions on each record as
the loop navigates each consecutive record. I want the text box on the form
to display a field in the recordset (primary key) that lets me know at what
point in the recordset the loop is at. For example
 
If all you are after is an progress indicator, would it be better to use a
progressbar?
STEP said:
Let me clarify a bit more. I actually do want to navigate through the
Click to expand...
entire recordset to EOF while performing certain actions on each record as
the loop navigates each consecutive record. I want the text box on the form
to display a field in the recordset (primary key) that lets me know at what
point in the recordset the loop is at. For example
 
Good idea... progress bar may be a good final solution, although still looking for the solution to the original problem just from a 'learning' perspective. How do I get the code called by the OnClick event on the command button to update the display of a field in an ADO recordset in a text box on my form?

----- Mike S. wrote: -----

If all you are after is an progress indicator, would it be better to use a
progressbar?
STEP said:
Let me clarify a bit more. I actually do want to navigate through the
Click to expand...
entire recordset to EOF while performing certain actions on each record as
the loop navigates each consecutive record. I want the text box on the form
to display a field in the recordset (primary key) that lets me know at what
point in the recordset the loop is at. For example
 
Still looking for a little help with the original problem. Insight appreciated.

----- STEP wrote: -----

Newbie help needed:

I'm trying to navigate a recordset and display the value of a field on a form as I navigate the recordset.

I have one text box control (txtRandomID) on the form and a command button which calls the OnClick event below. My record opens fine and I seem to 'walk' the file okay... however the text box control remains empty and is not updated with the appropriate field value as the records change. What am I missing?

Private Sub cmdADOTest_Click()

Dim rstImages As ADODB.Recordset
Dim lngCurrentRecordNumber As Long
Dim lngCountTotalRecords As Long
Set rstImages = New ADODB.Recordset

rstImages.Open "IMGCTRL", CurrentProject.Connection, adOpenStatic, _
adLockReadOnly

MsgBox ("Recordset Opened")

Set Me.Recordset = rstImages

With rstImages
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
Me.txtRandomID.Value = .Fields("RANDOMID")
.MoveNext
Loop
End If
.Close
End With

MsgBox ("All Records Navigated")

Set rstImages = Nothing

End Sub
 
Back
Top