Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Move, Movenext, Moveprevious,movelast Records

let us assume that i have already connection, how can i browse records MOVEFIRST, MOVELAST, MOVENEXT, MOVEPREVIOUS with sql command and in reference of WHERE RECSET!lNAME=LNAMETXT.TXT as my RECORD POINTER. Connectstrings
Dim conn As New ADODB.Connection
Dim recset As New ADODB.Recordset
Set recset = New ADODB.Recordset
recset.CursorLocation = adUseClient
recset.CursorType = adOpenKeyset
recset.LockType = adLockOptimistic

recset.Open "SELECT * FROM emp_tbl order by lname asc", conn

If Not recset.EOF Then
empidtxt = recset!Emp_Id
lnametxt = recset!lname
fnametxt = recset!fname
mitxt = recset!mi
curraddtxt = recset!curradd
homaddtxt = recset!homadd
celnotxt = recset!celno
bdate.Value = recset!bdate
dhired.Value = recset!dhired
salarytxt = recset!salary
emyconnametxt = recset!emyconname
emyconpontxt = recset!emyconpon
titlecombo.Text = recset!Title
sssnotxt = recset!sssno
phnotxt = recset!phno
statempcombo.Text = recset!statemp
positxt = recset!posi
agetxt = recset!age
lognametxt = recset!logname
logpasstxt = recset!logpass

If recset.BOF Then
empidtxt = recset!Emp_Id
lnametxt = recset!lname
fnametxt = recset!fname
mitxt = recset!mi
curraddtxt = recset!curradd
homaddtxt = recset!homadd
celnotxt = recset!celno
bdate.Value = recset!bdate
dhired.Value = recset!dhired
salarytxt = recset!salary
emyconnametxt = recset!emyconname
emyconpontxt = recset!emyconpon
titlecombo.Text = recset!Title
sssnotxt = recset!sssno
phnotxt = recset!phno
statempcombo.Text = recset!statemp
positxt = recset!posi
agetxt = recset!age
lognametxt = recset!logname
logpasstxt = recset!logpass
End If
End If
End If
Set recset = Nothing
Set conn = Nothing

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
MoveNext, MoveLast, MovePrevious, MoveFirst.
Hi All,
This is the 1st time i'm learning to code for MoveNext. Can somebodyguide me how to code on search record using moveNext?
For example, I select the 1st record in the combo box which isRecord 1. Then when i click "Next", how can i retrieve the next recordwith all the fields are displayed.

Khai Sze

Yet Another MoveFirst/MovePrevious/MoveNext/MoveLast Methods Thread...
I have four buttons on a control matrix named cmdViewFact. They are as follow:

|< << >> >|
(3) (4) (5) (6)

So, I'm using them to move around a recordset that I get from a DataEnvironment object. I have it set to client cursor, optimistic, all records, cache size = 100...

Anyway, here's the code:

Case 3
cmdViewFact(3).Enabled = False
cmdViewFact(4).Enabled = False
cmdViewFact(5).Enabled = True
cmdViewFact(6).Enabled = True
Exit Sub
Case 4
If DE.rsAllFact.BOF Then
cmdViewFact_Click (3)
End If
Case 5
If DE.rsAllFact.EOF Then
cmdViewFact_Click (6)
End If
Case 6
cmdViewFact(3).Enabled = True
cmdViewFact(4).Enabled = True
cmdViewFact(5).Enabled = False
cmdViewFact(6).Enabled = False
Exit Sub

As you can see, I disable corresponding cursor movement when BOF or EOF are True. However, it seems as if it's not looking at BOF/EOF, 'cause just doing the recommended method:

If DE.rsAllFact.EOF Then
DE.rsAllFact.MovePrevious ' MoveLast should be also possible.
End If

always gets the same error:
Error '-2147217842': Operation cancelled.

I can MoveNext or MovePrevious as long as I don't 'get close' to the end/beggining of the recordset. Also, if I'm at the last position, moving to the first OR to the previous record gets the error

Any ideas on why this is happening, and/or how to correct it?

EDIT: BTW, trapping the error means losing the connection to the DB

ADO, No Moveprevious And Movelast
Hi everyone,

I usually use DAO to connect to a database, but now i am using microsoft sql server. I use ADO now. when i add a the object to my form and i connect it with the database it works.....
but i also want to add an ADO connection in the code.....this i use a sql query to retrieve my data out of the database......
but i can only use movenext and movefirst on that recordset. when i use movelast or moveprevious i get an error. i saw something in msdn that it has to have a backwards cursor or something....:s
How can i make it so that i can use moveprevious and movelast in my recordset??????
Now i store the position of my record in a counter and thats how i know wich is the previous record.....but thats not very usefull :s.....

thx !!!


.MoveNext &amp; .MovePrevious
Will somebody please help me understand what I'm doing wrong in this simple record retrieval routine?

Moveprevious Movenext Again
Hi again,
sorry to bother with the same question but i can't seem to tackle this one
this is the code:

Option Explicit
Dim oConn As ADODB.Connection
Dim adoRecordSet As ADODB.Recordset

Private Sub Command1_Click()
End Sub

Private Sub Command2_Click()

End Sub

