Solved: Access navigation from within excel

[gibbo1715]

All

I am linking to an access database from an excel userform and wondering about navigation buttons

I was going down the lines as follows

[VBA]
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim TableName As String
Dim TargetRange As Range
i = 11
TableName = "tbl_Test"
Set TargetRange = Range("A1").Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
stDB & ";"
Set rs = New ADODB.Recordset
With rs
.Open "SELECT * FROM " & TableName & _
" WHERE [ID] = " & i, cn, , , adCmdText
End With

Me.TextBox1.Value = rs.Fields("FieldName1").Value
Me.TextBox2.Value = rs.Fields("FieldName2").Value
Me.TextBox3.Value = rs.Fields("FieldName3").Value
Me.TextBox4.Value = rs.Fields("FieldName4").Value
Me.TextBox5.Value = rs.Fields("FieldName5").Value

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing [/VBA]

but this idea clearly wont work because ID is my primary key and when i delete records my numbering will also change

can anyone tell me a way to navigate around an access database table from a userform within excel or point me in the right direction?

thanks

Gibbo

[gibbo1715]

All

This works for navigation although i havnt sorted my counting label properly yet

Cheers

Gibbo

[VBA]Private Sub CommandButton2_Click()
rs.MovePrevious
intRecNumber = intRecNumber - 1

If Not rs.BOF Then
Call Navigation

Me.Label1.Caption = CStr(intRecNumber) & " of " & rs.RecordCount
Else
CommandButton5_Click
End If
End Sub
Private Sub CommandButton3_Click()
rs.MoveNext
intRecNumber = intRecNumber + 1

If Not rs.EOF Then
Call Navigation

Me.Label1.Caption = CStr(intRecNumber + 1) & " of " & rs.RecordCount
Else
CommandButton6_Click
End If
End Sub

Private Sub CommandButton5_Click()

rs.MoveFirst
intRecNumber = 0

Call Navigation

Me.Label1.Caption = "1 of " & rs.RecordCount
End Sub
Private Sub CommandButton6_Click()

rs.MoveLast
intRecNumber = rs.RecordCount

Call Navigation

Me.Label1.Caption = CStr(intRecNumber) & " of " & rs.RecordCount
End Sub

Private Sub Navigation()

Me.TextBox1.Value = rs.Fields("FieldName1").Value
Me.TextBox2.Value = rs.Fields("FieldName2").Value
Me.TextBox3.Value = rs.Fields("FieldName3").Value
Me.TextBox4.Value = rs.Fields("FieldName4").Value
Me.TextBox5.Value = rs.Fields("FieldName5").Value

End Sub[/VBA]