gibbo1715
10-17-2005, 10:20 AM
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
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
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
I am linking to an access database from an excel userform and wondering about navigation buttons
I was going down the lines as follows
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
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