"Filling DataGrid based on DataCombo choice" is broken 1

[Sideman]

Hi there,
First of all, I'm still a learner using VB6(SP5), Access 2000, ADO Data controls (adcInv and adcInvGrid), all with bound controls.

On my form I have a DataCombo box bound to adcInv, a DataGrid control bound to adcInvGrid and two groups of Option buttons for user navigation. The user can select from the Option buttons and view various segments of the inventory. That works fine.

Where I'm stuck is on the DataCombo box...here, I try to allow selection of one of the Mfg names and then display the related records in the grid, but all I get is an empty grid.

I've scoured the entire forum, FAQs, links and can't find any clues, and have tried countless variations in the code...no change. The mdb is sound and all the data types are correctly matched up. I just can't seem to populate the DataGrid with data based on code in the dcbInv_Change procedure.

HELP!!! before I loose the rest of my mind!!
and Thanks for ANY suggestions (even if they make me look really stupid)

That said, below is the code I'm using. It works just fine except for the dcbInv_Change procedure (tried it in Click also with same result).

Private Sub dcbInv_Change()
    adcInvGrid.RecordSource = "SELECT * FROM Inventory _
        WHERE Mfg = ' " & dcbInv.Text & " ' "
    adcInvGrid.Refresh
    Set dgrInv.DataSource = adcInvGrid
    dgrInv.Refresh
End Sub

Private Sub optEQInv1_Click(Index As Integer)
    Select Case Index
        Case (1) ' Mfg
            adcInv.RecordSource = "SELECT Distinct Mfg FROM Inventory ORDER BY Mfg"
            adcInv.Refresh
            adcInvGrid.RecordSource = "SELECT * FROM Inventory ORDER BY Mfg"
            adcInvGrid.Refresh
            dcbInv.Refresh
            dcbInv.ListField = "Mfg"
            dcbInv.Text = ""
            optEQInv2(0).Value = True
        Case (2) ' Model
            adcInv.RecordSource = "SELECT Distinct Model FROM Inventory ORDER BY Model"
            adcInv.Refresh
            adcInvGrid.RecordSource = "SELECT * FROM Inventory ORDER BY Model"
            adcInvGrid.Refresh
            dcbInv.Refresh
            dcbInv.ListField = "Model"
            dcbInv.Text = ""
            optEQInv2(0).Value = True
        Case (3) ' Serial
            adcInv.RecordSource = "SELECT Distinct Serial FROM Inventory ORDER BY Serial"
            adcInv.Refresh
            adcInvGrid.RecordSource = "SELECT * FROM Inventory ORDER BY Serial"
            adcInvGrid.Refresh
            dcbInv.Refresh
            dcbInv.ListField = "Serial"
            dcbInv.Text = ""
            optEQInv2(0).Value = True
    End Select
    dgrInv.Refresh
    dcbInv.SetFocus
End Sub

Private Sub optEQInv2_Click(Index As Integer)
    Select Case Index
        Case (0) ' All
            adcInv.Refresh
            adcInvGrid.Refresh
        Case (1) ' Mail Machines
            adcInvGrid.Recordset.Filter = "Mail = True"
        Case (2) ' Folder/Inserters
            adcInvGrid.Recordset.Filter = "FoldIns = True"
        Case (3) ' Folders
            adcInvGrid.Recordset.Filter = "Fold = True"
        Case (4) ' Scales
            adcInvGrid.Recordset.Filter = "Scale = True"
        Case (5) ' Openers
            adcInvGrid.Recordset.Filter = "Open = True"
        Case (6) ' Bursters
            adcInvGrid.Recordset.Filter = "Burster = True"
        Case (7) ' Collators
            adcInvGrid.Recordset.Filter = "Collator = True"
        Case (8) ' Addressers
            adcInvGrid.Recordset.Filter = "Address = True"
        Case (9) ' Accessories
            adcInvGrid.Recordset.Filter = "Accessory = True"
        Case (10) ' Computers
            adcInvGrid.Recordset.Filter = "Computer = True"
        Case (11) ' Printers
            adcInvGrid.Recordset.Filter = "Printer = True"
        Case (12) ' Miscellaneous
            adcInvGrid.Recordset.Filter = "Misc = True"
    End Select
    dgrInv.Refresh
    dcbInv.SetFocus
End Sub

Of all the things I've lost, I miss my mind the most!
Sideman

 

[Golom]

It may be as simple as this. Suppose that "dcbInv.Text" contains "ABC". Then your SQL in the dcbInv_Change Event resolves to

adcInvGrid.RecordSource = "SELECT * FROM Inventory _
WHERE Mfg = ' ABC ' "

and that is not going to match a manufacturer named "ABC" because "ABC" is NOT equal to " ABC " (note the leading and trailing spaces.)

 

[Sideman]

Hi Golom,

Oh my god, I knew it!!!! I REALLY AM STUPID!!!!

Just kidding. You are right on the button...2 spaces were causing all the grief...another lesson learned! It works fine now.

Thanks a million, and a star for your kind expertise.

Of all the things I've lost, I miss my mind the most!
Sideman