Popluating Combobox with recordsource field that's dynamically set. 1

[CrystalVisualBOracle]

Hi there. I would greatly appreciate any help I can get with this problem.

I've been working on this project that involves creating crystal reports in a VB6 application against an Oracle 8 database. Trouble is I'm fairly new to both Crystal Reports and VB6. Right now I've designed an ADODB connection which is created during runtime of the VB application. When the form is loaded, I'm using a recordsource to populate a combo box on the form with one of the fields returned. When a user selects a record from the dropdown list in the combo box, I would like the application to automatically use their selection to populate another combo box on the form.

For example...

1 - say recordsouce1 used to populate ComboBox1 says
"Select x.descriptor, x.id from table x"

2 - I then populate combobox1 with all of the x.descriptor fields returned.

3 - The user clicks on combobox1 and selects one of the fields in the combo box.

4 - The program then uses the combobox1 value to create recordsource2 as follows
"Select y.date from table y where y.id=" &recordsource1.field(1)
* Notice that the user selects field(0) in combobox1 but I need to reference field(1) in the above select statement.

5 - the y.date field is then used to populate combobox2.

So far I've gotten to step 2 above but from there it doesn't do what I want it to. I'm including my code below so you can see what I may have missed.

<b>
Public adoConn As New ADODB.Connection
Public adoRecordsetName As New ADODB.Recordset
Public adoRecordsetDate As New ADODB.Recordset
Public txtPubrName As String
Public intPubrID As Integer


Private Sub Form_Load()
With adoConn
.ConnectionString = &quot;Provider=MSDAORA.1;Data Source=DATABEAST;Server=DATABEAST;User ID=-----;Password=-----;Persist Security Info=False&quot;
.CursorLocation = adUseClient
.Open
End With

With adoRecordsetName
.ActiveConnection = adoConn
.CursorType = adOpenDynamic
.Open &quot;Select a.descriptor, a.id from d2main.m_publisher a where exists (select 'X' from d2alllogs.r_pubr_rpt_log b where a.id=b.m_pubr_id)&quot;
.MoveFirst
End With

cboPubrName.Clear
cboDATESTAMP.Clear
Do Until adoRecordsetName.EOF
cboPubrName.AddItem adoRecordsetName(0).Value
adoRecordsetName.MoveNext
Loop

End Sub

Private Sub cboPubrName_Click()

txtPubrName = cboPubrName.Text
intPubrID = adoRecordsetName(1).Value

MsgBox &quot;The PubrName chosen is &quot; &amp; txtPubrName &amp; &quot; The pubr id is &quot; &amp; intPubrID, &quot;Publisher Data&quot;, 0

With adoRecordsetDate
.ActiveConnection = adoConn
.CursorType = adOpenDynamic
.Open &quot;Select distinct(a.datestamp) from d2alllogs.r_pubr_rpt_log where a.id = &quot; &amp; intPubrID &amp; &quot;;&quot;
.MoveFirst
End With

cboDATESTAMP.Clear
Do Until adoRecordsetDate.EOF
cboDATESTAMP.AddItem adoRecordsetDate(0).Value
adoRecordsetDate.MoveNext
Loop

cboDATESTAMP.SetFocus

End Sub
</b>

I would really appreciate someone's help with this.

Thanks a lot,
CrystalVisualBOracle

 

[swilliams]

Without testing any of this, I think you are only one step away from achieving what you want. The crux of it lies in storing the ID from the first select statement with the descriptor items that you add to the combo box.

To accomplish this, you should use the ItemData property of the combo box. In your Form_Load sub, instead of using:

Do Until adoRecordsetName.EOF
cboPubrName.AddItem adoRecordsetName(0).Value
adoRecordsetName.MoveNext
Loop

you should use the following:

Do Until adoRecordsetName.EOF
cboPubrName.AddItem adoRecordsetName(0).Value
cboPubrName.ItemData(cboPubrName.NewIndex) = adoRecordsetName(1).Value
adoRecordsetName.MoveNext
Loop

And then in the cboPubrName_Click() sub, instead of using

With adoRecordsetDate
.ActiveConnection = adoConn
.CursorType = adOpenDynamic
.Open &quot;Select distinct(a.datestamp) from d2alllogs.r_pubr_rpt_log where a.id = &quot; &amp; intPubrID &amp; &quot;;&quot;
.MoveFirst
End With


you should use:

With adoRecordsetDate
.ActiveConnection = adoConn
.CursorType = adOpenDynamic
.Open &quot;Select distinct(a.datestamp) from d2alllogs.r_pubr_rpt_log where a.id = &quot; &amp; cboPubrName.ItemData(cboPubrName.ListIndex) &amp; &quot;;&quot;
.MoveFirst
End With
Simon