Using an Excel Spreadsheet as a DataSource using DAO

[Slicemahn]

Hello Everyone!

i need a second pair of eyes to figure where I have gone wrong in my coding. I have attached a worksheet that is to be used as a datasource for call information. There is a very important reason why it is in this form as opposed to a pivot table or filter list: It all comes down to data integrity!. Having said this, I have dropdown lists on the Report tab that will select the Vice-President and then the corresponding list of Call Centres. I am challenged in two areas:

(1) The data validation lists only allow for me to filter for Vice_President, Call_Centre and Director. It does not allow for the other two categories Sr. Manager and Team Manager.

(2) After selecting the Vice_President there is a runtime 1004 error : Application-defined or object error.

I cycled through my code without the luck in finding what is causing the error. I am hopeful that one of the experts in this forum may find and inform me of my wayward coding.

Many thanks,
Slice

[Bob Phillips]

I don't see what you describe.

The DV only gets set for one cell for me, and no error 1004.

Try changing

[vba]


With rng.Cells(1, 1).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=RetrieveList(sqlstr)
End With
[/vba]

to

[vba]


With rng.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=RetrieveList(sqlstr)
End With
[/vba]

[Slicemahn]

Hi xld,

This was the case for me as well but I don't think your modification will do it. What I am doing is really similar to a filter. Upon selecting a VP, the next dropdown list will hold all the call centres attached to that VP. When that Call Centre is chosen then all associated directors will be shown in the DV List. So far I have managed to get to only one level of filtering before I get this error.

[p45cal]

1. In the Report sheet code module, look at changing[vba] If Not Application.Intersect(Target, Me.Range("B1:B3")) Is Nothing Then
[/vba]to[vba] If Not Application.Intersect(Target, Me.Range("B1:B5")) Is Nothing Then
[/vba] (or perhaps B4).
2. Look at this line:[vba] Case 2 'Call_Centre changed
sqlstr = "SELECT [Director] FROM [RAW$]" & _
"WHERE [Vice_President] = " & Chr(34) & rng.Offset(-1).Value & Chr(34) & _
"AND [Call_Centre]= " & Chr(34) & rng.Value & Chr(34) & " ORDER BY [Director]"
[/vba]which produces this as sqlstr:

SELECT [Director] FROM [RAW$]WHERE [Vice_President] = "COSTANZA GEORGE "AND [Call_Centre]= "Bangkok" ORDER BY [Director]

which when used in RetrieveList as an argument yields:
NADAL RAFAEL,PHILLIPS KAREN,PHILLIPS KAREN,PHILLIPS KAREN,PHILL......
with far too many more KAREN PHILLIPSes, and I think it's this when used in the line[vba] .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=RetrieveList(sqlstr)
[/vba]that returns the error.
Not knowing enough about SQL to tell you what it should be, I'd guess the fault lies in the Case 2 block above. (Should there be a GROUP BY in there somewhere?).
Sorry I can't put it right myself - but I feel it's a starting point.