G
gatarossi
Dear all,
with help, I create a validation in a range of excel (the data is from
access). For example: in this code below excel create a list - the
data is of access - and put this list like a validation in a range.
This list contain the accounting code. Now I'm looking for a code that
put the accounting description. It´s very important to me, that all
I'm doing is VBA code!!! and I must work with one sheet, I wouldn't
like to bring all information of the table of access and put in a
sheet!!!
For example, if I select in range a1 the code 10, then in the range b1
appear salary.
In the access, the fields of my table is:
accounting_code accounting_description
10 salary
20 tax
..
..
..
The code is:
Private Const m_cDBLocation As String = "C:\Documents and
Settings\andre\Meus documentos\expenses.mdb"
Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As
String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As
Boolean) As ADODB.Recordset
Dim strConnection As String
On Error GoTo ErrorHandler
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"
Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With
RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
Exit Function
ErrorHandler:
MsgBox Err.Description
End Function
Sub Validation_Expenses()
Dim rst As ADODB.Recordset
Dim strValidationList As String
Set rst = RunQuery("Select *", "From accounting_plan", "", ";",
False)
rst.MoveFirst
strValidationList = rst.Fields("accounting_code").UnderlyingValue
rst.MoveNext
Do While Not rst.EOF
strValidationList = strValidationList & ", " &
rst.Fields("accounting_code").UnderlyingValue
rst.MoveNext
Loop
Range("A1:A5").Validation.Delete
Range("A1:A5").Validation.Add xlValidateList, , ,
strValidationList
End Sub
Thanks a lot!
André.
with help, I create a validation in a range of excel (the data is from
access). For example: in this code below excel create a list - the
data is of access - and put this list like a validation in a range.
This list contain the accounting code. Now I'm looking for a code that
put the accounting description. It´s very important to me, that all
I'm doing is VBA code!!! and I must work with one sheet, I wouldn't
like to bring all information of the table of access and put in a
sheet!!!
For example, if I select in range a1 the code 10, then in the range b1
appear salary.
In the access, the fields of my table is:
accounting_code accounting_description
10 salary
20 tax
..
..
..
The code is:
Private Const m_cDBLocation As String = "C:\Documents and
Settings\andre\Meus documentos\expenses.mdb"
Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As
String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As
Boolean) As ADODB.Recordset
Dim strConnection As String
On Error GoTo ErrorHandler
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"
Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With
RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
Exit Function
ErrorHandler:
MsgBox Err.Description
End Function
Sub Validation_Expenses()
Dim rst As ADODB.Recordset
Dim strValidationList As String
Set rst = RunQuery("Select *", "From accounting_plan", "", ";",
False)
rst.MoveFirst
strValidationList = rst.Fields("accounting_code").UnderlyingValue
rst.MoveNext
Do While Not rst.EOF
strValidationList = strValidationList & ", " &
rst.Fields("accounting_code").UnderlyingValue
rst.MoveNext
Loop
Range("A1:A5").Validation.Delete
Range("A1:A5").Validation.Add xlValidateList, , ,
strValidationList
End Sub
Thanks a lot!
André.