Bring Data From MS Acess in a Textbox

[gatarossi]

Dear all,

How can I complete/fix this code to return access data in textbox in a
excel form?

Sub returnao()
Dim wrkSpace As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strDB As String

strDB = ThisWorkbook.Path & "\db_sales.mdb"

Set wrkSpace = Workspaces(0)
Set db = wrkSpace.OpenDatabase(strDB, False, False, "MS
Access;PWD=sales")

strSQL = "SELECT TOP 1 tbl_code.code FROM tbl_code "
strSQL = strSQL & "WHERE (((tbl_code.division)= '" & cboDivision &
"')) "
strSQL = strSQL & "ORDER BY tbl_code.code DESC ;"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'here is the problem... bring the rs data in the txtSNumber

With frmCode
.txtSNumber.Value = rs
'.cboApproval.ListIndex = 0
End With

rs.Close
db.Close
wrkSpace.Close

Set rs = Nothing
Set db = Nothing
Set wrkSpace = Nothing
End Sub

Thanks

André.

 

[Harald Staff]

Dunno about DAO. But in ADO it's

..txtSNumber.Text = rs(0)

HTH. Best wishes Harald


Dear all,

How can I complete/fix this code to return access data in textbox in a
excel form?

Sub returnao()
Dim wrkSpace As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strDB As String

strDB = ThisWorkbook.Path & "\db_sales.mdb"

Set wrkSpace = Workspaces(0)
Set db = wrkSpace.OpenDatabase(strDB, False, False, "MS
Access;PWD=sales")

strSQL = "SELECT TOP 1 tbl_code.code FROM tbl_code "
strSQL = strSQL & "WHERE (((tbl_code.division)= '" & cboDivision &
"')) "
strSQL = strSQL & "ORDER BY tbl_code.code DESC ;"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'here is the problem... bring the rs data in the txtSNumber

With frmCode
.txtSNumber.Value = rs
'.cboApproval.ListIndex = 0
End With

rs.Close
db.Close
wrkSpace.Close

Set rs = Nothing
Set db = Nothing
Set wrkSpace = Nothing
End Sub

Thanks

André.