access97 - option group determines values in combo box...

[stellJess]

Hello Experts!

can anyone out there please tell me if this is possible. need to select from option group of 5 and base on the option selected, the combo box on the same unbound form needs to only display the values for that option.

o technical
o professional
o n

then in the combo box below
if option(1) is selected then list only technical records
if option(2) is selected then list only profesional records
else n will list all records.

i've tried a select case but it does not seem to work using a docmd.openQuery for each option. i tried using a sql for the rowSource and it is not working either.

the select case was placed in the lostFocus property of the optionGroup, in the afterUpdate property of the optionGroup. i know i'm overlooking something simple. can you tell me what i'm doing wrong.

in advance, thanks for your help.
stellJess

 

[GHolden]

Hi,

Something like this should work in the OnClick for the option group

Private Sub Frame0_Click()
Dim strSQL As String


Select Case Frame0
Case Is = 1
strSQL = "SELECT * FROM TABLE WHERE FIELD = 'WHATEVER FOR TECHNICAL'"
Case Is = 2
strSQL = "SELECT * FROM TABLE WHERE FIELD = 'WHATEVER FOR PROFESSIONAL"
Case Is = 3
strSQL = "SELECT * FROM TABLE"
End Select

Combo8.RowSource = strSQL
Combo8.Requery

End Sub

You will obviously have to mod for your own SQL etc. If you still have problems post your code.

Hope this helps.



There are two ways to write error-free programs; only the third one works.

 

[stellJess]

Hi GHolden

thanks for your reply. however, it is not working. here is my code in the onClick property for the option group (ogRevCenter) and the combo box (cboDepartmentNo). case 3 - 5 has been commented out but has the original doCmd.openQuery commands. will you take a lookC and tell me where the problem is.

what's happening ---> the ogRevCenter works fine but the cboDepartmentNo is not being populated w/the strSql results.

Private Sub ogRevCenter_Click() 'and cboDepartmentNo_Click()

Dim strSql As String

Select Case ogRevCenter

Case Is = 1

strSql = "SELECT PHYSICIANS.physicianID AS ID, PHYSICIANS.physicianName AS Name" _
& "FROM PHYSICIANS" _
& "WHERE (((PHYSICIANS.revenueCenter) Like 'PROFESSIONAL*')) ORDER BY PHYSICIANS.physicianName;"

Me.cboDepartmentNo.RowSource = strSql
' Me.cboDepartmentNo.Requery
Case Is = 2

strSql = "SELECT PHYSICIANS.physicianID AS ID, PHYSICIANS.physicianName AS Name" _
& "FROM PHYSICIANS" _
& "WHERE (((PHYSICIANS.revenueCenter) Like '*100% TECHNICAL'))ORDER BY PHYSICIANS.physicianName"

Case Is = 3
'DoCmd.OpenQuery "caseDirect", acViewNormal

Case Is = 4
'DoCmd.OpenQuery "caseIndirect", acViewNormal

Case Is = 5

'DoCmd.OpenQuery "caseAll", acViewNormal

End Select
' Me.cboDepartmentNo.RowSource = strSql
Me.cboDepartmentNo.Requery
End Sub


in advance, thank you so much.

 

[GHolden]

Hi,

Don't know is this is the problem, however...

strSql = "SELECT PHYSICIANS.physicianID AS ID,PHYSICIANS.physicianName AS Name" _
AS Name" _
& "WHERE (((PHYSICIANS.revenueCenter) Like 'PROFESSIONAL*')) ORDER BY PHYSICIANS.physicianName;"

looks like it is missing some spaces...

eg.

AS Name" _
& "WHERE (((PHYSICIANS.revenueCenter)

will give you...

AS NameWHERE (((PHYSICIANS.revenueCenter)

Try adding spaces inside the strings so when they are concatonated the words don't get joined together...

Also you can try putting a message box after the strSQL is assigned, this quite often highlights errors in the string concatonation which are otherwise hard to spot.

ie.

strSql = "SELECT PHYSICIANS.physicianID AS ID, PHYSICIANS.physicianName AS Name" _
& "FROM PHYSICIANS" _
& "WHERE (((PHYSICIANS.revenueCenter) Like 'PROFESSIONAL*')) ORDER BY PHYSICIANS.physicianName;"

MsgBox strSQL





There are two ways to write error-free programs; only the third one works.

 

[stellJess]

Hi GHolden,

tried the spacing suggestion -- not it.

even tried the choose() that does not completely work either.

while the choose() does place the object (as a sql statment) into the me.cboDepartmentNo.rowSource for the first value, the remaining values (choices) for the optionGroup (2 thru 5) has a "null" value giving error msg: "invalid use of null."

here is my code for the choose()

Private Sub ogRevCenter_Click()

Dim caseProfessional As Object
Dim caseTechnical As Object
Dim caseDirect As Object
Dim caseIndirect As Object
Dim caseAll As Object
Set caseProfessional = Me.cboDepartmentNo
Set caseTechnical = Me.cboDepartmentNo
Set caseDirect = Me.cboDepartmentNo
Set caseIndirect = Me.cboDepartmentNo
Set caseAll = Me.cboDepartmentNo

me.cboDepartmentNo.RowSource=Choose([ogRevCenter], _
caseTechnical, caseProfessional, caseDirect, _
caseIndirect, caseAll)

i've tried to correct it myself b4 bothering you again, to no avail. so again, will you take a lookC and tell me where the problem is.

thanks!
stellJess

 

[GHolden]

Hi,

Appologies for the delayed response.

I've never used Choose before. So I'm not sure what's going wrong there...

Have you checked that the SQL is correct. Eg have you tried manually copying the SQL into the rowsourse of the combo box to see if you get the expected results?



There are two ways to write error-free programs; only the third one works.