Unbound form, how to get to next record ?

[mbilalk]

Hello,

Well here goes, I am using Microsoft Access to design a user interface for SQL Server database, I am currntly using the project method. I have manged to link it up and get my form to show the record, but when I go to click on next record it is not showing the next record. Where you have the next buttons it shows how many records there are, in this instance there is 706, but always seems to get stuck on the first record, this is the code I have so far, have been searching the net and can not seem to find anything.

Here is my code:-

Private Sub Form_Load() 
   Dim cn As ADODB.Connection 
    Dim rs As ADODB.Recordset 
          
    Set cn = CurrentProject.AccessConnection 
    Set rs = New ADODB.Recordset 
        With rs 
            Set .ActiveConnection = cn 
            .Source = "SELECT * FROM prod_parent" 
            .LockType = adLockOptimistic 
            .CursorType = adOpenKeyset 
            .Open 
        End With 
     
   Set Me.Recordset = rs 
   
   Me.prodcode = rs!prodcode 
   Me.prodtitl = rs!prodtitl 
   Me.price = rs!price 
   Me.description = rs!description 
   rs.Close 
   Set rs = Nothing 
   Set cn = Nothing 
End Sub

Thanks

[ADezii]

Hello,

Well here goes, I am using Microsoft Access to design a user interface for SQL Server database, I am currntly using the project method. I have manged to link it up and get my form to show the record, but when I go to click on next record it is not showing the next record. Where you have the next buttons it shows how many records there are, in this instance there is 706, but always seems to get stuck on the first record, this is the code I have so far, have been searching the net and can not seem to find anything.

Here is my code:-


Private Sub Form_Load()
Dim cn As ADODB.Connectio n
Dim rs As ADODB.Recordset

Set cn = CurrentProject. AccessConnectio n
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnecti on = cn
.Source = "SELECT * FROM prod_parent"
.LockType = adLockOptimisti c
.CursorType = adOpenKeyset
.Open
End With

Set Me.Recordset = rs

Me.prodcode = rs!prodcode
Me.prodtitl = rs!prodtitl
Me.price = rs!price
Me.description = rs!description
rs.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Thanks

The simplest possible explanation, not accounting for EOF and other factors:

1. Delare the Connection and Recordset Variables in the Form's Code Module:
   

   Private cn As ADODB.Connection
   Private rs As ADODB.Recordset

2. Create the Recordset and populate the Form with the 1st Record in the Load() Event of the Form:
   

   Private Sub Form_Load()
   Set cn = CurrentProject.Connection
   Set rs = New ADODB.Recordset
   
   With rs
     Set .ActiveConnection = cn
    .Source = "SELECT * FROM prod_parent"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
      .Open
   End With
   
   Set Me.Recordset = rs
   
   Me.prodcode = rs!prodcode
   Me.prodtitl = rs!prodtitl
   Me.price = rs!price
   Me.Description = rs!Description
   End Sub

3. To Move to the Next Record in the Recordset and display the Record on the Form:
   

   Private Sub cmdNextRecord_Click()
   Me.Recordset.MoveNext
   
   Me.prodcode = rs!prodcode
   Me.prodtitl = rs!prodtitl
   Me.price = rs!price
   Me.Description = rs!Description
   End Sub

4. To Close the Recordset and Connection and regain memory:
   

   Private Sub Form_Close()
   rs.Close
   cn.Close
   Set rs = Nothing
   End Sub

[mbilalk]

Hi ADezii,
Thanks a lot for ur reply. I was looking for the exact soltion which you provided. Now i am on motor way and enjoying programming.

once again Thanks a lot.

[ADezii]

Hi ADezii,
Thanks a lot for ur reply. I was looking for the exact soltion which you provided. Now i am on motor way and enjoying programming.

once again Thanks a lot.

You are quite welcome.