The code for cascading combo boxes would go like this:

On the On Open event of the form, and on the Print button, add this code:

[Forms]![frmYourForm].[SerialNoList].RowSource = ""

For the City list box, I think you can use the same data source you have now:

"SELECT DISTINCT [City] FROM tblYourCityTable"

Then on the On Click event of your City list box, add this code:

Dim sSQL As String
Dim sString As String

If IsNull(Me.CityList.Value) Or Me.CityList.Value = "" Then
    [Forms]![frmYourForm].[SerialNoList].RowSource = ""
    Exit Sub
    Else
    sString = Me.CityList.Value
End If

sSQL = "SELECT DISTINCT [Serial No] FROM tblYourTable WHERE [City] = '" & sString & "' AND [Print] = 0"

[Forms]![frmYourForm].[SerialNoList].RowSource = sSQL

This will populate the Serial No List with only those serial numbers associated with that city, and that have not been printed.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

On the On Open event of the form, and on the Print button, add this code:

[Forms]![frmYourForm].[SerialNoList].RowSource = ""


You asked to add the code "ON THE PRINT BUTTON".But there is no print button in my form..
Do i need to add the code in On Open event of the form?
Am i making mistake?Can you help?

How do your users print the record they want? Whatever that event is, put that code on that event as well.

mmcdonal

Look it up at: http://wrox.books24x7.com

[Forms]![frmYourForm].[SerialNoList].RowSource = ""

Do i need to add anything(table name,etc)within the "" ?

No, this is a cascading list box, as you requested. The second list box (serial no) needs to be reset to blank until the user selects a city. Then the serial no list box gets populated. You also need to remove the current data source for the serial number list box as well.

mmcdonal

Look it up at: http://wrox.books24x7.com

When i click the city the serial no listbox is getting empty and nothing is loading to select in serial listbox.Have i written code wrongly somewhere?

You're going to have to send me the database or a part of it in order for me to see what you are doing.

mmcdonal

Look it up at: http://wrox.books24x7.com

Below is the code which am using for the form.Have also mentioned the row source for each listbox.

I have a form called Form with two listbox lstsl,lstsrl and two button Filter and Print.
The filter button opens a report called Stock Query which gives me Date,serial no, city, qty.

Can you help now with the given code....


Option Compare Database

Private Sub filter_Click()

Dim varItem As Variant
Dim strwhere As String
Dim strreport As String
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim lRecord As Long
Dim sRecord As String
Dim ctrl As Control
Dim intCurrentRow As Long

strwhere = ""
strreport = "Stock query"

'Using Text as Column(0)
If lstsl.ItemsSelected.Count > 0 Then
    strwhere = strwhere & "("
        For Each varItem In lstsl.ItemsSelected
            strwhere = strwhere & "[city] = '" _
            & Me![lstsl].Column(0, varItem) & "' Or "
        Next varItem
        strwhere = Left(strwhere, Len(strwhere) - 4)
          strwhere = strwhere & ")"
End If

If Len(strwhere) > 0 Then strwhere = strwhere & " And "

'Using integer PK as Column(0)
If lstsrl.ItemsSelected.Count > 0 Then
    strwhere = strwhere & "("
        For Each varItem In lstsrl.ItemsSelected
            strwhere = strwhere & "[Serial No] =" & Me![lstsrl].Column(0, varItem) & " Or "
        Next varItem
        strwhere = Left(strwhere, Len(strwhere) - 4)
          strwhere = strwhere & ")"
End If

If Len(strwhere) > 0 Then strwhere = strwhere

'For Text
Set ctrl = Me.lstsrl
For intCurrentRow = 0 To ctrl.ListCount - 1
    If ctrl.Selected(intCurrentRow) Then
        lRecord = ctrl.Column(0, intCurrentRow)
        sSQL = "UPDATE Stock SET [Print] = Yes WHERE [Serial No] = " & lRecord
        Set rs = New ADODB.Recordset
        rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    End If
Next intCurrentRow
intCurrentRow = 0

DoCmd.OpenReport strreport, acViewReport, , strwhere

Me.lstsrl.Requery

End Sub



Private Sub lstsl_Click()
Dim sSQL As String
Dim sString As String

If IsNull(Me.lstsl.Value) Or Me.lstsl.Value = "" Then
    [Forms]![Form].[lstsrl].RowSource = ""
    Exit Sub
    Else
    sString = Me.lstsl.Value
End If

sSQL = "SELECT DISTINCT [Serial No] FROM stock WHERE [City] = '" & sString & "' AND [Print] = 0"

[Forms]![Form].[lstsrl].RowSource = sSQL

End Sub
Private Sub print_Click()
On Error GoTo Err_print_Click

    [Forms]![Form].[lstsrl].RowSource = ""

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.PrintOut acSelection

Exit_print_Click:
    Exit Sub

Err_print_Click:
    MsgBox Err.Description
    Resume Exit_print_Click

End Sub

The above is the code am using for two buttons Filter and Print in my form called(Form)..
---------------

The first listbox lstsl(City) the rowsource is:

SELECT [City].City_Name FROM City;

On Click Event:

Private Sub lstsl_Click()
Dim sSQL As String
Dim sString As String

If IsNull(Me.lstsl.Value) Or Me.lstsl.Value = "" Then
    [Forms]![Form].[lstsrl].RowSource = ""
    Exit Sub
    Else
    sString = Me.lstsl.Value
End If

sSQL = "SELECT DISTINCT [Serial No] FROM stock WHERE [City] = '" & sString & "' AND [Print] = 0"

[Forms]![Form].[lstsrl].RowSource = sSQL

End Sub
-----------------

The second listbox lstsrl(serial No) the row source is:

SELECT [Stock].[Serial No], [Stock].Print FROM Stock WHERE ((([Stock].Print)=No));
--------------------

The form Row Source is:

SELECT [City].City_Name FROM City;

On Open event is:

=Forms![Form].lstsrl.RowSource=""

Is the code i have used is correct or it needs to be modified?

Does the code do what you want it to do? If so, then it is correct. Without seeing the database, or the pertinent part of it, it is hard for me to tell at this point. I think it is okay, but if it doesn't work, then it is not okay.

mmcdonal

Look it up at: http://wrox.books24x7.com