combobox choice filters out another combobox

[JohnPhilipps]

Hello,

I have two data comboboxes on a VB6 form "DataComboMasterPolicy" and "DataComboPurchasingGroup" after selecting the first appropriate choice with "DataComboMasterPolicy" I would like for the 2nd datacombox "DataComboPurchasingGroup" to only display the data associated with the user's selection.

The data is in an Access 2003 db.
My code for both boxes:

Private Sub DataComboMasterPolicy_Click(Area As Integer)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
    
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\test.mdb"

rs.Open "SELECT DISTINCT MasterPolicy FROM Certificate ORDER BY MasterPolicy", cn, adOpenStatic, adLockOptimistic
    
Set DataComboMasterPolicy.RowSource = rs
DataComboMasterPolicy.ListField = "MasterPolicy"

End Sub


Private Sub DataComboPurchasingGroup_Click(Area As Integer)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
    
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\test.mdb"

rs.Open "SELECT DISTINCT Code FROM qryMasterPurchasing WHERE MasterPolicy = " & DataComboMasterPolicy, cn, adOpenStatic, adLockOptimistic

Set DataComboPurchasingGroup.RowSource = rs
DataComboPurchasingGroup.ListField = "Code"

End Sub

However when I select the first combox box it doesn't appear to do what it is suppose to because I get this error:
"no value given for one or more required parameter"
I chose debug and can see the 3rd line from the bottom highlighted in yellow:
rs.Open "SELECT DISTINCT Code FROM qryMasterPurchasing WHERE MasterPolicy = " & DataComboMasterPolicy, cn, adOpenStatic, adLockOptimistic

which I attached as the "error" file, which basically tells me the line as an error yet if I over the "& DataComboMasterPolicy" section I can see it has properly memorized the selection from DataComboMasterPolicy

Could somebody help me with what I am doing wrong, or perhaps there is a tutorial somewhere?

Thank you,

John

[sinnerFA]

Change DataComboMasterPolicy to DataComboMasterPolicy.SelText

HTHs
SinnerFA

[JohnPhilipps]

Change DataComboMasterPolicy to DataComboMasterPolicy.SelText

HTHs
SinnerFA

Thanks for your reply sinnerFA

I tried the change of DataComboMasterPolicy to DataComboMasterPolicy.SelText, but it gives me the following error:
Syntax error (missing operator) in query expression 'MasterPolicy ='.

The code used:

rs.Open "SELECT DISTINCT Code FROM qryMasterPurchasing WHERE MasterPolicy = " & DataComboMasterPolicy.SelText, cn, adOpenStatic, adLockOptimistic

also tried DataComboMasterPolicy.Text but it still doesn't work and I get the original problem message:
"no value given for one or more required parameter"

thanks

[sinnerFA]

I am sorry wasn't even paying attention to the whole string I just had seen the issue with the value of the combobox.
Try This:

Dim strSQL = "SELECT DISTINCT Code FROM qryMasterPurchasing WHERE MasterPolicy = '" & DataComboMasterPolicy.SelText & "'"
rs.Open strSQL, cn, adOpenStatic, adLockOptimistic

HTHs
sinnerFA

[JohnPhilipps]

Thank you for such a quick response.

I tried it but I get syntax error.
I tried a couple of different ways, because when I paste it on the VB6 form it first wanted to place the whole thing on one line.
Code used:

Dim strSQL = "SELECT DISTINCT Code FROM qryMasterPurchasing WHERE MasterPolicy = '" & DataComboMasterPolicy.SelText & "'"
 rs.Open strSQL, cn, adOpenStatic, adLockOptimistic

the exact error is:
Compile error
syntax error

thank you much for your help

[sinnerFA]

Dim strSQL as String 
strSQL = "SELECT DISTINCT Code FROM qryMasterPurchasing WHERE MasterPolicy = '" & DataComboMasterPolicy.SelText & "'"
 rs.Open strSQL, cn, adOpenStatic, adLockOptimistic

I have not worked in VB6 in awhile so bare with me....

[JohnPhilipps]

Thank you very much for your help, it was a little syntax error :)

John

[jireh]

In the above suggestions does it solve your problem? You must be aware also on what type of event you did put your code.