multiple combos help

[Admirer]

HI i need help while i am using multiple combos.I am fetchig values from database and populating in combo1 and in the other combo2 i want to fetch the data wich was havig relation betwee them iam posting my code here once go though it...i am getting an eror like Novalue give for one or more Required parameters

sqlstr = "select distinct ProductId from die1 "
   rs.Open sqlstr, con, adOpenDynamic, adLockPessimistic
  ' Combo2.Clear
    Do While Not rs.EOF
     Combo2.AddItem rs.Fields("ProductId").Value
     rs.MoveNext
     Loop
    
   ' Populating combo2 with proper scheduleId's belongs to Product
  rs.Close
  Set rs = Nothing
    
  sqlstr = "select distinct ScheduleId  from die1  where productid= " & Combo2.Text & ""
  
  Set rs = New ADODB.Recordset
  rs.Open sqlstr, con, adOpenDynamic, adLockPessimistic

[si_the_geek]

When you first fill Combo2, no item is selected - so there is no text. Before loading the data for the second combo, you need to either select an item via code (eg: Combo2.ListIndex=1) or wait until the user makes a selection.


By the way, if the productid field is a text/char field, you need to put ' on each side of the value.

[Admirer]

Hi now i was said by my sir to use listboxes to get the result.Here again i require 2 listboxes and one for getting produtcts(datatype text) and the other one after clicking the any of the product i should get the schedules and passes under it...How it can be possible? Plz help me
My code is

Set con = Nothing
    Set rs = Nothing
    Set con = New ADODB.Connection
    con.ConnectionString = "provider=microsoft.jet.oledb.4.0;" & "Data Source=" & App.Path & "\WRMdatabase.mdb"
    con.Open
   Set rs = New ADODB.Recordset
   sqlstr = "select distinct ProductId from die1 "
   rs.Open sqlstr, con, adOpenDynamic, adLockPessimistic
  ' Combo2.Clear
    Do While Not rs.EOF
     lstpro.AddItem rs.Fields("ProductId").Value
     rs.MoveNext
     Loop

until there i am viewing my all products wich were there but after clicking any products,the schedules and passes shld visible...How this can be done all are Text datatypes.

[ganeshmoorthy]

you need to change the field and table names according to your requirements

If List1.ListIndex <> -1 Then 
        MsgBox "No Item Selected !"
        Exit Sub
    End If
    sqlstr = "select Schedules From <Your Table Name> Where ProductID =" & List1.List(List1.ListIndex)
    rs.Open sqlstr, con, adOpenDynamic, adLockPessimistic
    List2.Clear
    Do While Not rs.EOF
        List2.AddItem rs.Fields("Schedule").Value
        rs.MoveNext
    Loop

[Admirer]

HI,Ganesh I had atached ur code in my project but the followinf error occurs..

No Value Given For One or more Required Parameters.
My code is like

sqlstr = "select ScheduleId From die1 Where ProductID =" & lstpro.List(lstpro.ListIndex)
    rs.Open sqlstr, con, adOpenDynamic, adLockPessimistic
    
    lstall.Clear
    Do While Not rs.EOF
        lstall.AddItem rs.Fields("ScheduleId").Value
        rs.MoveNext
    Loop

Here i feel mycode is not fetching the value of ScheduleId From database coz if i put msgbox to print nothing is appearing in place of ScheduleId.So Kindly help me..

[si_the_geek]

It's not clear from your post if ProductId is text or not - if it is, you need to put ' around the value, eg:

sqlstr = "select ScheduleId From die1 Where ProductID = '" & lstpro.List(lstpro.ListIndex) & "'"

If that doesn't solve it, answer these questions:

1. What line does the error occur on?

2. Where is this code being run? (is it after an item is selected in the list?)

3. What data type is ProductId?

[Admirer]

Hi
Tanq Very Much Now the problem is solved.