Private Sub Command3_Click()

    If MsgBox("Are you sure you want to delete record", vbOKCancel + vbExclamation, "Deleting Record") = vbOK Then

        If adoRecordSet.EOF Then
            MsgBox "Last Record"
        End If
    End If
End Sub

Private Sub Form_Load()

Set oConn = New ADODB.Connection

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:2000ver.mdb;" & _
           "User Id=admin;" & _
  If oConn.State = adStateOpen Then
        MsgBox "Connected to database", vbOKOnly + vbInformation, App.ProductName
        MsgBox "Connection failed!", vbOKOnly + vbCritical, App.ProductName
    End If

  Me.Top = (Screen.Height - Me.Height) / 2
  Me.Left = (Screen.Width - Me.Width) / 2
End Sub
Public Sub Connect_RS()
Dim strSql As String
'Prepare the recordset object
Set adoRecordSet = New ADODB.Recordset
strSql = "select * from testing"

With adoRecordSet
 'The only cursor allowed with client side location is Static
  .CursorType = adOpenStatic
 'Client side cursor
  .CursorLocation = adUseClient
 'Guarantee that the record being edited can be saved
  .LockType = adLockPessimistic
 'Source should be a SQL statement indicating where to retrieve data from
  .Source = strSql
 'Recordset need to what connection to use
  .ActiveConnection = oConn
 'Open the recordset using the information supplied
 End With
End Sub

Public Sub populateFields()

With adoRecordSet
 Text1.Text = !companyid
 Text2.Text = !CompanyName
End With

End Sub

this is a small version of the real one and the moveprevious and movenext don't work.
can anyone see where the problem is

thanks in advance


MoveNext And MovePrevious Problem

I have a problem with my program. I have three tables in my access 2000 database. I select various information out of the in my VB code. The problem I am having is that when I want to go through the recordset my movefirst and movelast command buttons work fine but my moveprevious and movenext buttons you have to click on six times before the change the data in my form. Anyone have any idea why this is happening? If you do any help would be appreciated. Thank you.

Here is what my code looks like:
Option Explicit

'Connection to Database
Private WithEvents connCustomer As ADODB.Connection
'Connection to recordset
Private WithEvents rsRecordsetCustomer As ADODB.Recordset
Private WithEvents connCall As ADODB.Connection
'Connection to recordset
Private WithEvents rsRecordsetCall As ADODB.Recordset

Private Sub Form_Load()
Top = 300
Width = 9915
Height = 8970
Left = 1000
Call LoadCustomerData
End Sub

Private Sub LoadCustomerData()
'Varibles for connection string
Dim strConnectCustomer As String
Dim strProviderCustomer As String
Dim strDataSourceCustomer As String
Dim strDatabaseNameCustomer As String

strProviderCustomer = "Provider = Microsoft.Jet.OLEDB.4.0;"
'Directory that the program is executing in
strDataSourceCustomer = "S:Database"
'Name of database
strDatabaseNameCustomer = "TBS Database.mdb;"
'Append on the name of the database
strDataSourceCustomer = "Data Source=" & strDataSourceCustomer & _
'Make connection String
strConnectCustomer = strProviderCustomer & strDataSourceCustomer
'Connection object
Set connCustomer = New ADODB.Connection
'Use server because database is on server
connCustomer.CursorLocation = adUseServer
'Open conection
connCustomer.Open strConnectCustomer

'Prepare recordset
Set rsRecordsetCustomer = New ADODB.Recordset
rsRecordsetCustomer.CursorType = adOpenStatic
'Application is using server side cursor
rsRecordsetCustomer.CursorLocation = adUseServer
'Make sure record that is being edited can be saved
rsRecordsetCustomer.LockType = adLockPessimistic
'Select statement to retrieve data from database
rsRecordsetCustomer.Source = _
"SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName, CompanyName, Address, City, State, Zip, WorkPhone, Customer.MobilPhone, FaxNumber, Customer.HomePhone, Email, CallID, CallInDate, CallInTime, CallInMessage, CallResolved, Call.CustomerID, CallSeverity, CallTakenBy, CallFor, Employee.EmployeeID, Employee.FirstName" _
& " FROM Customer, Call, Employee WHERE Customer.CustomerID = Call.CustomerID GROUP BY CallResolved, CallInDate, CallInTime, Customer.CustomerID, Customer.FirstName, Customer.LastName, CompanyName, Address, City, State, Zip, WorkPhone, Customer.MobilPhone, FaxNumber, Customer.HomePhone, Email, CallID, CallInMessage, Call.CustomerID, CallSeverity, CallTakenBy, CallFor, Employee.EmployeeID, Employee.FirstName;"
'Let record set know what connection to use
rsRecordsetCustomer.ActiveConnection = connCustomer
'Open Recordset
Call LoadDataInControls
'Close connection
'Set connCustomer = Nothing

End Sub

