Problem with multiple Combos

[azizsohail]

hi there,

I got a unbound form, which got 4 unbound combos.

1. ApplicationCombo
2. FolderCombo
3. SubFolderCombo
4. ReportCOmbo

ApplicationCombo is filled in the beforeupdate event.
FolderCmbo is filled with data on the afteraupdate event of Application Combo.

The AfterUpdate event of folder combo fill in the SubfolderCombo and also ReportCombo using the following code (its possible to have reports in in main folders).

Private Sub FolderCombo_AfterUpdate()
On Error GoTo Err_fcmobo_Click
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim srtqry As String

ReportCombo.RowSource = ""
ReportCombo.Value = Null
SubFolderCombo.RowSource = ""
SubFolderCombo.Value = Null

rsData.ActiveConnection = CurrentProject.Connection
rsData.CursorType = adOpenDynamic
strqry = "SELECT RTname.LinkedFolder_ID,RTName.description_vc from " & _
" ICT_Reports_ReportName_T (nolock) RTName " & _
" where (RTname.expiry_date_dt>getdate() or RTname.expiry_date_dt is null) " & _
"and RTname.LinkedFolder_ID ='" & FolderCombo.Column(0) & "' order by 1;"

rsData.Open (strqry)

Do While Not rsData.EOF
ReportCombo.RowSource = ReportCombo.RowSource & rsData(0) & ";" & rsData(1) & ";"
rsData.MoveNext
Loop

rsData.Close

'----------------------SubFolder script

rsData.ActiveConnection = CurrentProject.Connection
rsData.CursorType = adOpenDynamic
strqry = "SELECT sct.code_ID,sct.description_vc from Shared_Codes_T (nolock) sct " & _
" where sct.Type_ID = 5 and sct.posting_code_vc ='" & FolderCombo.Column(0) & "'order by 1;"

rsData.Open (strqry)

Do While Not rsData.EOF
SubFolderCombo.RowSource = SubFolderCombo.RowSource & rsData(0) & ";" & rsData(1) & ";"
rsData.MoveNext
Loop

rsData.Close
Set rsData = Nothing


Exit_fcmobo_Click:
Exit Sub
Err_fcmobo_Click:
MsgBox Err.Description
Resume Exit_fcmobo_Click

End Sub



IF you click on the Subfolder and make a selection,the afterupdate event will clear the Reportcombo by

me.Reportcombo.rowsource=""
me.reportcombo=null

then fill it with list of reports within that subfolder by excuting the following code

Private Sub SubFolderCombo_AfterUpdate()
On Error GoTo Err_SFcmobo_Click

Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
Dim srtqry As String

ReportCombo.RowSource = ""
ReportCombo.Value = Null

rsData.ActiveConnection = CurrentProject.Connection
rsData.CursorType = adOpenDynamic
strqry = "SELECT RTname.LinkedFolder_ID,RTName.description_vc from " & _
" ICT_Reports_ReportName_T (nolock) RTName " & _
" where (RTname.expiry_date_dt>getdate() or RTname.expiry_date_dt is null) " & _
"and RTname.LinkedFolder_ID ='" & SubFolderCombo.Column(0) & "' order by 1;"

rsData.Open (strqry)

Do While Not rsData.EOF
ReportCombo.RowSource = ReportCombo.RowSource & rsData(0) & ";" & rsData(1) & ";"
rsData.MoveNext
Loop

rsData.Close
Set rsData = Nothing

Exit_SFcmobo_Click:
Exit Sub

Err_SFcmobo_Click:
MsgBox Err.Description
Resume Exit_SFcmobo_Click

End Sub

The problem is with the reportCombo

the ReportCombo settings are
ColumnCount=2
Columnbound=1
Columnwidth=0cm;6cm

when i click on it, its displays the following data... (please remmember the numbering is just for you, its not used in the combo)

number Code, ReportName
1. 20 Accounts KPI
2. 23 Accounts Statistics
3. 28 SubAccounts details
4. 30 all categories

now let say if i select number 3, which is

Code=28 and ReportName=SubAccounts Details

It shows Code=20 and ReportName=Accounts KPI

No matter whatever i select, it will show me

It shows Code=20 and ReportName=Accounts KPI as the selected value

in the afterUpdate event of the ReportCombo i just put in

Msgbox me.reportcombo.column(0)

every time i get the value 20 only... its dont change at all....

there is nothing else or no other event of the report combo used.


i dont know how to solve the problem. Please help.

Regards ....

 

[GinaWhipp]

Well, you're going to have ReQuery or something to get the values to change. Right now once you set them you do nothing to change them.

 

[azizsohail]

GinaWhipp - thanks for your responce.

a bit confusion. i do click on the dropdown combo "Reportcombo" and select 5 or 6 value available in the list, but it will show you the frist value from the list select.

No matter what i do....

 

[GinaWhipp]

1. Is the Combo Box bound or unbound?

2. What is the RowSource?

3. Whay are you setting it to NULL or empty in the After_Update event of the other combos?

4. Are you trying to create Cascading Combos?

 

[azizsohail]

hi thanks for your time, i sort out the problem, the problem was due to the the query procducing the same ID number for all ... description. which force the combo to show the frist discription.

 

[GinaWhipp]

Glad you sorted it and shared the solution, it is bound to help someone else!