Results 1 to 10 of 10

Thread: movenext

  1. #1

    Thread Starter
    Addicted Member smh's Avatar
    Join Date
    Oct 2000
    Location
    South Dakota, USA
    Posts
    249
    How do I (or can I for that matter) use the movenext statement for coding a command button to move to the next record when I am not using a data control? My code is as follows:


    Private Sub Form_Load()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    'Open an ADO Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Issues_files\issues.mdb;"

    'Open the ADO Recordset
    rst.Open "Select * from tblLog WHERE location = '" & gstrLocation & " ' ", cnn

    If rst.BOF = True Or rst.EOF = True Then
    temp = MsgBox("No records were found.", vbOKOnly, "Error")
    End If
    txtDateOccured = rst("dateoccured")
    txtTimeOccured = rst("timeoccured")
    txtDateReported = rst("datereported")
    txtReportedBy = rst("reportedby")
    txtLocation = rst("location")
    txtCubeNumber = rst("cubenumber")
    txtEnteredBy = rst("enteredby")
    txtIssueNumber = rst("issuenumber")
    txtCategory = rst("category")
    txtPriority = rst("priority")
    txtDownTime = rst("downtime")
    txtIssue = rst("issue")
    txtStatus = rst("Status")


    End Sub


    Private Sub cmdNext_Click()

    Dim temp As String

    'Move to the next record and show when last record is reached

    With rst
    .MoveNext
    If .EOF Then
    .MoveLast
    temp = MsgBox("This is the last record.", vbOKOnly, "Last Record")
    End If
    End With

    End Sub

    I am not getting an error message at all, but my "Last Record" message box is not appearing either. What am I missing?

    smh
    Normal is boring...

    smh

  2. #2
    Frenzied Member monte96's Avatar
    Join Date
    Sep 2000
    Location
    Somewhere in AZ
    Posts
    1,379
    You probably don't have Option Explicit set (Which you should! Always! Always! Always!)

    And... your recordset appears to be out of scope.. You create it and set it's reference in a private procedure which makes it local only to that procedure. If you need it throughout the form, you will need to move it's declaration out to the General section of the Form's code module so it has form/module scope. Since you don't have Option Explicit set, it won't error because the object doesn't exist..


    Did I mention you should always set Option Explicit?
    oOOo--oOOo
    __/\/\onte96
    oOOo--oOOo
    Senior Programmer/Analyst
    MCP
    monte96_@hotmail.com
    monte96@vbgarage.com


    Your results may vary.. some restrictions may apply.. pricing and participation may vary.. not available in all states.. professional driver closed course..quantities limited..

  3. #3
    Fanatic Member
    Join Date
    Oct 2000
    Location
    London
    Posts
    1,008
    Hi smh,

    I think you should check your spelling <grin>

    When you do what Monte says (good boy), you will find your text boxes do not update if you move records, you have merely set their text values to the first value in the dataset - you need to update your values - I'd suggest a Sub().

    Paul.

    P.S. Ensure your dataset can move back and forth otherwise, when you attempt rst.MoveLast from the EOF the code will error horribly (MSMoan: ADO can't move backwards whinge whinge whinge etc.)

    P.
    Not nearly so tired now...

    Haven't been around much so be gentle...

  4. #4
    Hyperactive Member
    Join Date
    Jun 1999
    Posts
    308
    Hi, smh.

    First of all monte96 is right, you should declare database and recordset in Option Explicit.
    Second - if rst.EOF is true you cannot use .MoveLast ,only .MovePrevious. EOF is not the last record, but the pointer after the last record.

  5. #5

    Thread Starter
    Addicted Member smh's Avatar
    Join Date
    Oct 2000
    Location
    South Dakota, USA
    Posts
    249

    Changes

    OK, I've changed my code, but I am still not getting what I want. I have inserted 'rst.recordcount' right after opening the recordset for testing this, and I get -1. This is a problem....

    smh

    My code follows
    ........................................................
    Option Explicit

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset



    Private Sub cmdNext_Click()

    Dim temp As String

    'Move to the next record and show when last record is reached

    With rst
    .MoveNext
    If .EOF Then
    temp = MsgBox("This is the last record.", vbOKOnly, "Last Record")
    End If
    End With

    End Sub
    _________________________________________
    Private Sub cmdPrevious_Click()

    Dim temp As String

    With rst
    .MovePrevious
    If .BOF Then
    temp = MsgBox("This is the first record.", vbOKOnly, "First Record")
    End If
    End With
    End Sub
    ___________________________________________

    Private Sub Form_Load()

    Dim temp As String

    'Open an ADO Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Issues_files\issues.mdb;"

    'Open the ADO Recordset
    rst.Open "Select * from tblLog WHERE location = '" & gstrLocation & " ' ", cnn

    If rst.BOF = True Or rst.EOF = True Then
    temp = MsgBox("No records were found matching your criteria.", vbOKOnly, "Error")
    Else

    txtDateOccured = rst("dateoccured")
    txtTimeOccured = rst("timeoccured")
    txtDateReported = rst("datereported")
    txtReportedBy = rst("reportedby")
    txtLocation = rst("location")
    txtCubeNumber = rst("cubenumber")
    txtEnteredBy = rst("enteredby")
    txtIssueNumber = rst("issuenumber")
    txtCategory = rst("category")
    txtPriority = rst("priority")
    txtDownTime = rst("downtime")
    txtissue = rst("issue")
    txtStatus = rst("Status")
    txtissue = rst("issue")
    End If

    End Sub






    Normal is boring...

    smh

  6. #6
    Hyperactive Member
    Join Date
    Jun 1999
    Posts
    308
    Hi.

    You don't understand meaning of EOF and BOF.
    BOF Indicates that the current record position is before the first record in a Recordset object.


    EOF Indicates that the current record position is after the last record in a Recordset object.

    It means if EOF is True, you have to move record to Previous. If BOF is True, you have to move record to Next.
    Check Help, you can find an example.

  7. #7
    Fanatic Member
    Join Date
    Oct 2000
    Location
    London
    Posts
    1,008
    Incidentally

    If rst.BOF = True Or rst.EOF = True Then
    temp = MsgBox("No records were found matching your criteria.", vbOKOnly, "Error")
    This should be And, if rst.BOF and rst.EOF then the beginning of the file is the end of the file => no records.


    BTW. LG, if the record pointer is at rst.EOF you can certainly use rst.MoveLast in DAO, is that not true of ADO?

    Cheers,

    Paul



    Not nearly so tired now...

    Haven't been around much so be gentle...

  8. #8
    Frenzied Member monte96's Avatar
    Join Date
    Sep 2000
    Location
    Somewhere in AZ
    Posts
    1,379
    You can use MoveLast at any time as long as the cursortype supports it and there is at least one record.

    You are not specifying the cursortype which means you are getting the default type which is adOpenForwardOnly. Any attempt to use MovePrevious will error- You want to do this:

    Code:
    rst.Open "Select * from tblLog WHERE location = '" & gstrLocation & " ' ", cnn, adOpenStatic
    Also, if you plan on allowing updates to this recordset, the default locktype is readonly and since you aren't specifying any, that's what you have.

    in your movenext and moveprevious code:

    Code:
    Private Sub cmdNext_Click() 
        Dim temp As String 
    'Move to the next record and show when last record is reached 
    
        With rst 
            .MoveNext 
            If .EOF Then 
                MsgBox "This is the last record.", vbOKOnly, "Last Record"
                .MoveLast
            End If
    
            LoadFormData
        End With 
    End Sub 
    _________________________________________ 
    Private Sub cmdPrevious_Click() 
        Dim temp As String 
    
        With rst 
            .MovePrevious 
            If .BOF Then 
                MsgBox "This is the first record.", vbOKOnly, "First Record"
                .MoveFirst
            End If
    
            LoadFormData
        End With 
    End Sub
    and add this:

    Code:
    Private Sub LoadFormData()
    'Assuming these are strings- need to handle possible NULLS:
        txtDateOccured = rst("dateoccured") & ""
        txtTimeOccured = rst("timeoccured") & ""
        txtDateReported = rst("datereported") & ""
        txtReportedBy = rst("reportedby") & ""
        txtLocation = rst("location") & ""
        txtCubeNumber = rst("cubenumber") & ""
        txtEnteredBy = rst("enteredby") & ""
        txtIssueNumber = rst("issuenumber") & ""
        txtCategory = rst("category") & ""
        txtPriority = rst("priority") & ""
        txtDownTime = rst("downtime") & ""
        txtissue = rst("issue") & ""
        txtStatus = rst("Status") & ""
        txtissue = rst("issue") & ""
    End Sub
    and for your form_load:
    Code:
        If rst.BOF = True And rst.EOF = True Then 
            MsgBox "No records were found.", vbOKOnly, "Error"
            Exit Sub
        End If 
        LoadFormData
    oOOo--oOOo
    __/\/\onte96
    oOOo--oOOo
    Senior Programmer/Analyst
    MCP
    monte96_@hotmail.com
    monte96@vbgarage.com


    Your results may vary.. some restrictions may apply.. pricing and participation may vary.. not available in all states.. professional driver closed course..quantities limited..

  9. #9
    Fanatic Member
    Join Date
    Oct 2000
    Location
    London
    Posts
    1,008
    Excellent reply, Monte - I am too lazy to put in all that code. Maybe a basic tutorial on recordset creation and navigation could be organised because there seems to be a lot of confusion over these issues.

    What do you think?

    In fact I might start a thread...

    Cheers,

    Paul.
    Not nearly so tired now...

    Haven't been around much so be gentle...

  10. #10
    Frenzied Member monte96's Avatar
    Join Date
    Sep 2000
    Location
    Somewhere in AZ
    Posts
    1,379
    Sounds like a good idea to me.. I don't know how many threads I've answered about non-updatable recordsets for instance..
    oOOo--oOOo
    __/\/\onte96
    oOOo--oOOo
    Senior Programmer/Analyst
    MCP
    monte96_@hotmail.com
    monte96@vbgarage.com


    Your results may vary.. some restrictions may apply.. pricing and participation may vary.. not available in all states.. professional driver closed course..quantities limited..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width