Private Sub LoadDataInControls()
'If recordset is at end of recordset exit sub
'If rsRecordsetCustomer.BOF = True Or rsRecordsetCustomer = True Then
'Exit Sub
'End If
'Fill in text boxes with data from database
lblCustomerID.Caption = ("Customer ID: ") & rsRecordsetCustomer.Fields("Customer.CustomerID").Value
txtFirstName.Text = rsRecordsetCustomer.Fields("Customer.FirstName").Value
txtLastName.Text = rsRecordsetCustomer.Fields("LastName").Value
txtCompany.Text = rsRecordsetCustomer.Fields("CompanyName").Value
txtEmail.Text = rsRecordsetCustomer.Fields("Email").Value
txtFaxNumber.Text = rsRecordsetCustomer.Fields("FaxNumber").Value
txtMobilPhone.Text = rsRecordsetCustomer.Fields("MobilPhone").Value
txtHomePhone.Text = rsRecordsetCustomer.Fields("HomePhone").Value
txtZip.Text = rsRecordsetCustomer.Fields("Zip").Value
txtState.Text = rsRecordsetCustomer.Fields("State").Value
txtCity.Text = rsRecordsetCustomer.Fields("City").Value
txtAddress.Text = rsRecordsetCustomer.Fields("Address").Value
txtWorkPhone.Text = rsRecordsetCustomer.Fields("Workphone").Value
txtCallInDate.Text = rsRecordsetCustomer.Fields("CallInDate").Value
txtCallInTime.Text = rsRecordsetCustomer.Fields("CallInTime").Value
txtSeverity.Text = rsRecordsetCustomer.Fields("CallSeverity").Value
txtNotes.Text = rsRecordsetCustomer.Fields("CallInMessage").Value
End Sub

Private Sub cmdFirstCustomer_Click()

If rsRecordsetCustomer.BOF = False Then
'Move to the first record in the record set
ElseIf rsRecordsetCustomer.BOF = True _
And rsRecordsetCustomer.EOF = True Then

MsgBox "There is no data in the record set!"
End If
Call LoadDataInControls
End Sub

Private Sub cmdLastCustomer_Click()

If rsRecordsetCustomer.EOF = False Then
'Move to the last record in the record set.
ElseIf rsRecordsetCustomer.BOF = True _
And rsRecordsetCustomer.EOF = True Then

MsgBox "There is no data in the record set!"
End If
Call LoadDataInControls
End Sub

Private Sub cmdNextCustomer_Click()

If rsRecordsetCustomer.EOF = False Then

If rsRecordsetCustomer.EOF Then
End If
'Check to see if there is any data in the recordset
If rsRecordsetCustomer.BOF Then
MsgBox "There is no data in the record set!"
End If
End If
Call LoadDataInControls
End Sub

Private Sub cmdPreviousCustomer_Click()

If rsRecordsetCustomer.BOF = False Then
'Check to see if you are at the front of the record set

If rsRecordsetCustomer.BOF = True Then
End If
If rsRecordsetCustomer.EOF Then
'Check to see if there is any data in the record set.
MsgBox "There is no data in the record set!"
End If
End If
Call LoadDataInControls
End Sub

MoveNext/MovePrevious Problem
This should be an easy problem to fix but for some reason I can't get it to work. I'm opening a DBase file through VB 6 and displaying the contents of each record by using text boxes.

I've got 4 buttons...First, Last, Next, and move to the respective records in the DBase file. I used MoveFirst and MoveLast to move to the first and last records and all works well. But, when I try to use MoveNext and MovePrevious, nothing works. For whatever reason, I remain on the same record. I added an if statement that checked for BOF/EOF, but that didn't help. In one variation of my code, I was able to advance 1 record. When I tried to advance another, I remained on the same record.

Any ideas as to why MoveNext/MovePrevious won't work? Thanks!

Movenext And Moveprevious After Search
Currently, I have a program that displays student information from a database.

Within the application, the user can use the Next (Movenext) and Back (Moveprevious) buttons to navigate through the database one record at a time.

Within the application there is also a search feature that allows the user to type in a name and click on the search button. If name typed in matches a student in the database, their record is displayed.

The problem is that when the user enters a name and the record is displayed, the movenext and moveprevious buttons will bring the user BACK to the point in the database where he/she was before the search was initiated.

For example, there are 26 students in the database (1 for each of the letters in the alphabet). When the application comes up the first student is displayed (Student A). If the user then initiates a search for Student Q, the information for Student Q is displayed. If the user then clicks the next button, the information for student B is displayed.

Is there a way to display the record information for Student R, since he/she is the next person in the database?

Thanks in Advance!

Movenext Moveprevious In Recordset
I have a problem. I have an ADO control that links several fields in a student database to text boxes in a VB6 form. When I use the Movenext or Moveprevious function they work as expected.

I also have a search box that searchs for an instance of the last name of a student, this also works as expected and populates the text boxes with the correct informtion.

However, how can I go about having a user do a search for a student's last name and then using the movenext and moveprevious button. If I hit the button after a search I get the following error.

Run-time error
The changes you requested to the table were not successful because they would create duplicate values.

Thanks in advance!

Moveprevious And Movenext Question
hello forum!

i am having some trouble with ado's moveprevious and movenext and i hope that you could help me figure it out what is my problem with my program

i have a flexgrid that displays 20 records and i have a previous and next button.

it works well with previous and next button but when i pressed previous on its first load... it doesnt move to the next record how can i overcome this situation

Private Sub cmdNext_Click()
End Sub

Private Sub cmdPrevious_Click()
Dim ctrX As Integer
For ctrX = 0 To 39
If myRecset.BOF = True Then
Exit For
End If
Next ctrX
End Sub

