Validation from Access

  • Thread starter Thread starter gatarossi
  • Start date Start date
G

gatarossi

Dear all,

I'm trying to create a validation in a range (like one list, when we
click in the cell appear all possible data). The data are in one field
in one table in ms access.

I have a suggestion that consist in firstly put all data in one column
of excel and make the validation from this range, but I think to my
work, it's better pick the information directly to access.

Is anybody could help me with this VBA code?

Thanks a lot!
 
Here is some code to do it. You need to reference your project to "Microsoft
ActiveX Data Object 2.? Library" (int the VBE Tools -> References ...) . You
will need to know the appropriate select statement and change the database
and table references.

Private Const m_cDBLocation As String = "C:\Test.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 Example()
Dim rst As ADODB.Recordset
Dim strValidationList As String

Set rst = RunQuery("Select *", "From tblRegions", "", ";", False)
rst.MoveFirst
strValidationList = rst.Fields("Region").UnderlyingValue
rst.MoveNext
Do While Not rst.EOF
strValidationList = strValidationList & ", " &
rst.Fields("Region").UnderlyingValue
rst.MoveNext
Loop
Range("A1").Validation.Delete
Range("A1").Validation.Add xlValidateList, , , strValidationList
End Sub
 
Per (e-mail address removed):
I have a suggestion that consist in firstly put all data in one column
of excel and make the validation from this range, but I think to my
work, it's better pick the information directly to access.
Click to expand...

One advantage of copying the data into the spreadsheet and
working from the copy is that it will make the spreadsheet
portable. i.e. whoever uses it won't have to worry about the
sheet being able to find the MS Access table.
 
Dear Jim Thomlinson,

Thanks a lot!!! I think that you know everything of Excel!!!


Dear PeteCresswell,

Thnaks for the coments!!!


André.
 
Back
Top