Dear all,

I'm trying to create a validation list with data from access. The code
below already do it, but I have a big problem: in the field of access
database I can have duplicate data...

table: period

fields
year_month         year_quarter         year
200701                  200701               2007
200702                  200701                2007
200703                  200701                2007
200704                  200702                2007
.
.
.
And the code below brings duplicate data. How can I solve this
problem?
I wouldn't like to bring all information to excel and after transform
it to unique values only... Is there other way to do it?

Thanks a lot!!!

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
Dim filenm As String

On Error GoTo ErrorHandler

filenm = ThisWorkbook.Path & "\controle_despesas.mdb"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
filenm & ";"

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 expenses_period()

Dim rst As ADODB.Recordset
Dim strValidationList As String
Dim strValidationList2 As String
Dim strValidationList3 As String

Set rst = RunQuery("Select *", "From period", "", ";", False)
rst.MoveFirst
strValidationList = rst.Fields("year_month").UnderlyingValue
strValidationList2 = rst.Fields("year_quater").UnderlyingValue
strValidationList3 = rst.Fields("year").UnderlyingValue
rst.MoveNext
Do While Not rst.EOF
   strValidationList = strValidationList & ", " &
rst.Fields("year_month ").UnderlyingValue
   strValidationList2 = strValidationList2 & "," &
rst.Fields("year_quater ").UnderlyingValue
   strValidationList3 = strValidationList3 & "," & rst.Fields("year
").UnderlyingValue
   rst.MoveNext
Loop

MsgBox strValidationList, vbInformation
MsgBox strValidationList2, vbInformation
MsgBox strValidationList3, vbInformation

'Range("D6:IV6").Validation.Delete
'Range("D6:IV6").Validation.Add xlValidateList, , , strValidationList

End Sub

Thanks a lot!!!

André.