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

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

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

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

Edited 6 Years Ago by JohnPhilipps: don't like the smily

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....

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

This question has already been answered. Start a new discussion instead.