Sub gridFill()
Dim ctrX As Integer
For ctrX = 1 To 20
If myRecset.BOF = True Or myRecset.EOF = True Then
Exit For
End If
With FlexGrid
.Rows = ctrX + 1
.TextMatrix(ctrX, 0) = myRecset.Fields(0)
.TextMatrix(ctrX, 1) = myRecset.Fields(1)
.TextMatrix(ctrX, 2) = myRecset.Fields(2)
End With
Next ctrX
End Sub

i hope you can help me with my code guys... i cant figure it out how to code this one

MoveNext, MovePrevious In An .ini File?
Hello all,
I would like to know if it's possible to navigate an .ini file like you can do with an Access database. In Access you can create buttons to MoveFirst, MoveNext, etc. but I would like this functionality for an .ini file.

I don't want to use an Access database because the setup of the db alone would be about 10x too big. It's only a small file to store a few preferences.

Any advice or examples are greatly appreciated,

MoveNext/MovePrevious {Solved}
i've got a quick question that's giving me a little bit of a problem. when i cycle through my database records, everything works fine until i get to BOF or EOF...when moving in the previous direction, and i get to BOF, it doesn't stop there, it bounces back and forth between the first and second record each time i click previous. same happens at EOF when moving to next records. instead of stopping on the last record, it's going back to the 2nd to last record, then to the last etc.
here's the Next and Previous buttons:
Private Sub cmdPrevious_Click()
On Error GoTo ErrorLine2
If rsMerchants.BOF Then
End If
    If Err.Number = -2147217900 Then
       response = MsgBox("You need to save the current record before continuing", vbOKCancel)
      ElseIf Err.Number Then
      MsgBox ("You have encountered Error Number:" & Err.Number & vbCr & "Error Description: " & Err.Description & vbCr & "Please report this information to the System Administrator")
    End If
End Sub

Private Sub cmdNext_Click()
On Error GoTo ErrorLine2
If rsMerchants.EOF Then
End If
    If Err.Number = -2147217900 Then
       response = MsgBox("You need to save the current record before continuing", vbOKCancel)
      ElseIf Err.Number Then
      MsgBox ("You have encountered Error Number:" & Err.Number & vbCr & "Error Description: " & Err.Description & vbCr & "Please report this information to the System Administrator")
    End If
   End Sub

anyone have an idea where i'm going wrong?


Edited by - mrsmiley0221 on 11/18/2003 11:36:06 AM

MoveNext/MovePrevious For Values In An Array.
I have a two dimensional array which stores values entered on a Data Entry Form. I want to display a preview of what was entered for 'n' number of vehicles, before saving it to database.

How can I connect ADO control with this array so that I can perform MoveNext and MovePrevious.

Quick Question About .MoveNext And .MovePrevious
hello again! i've got a little bit of a problem with my next and previous's the code:
Code:Private Sub cmdNext_Click()
End Sub
Private Sub cmdPrevious_Click()
End Sub
what i'm trying to do is have the records update when the user clicks on anything that changes the current record. not really sure how i need to go about that, but when it's set up as above, it does update the record and it does move to the next or previous, but it won't move every time i click the button...just the first time. any suggestions?
the update record sub is:
Private Sub UpdateRecord()
Dim strSQL As String

strSQL = "Update tblcustomerdb set [First Name] = '" & txtFirstName.Text & "',[Last Name] = '" & txtLastName.Text & "'... WHERE ID = " & txtID.Text & ";"
connDataview.Execute strSQL
Set rsDataview.ActiveConnection = connDataview
End Sub


Results Stored Procedure &amp; Movenext, Moveprevious
Hello There,

Hope somebody can help me with some problems... Thanks thanks thanks..

I have got a mastertable with CustomerID. This is the primary key.
How can i connect a table to a mastertable with a stored procedure by using the primary key of the mastertable and a foreign key of the table? I have used a stored procedure, but then I cannot Update columns at the Bankrelations table (Run-Time error ‘-2147467259(80004005)’ Insufficient key column information for updating or refreshing).
The stores procedure is connected with an Ado Data Control to a Datagrid.
(txt1.Text is connected to the Master table with an Ado Data Control and displays the Primary Key “CustomerID”)

Question 1. What can I do?
Question 2: How can I move to the next recordset for both Ado Data Control? (The mastertable Customers moves, but the results of the stored procedures CustomerAndBankrelations don't.)

My stored procedure look like as follow:
CREATE PROCEDURE CustomerAndBankrelations
@CustomersID INT
SELECT Bankrelations.Bank, Bankrelations.Banknumber
FROM Customers , Bankrelations
WHERE Bankrelations.CustomerID = @CustomersID

Code in Visual Basic:
Public Sub Form_load( )
Adodc1.ConnectionString = cnnCN
Adodc1.CommandType = adCmdStoredProc
Adodc1.RecordSource = "CustomerAndBankrelations;1(" & txt1.Text & ")"
End Sub

Public Sub cmdAddBankrelations_Click()
Dim CustomerID As String
CustomerID = txt1.Text
With Adodc1.Recordset
.Fields!CustomersID = StrConv(CustomersID, vbProperCase)
.Fields!Bank = StrConv(txt2, vbProperCase)
.Fields!Banknumber = StrConv(txt3, vbProperCase)
End With
txt2 = " "
txt3 = " "
End Sub

My movenext code look like:
Public sub movenext_click()
If Adodc1.Recordset.EOF Then
End If
End Sub

Thanks thanks in advance,


Problem With Move Previous, Movenext Is Ok
Hi everyone

I am facing prob with moving records to previous. movenext is ok. I have written code for both move next and move prev.

when i try to moveprev it displays only first records. then it doesnt work.

Please help me out

Waiting for help

Thanx in advance

Private Sub Form_Load()
rst.Open "select * from customers", bayan
rst1.Open "select * from customers", bayan
End Sub

Private Sub movenext_Click()
On Error GoTo MoveErr
   Exit Sub
   MsgBox "You are at the end of the recordset."
End Sub

Private Sub moveprev_Click()
On Error GoTo MoveErr
exit sub
   MsgBox "You are at the start of the recordset."
end Sub

Sub displayrecords()
On Error GoTo err_move

    txtcustno = rst!Cust_No
    txtcustnameL = "" & rst!cust_name_latin
    txtcustname = "" & rst!cust_name
    DTStart = "" & rst!Date
    txtaddress = "" & rst!Address
    txtpob = "" & rst!Po_Box
    txttel = "" & rst!Tel
    xtfax = "" & rst!fax
    txtzip = "" & rst!zip_code
    txttel = "" & rst!Tel
    txtpager = "" & rst!pager
    txtmobile = "" & rst!mobile
    txtemail = "" & rst!email
    txtcontact = "" & rst!contact_name
    txtauthorize = "" & rst!authorize
    DTexpire = "" & rst!expire_date
    txtremarks = "" & rst!Remarks
    If DTexpire <= Date Then
        Shape1.FillColor = &HFF&
    ElseIf DTexpire - 15 < Date Then
        Shape1.FillColor = &H80FFFF
        Shape1.FillColor = &H80FF80
    End If
    UMode = True
    Saved = False
    Exit Sub

End Sub
Sub move_prev()
On Error GoTo err_move
If Not rst1.BOF Then
    txtcustno = rst1!Cust_No
    txtcustnameL = "" & rst1!cust_name_latin
    txtcustname = "" & rst1!cust_name
    DTStart = "" & rst1!Date
    txtaddress = "" & rst1!Address
    txtpob = "" & rst1!Po_Box
    txttel = "" & rst1!Tel
    xtfax = "" & rst1!fax
    txtzip = "" & rst1!zip_code
    txttel = "" & rst1!Tel
    txtpager = "" & rst1!pager
    txtmobile = "" & rst1!mobile
    txtemail = "" & rst1!email
    txtcontact = "" & rst1!contact_name
    txtauthorize = "" & rst1!authorize
    DTexpire = "" & rst1!expire_date
    txtremarks = "" & rst1!Remarks
    If DTexpire <= Date Then
        Shape1.FillColor = &HFF&
    ElseIf DTexpire - 15 < Date Then
        Shape1.FillColor = &H80FFFF
        Shape1.FillColor = &H80FF80
    End If
    UMode = True
    Saved = False
End If
    Exit Sub

End Sub

How Can I Select The 12 Previous Records Starting From EOF Or MoveLast? [SOLVED]
For charting purposes I want to select the last 12 records in a field (12 previous from MoveLast, meaning the most recent)

Code:Private Sub Command2_Click()
    v = "sales"
    SkinLabel1.Caption = "Sales"
    rs.Open ("SELECT Max(" & v & ") as [H] FROM AggregateData "), cn, adOpenKeyset, adLockPessimistic
    h = IIf(IsNull(rs!h), 0, h)
    h = rs!h
    s = 300 / h
    Set rs = New ADODB.Recordset
    rs.Open "AggregateData", cn, adOpenKeyset, adLockPessimistic '<<<<<<<This is where I want to select the last 12 records so the chart only shows the 12 and not all the records in the field
    methodCharts (v)
End Sub

How can I state the instruction in code to select 12 records from MoveLast or from EOF? Do I use EOF or MoveLast?



Edited by - vbprog1144 on 11/11/2004 4:36:11 PM

MoveNext Records Do Not Show!?
Hi group,

I use this simple code to move to the next record:


With RS
end with

Why do I need the next extra lines:

me.txtID = rs.Fields("ID")



.movenext Skips Records - Quite Confused
I've never seen this problem before. I have a recordset and I'm using .movenext to iterate through it. It skips the same record every time. Not at the beginning or end, the skipped record is in the middle. Heres the block of code:

sql = "SELECT Standings.ID, Standings.HRTotal From Standings ORDER BY Standings.HRTotal DESC;"
Set rs = New ADODB.Recordset
rs.Open sql, glb_ConnString, adOpenStatic
i = numberOfTeams
Do While Not rs.EOF And Not rs.BOF
Set standObj = standCollection.Item(Str(rs!ID))
standObj.HRPoints = i
i = i - 1
this is what the sql returns:

thanks for any help. I've been banging my head on this all day.

Insert Records At Middle And Movenext
Front end : Visual BAsic 6.0
BAck end: Access(Visual Data manager provided with VB)

I have problems on VB.

First Question.

I have a table with field serial number(slno double).

I have data like this:
Now I want to insert a record between 3 and 4, so that the new record should have the number 4 and the previous 4 should become 5.

How to implement this? Same case with deleting a record also.When I delete a record 2, the recordnext to that(i.e. 3) should become 2.

Second question.

I have written code like this for navigation.
private sub cmdnextemp_click
if rs.eof then
end if
end sub

This code works fine if I open Recoredset without giving any condition like this

set rs=db.openrecordset("january")

But if I specify any condition

set rs=db.openrecordset("select * from january where slno=(<condition>)")

the code doesnot work.It will reach to EOF even the recordcount is 1.

I want to open recordset with this condition only.

Resolved:move Pointer To Next Subsequent Line In Text File Same As Recordset.movenext
Hi All, Pls Pls kindly help me.

How to move pointer to next line in reading text file by while loop similar to rs.movenext (dim rs as adodb.recordset)

Dim F As Long
Dim SLine As String
Open filepath & ".SNWSN1.TXT" For Input As F

>While Not EOF(F)
>Line Input #F, SLine
' want to insert some condition in which
'if criteria matched ==> jump to next line and then read.
'no return needed

Close #F

Thanks a lot.

Abt Move Records
Hello All

I got stuck at one place

here goes decription

I HAVE one combo from which
on click we can see records on text boxes
and in also have move next,last prev butoons

i used requery.for this

problem is when i click on combo it shows me records 1,2,3,4,....
bt after that if i click after this on move next,prev

it shud show next or prev of selected records frm combo right?
bt if click on next or prev
it startes frm first record

can u tell pls how to connect this combo and move next,prev buttons together?

thanks in advance

SQL Statement To Move Records
I need to move certain records to a different table.
I am thinking to use select statement to save the list of records into
VB array and then loop through array to update it to the other table.
Is there one SQL statement that can move certain records into another

Thanks for your help!

Move Records Within A Table
Hello everyone,
I am kind of new to VB and have a quick task to accomplish.

Say, I have the following table

region FName SecondName Age FName_1 SecondName_2 Age_2
001 Alan Smith 25 Rose McCartney 24

I would like to move the records in the FName_1, SecondName_2, Age_2 below the first ones, so that the new table would look like this

region Fname SecondName Age
001 Alan Smith 25
001 Rose McCartney 24

I would also like to create a variable increment, since I have a lot of tables I would like to convert to the format I need.

Any help will be highly appreciated.

Use VB To Move Records Between Access Databases

I need a utility program to copy records from multiple MS Access databases to a master Access database. The tables are all named "customer" and are identical in structure. If you could send an example, I can certainly modify the code.

I need to know how to connect, and use SQL to SELECT from one database and INSERT in a second database.

Thank you,


Event After Move Trough The Records
hi there!

My problem is simple:
I need to update a picture everytime a move trough the records of a database.
The picture path is linked to a variable.
So I was wondering wich event should I use to update this picture.
Something like
private sub form_JustAfterChangetheRecord
pic.result = loadPicture(txtfield)


Move Records Without Clicking On Anything In The Form?
I have a form which holds 12 records, and I have a function to automate the data from the form with word, which it does with the first record. I'm pulling the data out from the textboxes of the form, not fields from a recordset (because I got confused with the latter). Is it possible to get the data from the second record to show up in the form so I can run the subprocedure using the data from this second record? I eventually want to be able to do all 12, but one step at a time...

The automation function is all done by clicking on one button, and I'd to be able to get the second record data and send to word without the user having to do anything else. Can this be done?

Cant Move Through Records When Leaving Form
Hi everybody

I have a problem hire and cant find the solution becouse there is no error message, the thing is i hava a MDI app using ms-access, ADO, and vb, it seems to work ok, (i can move through recordsets, etc) but when i leave the form and return to it i cant move through records anymore it gets like frozen, can anyone help me with this? I cant seem to find the problem
Thank you all

Edited by - 2lost4u on 4/22/2005 10:54:10 AM

Move Records From One Database To Another For Archiving
how to move records from one access db to another ?
Thanks in advance,

How Do I Move Between Records In A Child Recordset
I am using data environment in VB6 to implement a simple parent child relationship. I have a customers table(parent) and a orders(child) table and they are defined as parent child nodes in data environment. My question is how do I move between the child records(orders).

I Can't Move Cursor Through Records (Cells) In DataGrid?
Dear Friends,

I have one DataGrid on my form which retrieves data from MS Access database and shows in the Grids. Everything is ok except I can't select any cel in the DataGrid.

Whenever I try to select a single cel from other rows, it just selects the first row cel. But I can select the whole row.

What to do to select a single cel from any row in the DataGrid?

Thanks in advance.

my code is like this

ssql = "select * from members where nomember = 123 order by no index" ssql, de.conn, adopenstatic, adlockreadonly
number = rs("nomember").value
set rs = nothing

notes : in my database a member can be added for several times for a month depend on his transaction in the month. But if i use the command rs.movelast the recordset will move in the first row of that member. Can anybody tell me how to move to the last record fro that member ? Thx A lot

Why Can't I Use MoveLast In ADO?
I am trying to get the last record in a database to get the autonumber key field.

When I do this:

Set rsInvoice = New ADODB.Recordset
rsInvoice.Open "SELECT * FROM Invoice", CN
If (Not rsInvoice.EOF) Then
Text1.Text = rsInvoice.Fields("Invoice").Value + 1
End If

it tells me "rowset does not support feching backword"

I do not want to have to loop threw all the records to get this number. What do I need to do?

Can't Use .MoveLast
I have the following situation :
  data_01.RecordSource ="SELECT name,..... FROM <table1> ORDER BY name"

  data_01.Database.Execute("INSERT INTO <table1>(name,...) VALUES (....)")

How can I move data_01 (and the controls bound) to the last record added,
 using "ORDER BY name" moves record added from last position, so I can't use .MoveLast
 Is any solution without using a new filed?

Thanks !!!

Why Can't I Do An Rs.movelast ?
I did this call:

DB.ExecuteStoredProc(my_sp, adUseServer, adOpenDynamic, adLockOptimistic, False, , RS)


I need to get a record count so I tried rs.movelast and then rs.movefirst.
It comes back with 'Rowset position cannot be restarted'. I don't understand why because I specify adOpenDynamic and I also tried adOpenKeySet with the same result.

Anyone got an idea ?

Why Can't I Do An Rs.movelast ?
I call a stored procedure and return a recordset. I specify adOpenDynamic. I still though get an errormsg, 'Rowset does not support fetching backwards.

DB.ExecuteStoredProc "dbo." & aSPname(List1.ListIndex), P.toArray, , adOpenDynamic, , False, , RS

RS.MoveLast ---> gives error, why ?

Any ideas ?


Listview Movelast
I'm working on the listview with columes. My problem is when form is loaded , my long listview will by automatically show from the first item only . It takes time for me to scroll down every time i want to see my inserted new data. Maybe my code is wrong. Please help

Private Sub Display_Listview()
Dim rs As New ADODB.Recordset
Dim rec As Integer
Dim str
Dim mItem As ListItem
str = "select * from chip order by stt asc, so asc, trang asc"
rs.Open str, cn, adOpenKeyset, adLockOptimistic, adCmdText
If rs.EOF = False Then
While Not rs.EOF
Set mItem = lvItem.ListItems.Add(, , rs!stt)
mItem.SubItems(1) = rs!so
mItem.SubItems(2) = rs!trang
mItem.SubItems(3) = rs!tacgia
mItem.SubItems(4) = rs!tuade
End If
End Sub

Incorrect MoveLast
I have some mapping software that I have been working on that uses MapPoint and stores some basic information for the files that are placed on the map in an access database. When I add the new studies I use a .movelast, increment the "Study Number"(primary key) by 1 and then add the values that I need too. This works fine most of the time but for some reason that I can't explain it doesn't always go to the very alst record. It can range from being one away from the last or a bunch.

One example is that I had 42 studies so 42 would've been the last number in that field but it is reading in 41 as the value after the .movelast.

MoveFirst And Then MoveLast?
Hi to all,

This question came from a code that I have seen. I wondered why is it that after calling the MoveFirst method on the ADO Recordset object, the MoveLast method is then called after?

Why do you think the author did that?

Is this a technique to quickly populate the recordset with records? I think that is the closest that I can get.

Thanks and god bless.

Movelast Not Correct?

I have a problem whereby it looks like .movelast is not actually doing that.

I've commented in the code below where the problem is occuring.

Any help would be richly appreciated! Thanks GJ

Here's my code:

Private Sub UpdateCalcsToRun(tmpCde As String)
'cycles through tmpCalcsToRun2 for tmpCde and updates CalcstoRun with every non
'zero period greater than one day.
Dim rstTmpCalcsToRun2 As ADODB.Recordset
Dim rstCalcsToRun As ADODB.Recordset
Dim strSQL As String
Dim blnOpenInterval As Boolean 'this variable changes depending whether a new period is open or closed
Set rstTmpCalcsToRun2 = New ADODB.Recordset
Set rstCalcsToRun = New ADODB.Recordset
blnOpenInterval = False 'initially set to false
strSQL = "Select * from tmpCalcsToRun2 ORDER BY ID"
rstTmpCalcsToRun2.Open strSQL, conn, adOpenKeyset, adLockOptimistic
rstTmpCalcsToRun2.Find "CumShares > 0", 0, adSearchForward
If rstTmpCalcsToRun2.EOF Then
Exit Sub 'couldn't find any cumshare>0, ie only positive cde because of a 1 day on-off, so go to next cde
strSQL = "INSERT INTO CalcsToRun (Cde,StartDate) " & _
"VALUES('" & tmpCde & "',#" & Format(rstTmpCalcsToRun2.Fields("Date"), "dd/mm/yyyy") & "#)"
conn.Execute strSQL 'found first cumshare>0, so update CalcsToRun...
blnOpenInterval = True '...any set open interval to true
End If
strSQL = "SELECT * FROM CalcsToRun"
rstCalcsToRun.Open strSQL, conn, adOpenKeyset, adLockOptimistic
Do Until rstTmpCalcsToRun2.EOF
If blnOpenInterval And rstTmpCalcsToRun2.Fields("CumShares") = 0 Then
rstCalcsToRun.Fields("EndDate") = rstTmpCalcsToRun2.Fields("Date")
blnOpenInterval = False
ElseIf Not blnOpenInterval And rstTmpCalcsToRun2.Fields("CumShares") > 0 Then
'found a new positive share date to open an interval
strSQL = "INSERT INTO CalcsToRun (Cde,StartDate) " & _
"VALUES('" & tmpCde & "',#" & Format(rstTmpCalcsToRun2.Fields("Date"), "dd/mm/yyyy") & "#)"
conn.Execute strSQL
blnOpenInterval = True
End If
'now check for unclosed interval at end...
If rstCalcsToRun.Fields("StartDate") > 0 And IsNull(rstCalcsToRun.Fields("EndDate")) Then
'rstCalcsToRun.Fields("EndDate") = Format(txtEndDte.Text, "dd/mm/yyyy")
conn.Execute "UPDATE CalcsToRun SET CalcsToRun.EndDate = #" & Format(txtEndDte.Text, "dd/mm/yyyy") & "#" & _
"WHERE (((CalcsToRun.EndDate) Is Null))"
End If
End Sub

MoveLast Does Not Go To Last Record!
Ah the newbie questions
I am using ADODC to allow a new record to be added to my database. Then I refresh the database and *want* to move to the very last record....the record that was just entered.
so i used


which I presumed would take me to the last record.
However, it takes me to a record in the middle of my database.......
Can anybody tell me why?

Thank you kindly

Having Problems With DAO Rs.movelast

im having problem with my code:


VB Code:
Function NextJN() Dim NewNum As BooleanDim searchtr, lastnum, NextNum As StringDim X As Integer Set rsLogbook = dbSSS.OpenRecordset("Logbook")rsLogbook.MoveLast NewNum = True lastnum = rsLogbook.Fields("JNumber"): lblJNumShow.Caption = rsLogbook.Fields("JNumber")lblJNum.Caption = Str(Val(lastnum) + 1)lblLastNum.Caption = lastnum NextNum = Trim(lblJNum.Caption) If Len(Trim(lblJNum.Caption)) < 5 Then        For X = 1 To (4 - Len(Trim(lblJNum.Caption)))           NextNum = "0" & NextNum         Next XNextNum = "SSS/" + Trim(NextNum) + "/" + Format(Date, "YY")End If strJobNumber = NextNum Exit FunctionEnd Function

- this code works for some time. then after saving a couple of records
the "movelast" function does not go to the Last item on my table.
- sometimes it works/ sometimes not

so i can't save the record because it will duplicate the primary key.

my last number in the table is 10 but the movelast function shows the last num as 9...

can u help me guys? thanks so much!



I have 2 commands button:Previous and Next
Private Sub cmdprevious_Click()
cmdnext.Enabled = True
If Rs!PID = nl Then
cmdprevious.Enabled = False
End If
End Sub

nl is the PID of the first record in the recordset

I want that when i click on previous and execute Rs.MovePrevious to test if it's the beginning of the recordset,if yes disable the command previous

How can I do it other than my way?


i use in a arecorset to move forward and backwort in
in a parameter quary
only when i try to move into a quray i can't move back
rs.MovePrevious dosen't work
when i try to move forward
rs.movenext work 100%
i saw in the msdn that " recordset object must support bookmark or backward cursor movment
if somone know how i need to define my cursor to move in the quray back

Can&#039;t MovePrevious
My recordset in VB6 is declared with a dynamic cursor. I can moveforard and to the begining, but it won't allow me to moveprevious.I'm not already at the BOF. What am I doing wrong?

This is an easy one, I am just stumped(brain dead) at the moment.


Dim cn as new adodb.connection
Dim cmd as new adodb.command
dim rs as new adodb.recordset

Set "The connection string goes here"
cmd.activeconnection = cn
cmd.commandtype = spStoredProc
cmd.commandtext = "spRetrieveData"
cmd.Paramaters(1) = iId
set rs = cmd.execute

I am not able to use 'rs.moveprevious' method.

Error received "Operation not allowed in this context"

I can move forward but not backwards.  Any ideas?

MovePrevious In ADO
Hi gys,

this is my code;

When my form loads, IT will view all status with A,D,R,W,H in FSTATUS field. This work fine but the problem is when my click the button NEXT i got error. Hope you can help me guys.

Code:Private Sub Form_Load()
Dim path As String
 path = App.path & "LoginCheck.mdb"
 cnn.CursorLocation = adUseClient
 cnn.Open "provider = microsoft.jet.oledb.4.0;persist security info=false;data source = " & path
Call QueryEnd Sub

Code:Private Sub Query()
rs.Open "SELECT * from MELOAD order by FSTATUS desc", cnn
 txtMemnr.Text = rs("FMEMNR")
 txtFname.Text = rs("FNAME")
 txtMobile.Text = rs("FMOBILE")
 txtAmount.Text = rs("FAMOUNT")
 txtStat.Text = rs("FSTATUS")
 Call Load

End Sub

This is the code i got error! When i click this to go the previous records it gives me error.
Code:Private Sub cmdPrev_Click()
'On Error Resume Next
With rs
        If rs.EOF Then
            txtMemnr.Text = rs("FMEMNR")
        End If
End With
End Sub

Thnx in advance


Edited by - Jam_10 on 2/23/2005 7:14:41 PM

Is Recordset.movelast Valid?
i want to point to the last record for a particular recordset, but it RS.moveLast valid? thx

Movefirst Works But Movelast Not
Hi folks,

a small question, why is it that MSDE & ADO support Movefirst but they don't support Movelast?

Copyright © 2005-08, All rights reserved