Problem with parent/child with ADO controls

[dmuroff]

Hi,

I have a Access database with 2 tables:
Supplier: PK Supplier_ID
Items: PK Item_ID
FK Supplier_ID

I am trying to use 2 ADO controls on 1 form. I am showing the Supplier fields on the form and then the items associated with the form in a datagrid control.

My problem is that the datagrid is showing all the records in the items table. I would like it to show just those associated with the given Supplier_ID.

I know with DED's there is an option where you can just set parent/child properties but I am unsure how to do this with ADO's.

Any help would be greatly appreciated. Thanks!

 

[Sheco]

Look at the DataSource for your grid.

 

[dmuroff]

The datasource is the ADO control which has a recordsource of tblItems.

The datagrid is displaying all the records in tblItems...I just want it to display the records that have the SUpplier_ID equal to the supplier_ID currently selected with the Supplier ADO control

I know I have to change the recordsource of the datagrid but I am not sure what it should be. Thanks

 

[Sheco]

If you are using the ADODC then maybe just leave the grid the way it is and change the way you are populating the ADODC.

Like if right now you have the CommandType set to table then instead you can set it to Text and change the RecordSource property to some query like
"SELECT * FROM Items WHERE Supplier_ID = " & lblSID.Caption

Assuming that the supplier id is in a label on your form named lblSID... change that to whatever it is on your form that has it.

 

[dmuroff]

Hi,

Ok..I am rather new at the whole programming thing...

Do I change the recordsource in design time in the ADODC properties? or do I do it at run time?

At design time I change the command type to 'adcmdtext'

then in the command text i type: select * from tblitems where supplier_id = txtsupplier_id.text

I am unable to put quotation marks in the statement because it gives me an invalid error.

I see what you are doing by setting the recordsource but for some reason it doesn't work in this situation.

If for example I write: select * from tblitems where supplier_id = 1
It works perfectly...I just need to extract the currently selected supplier_id somehow

Any ideas?
Thanks!

 

[dmuroff]

Does anyone have any suggestions?

Thank you

 

[Sheco]

Just leave the actual sql text blank at design time because you won't know the ID for the WHERE clause until runtime.

 

[dmuroff]

ok here's what I have in the form load event:

With Adodc1
    .ConnectionString = "Provider=Microsoft.jet.oledb.4.0;" & _
        "Persist Security info=false;Data Source=" & _
        App.Path & "\Trillium.mdb;mode=read"
        .CommandType = adCmdText
        .RecordSource = "Select * from tblitems where tblitems.supplier_id = " & txt1Supplier_ID.Text & ";"
        .Refresh
End With

Now this work but only for the supplierid that is loaded...

how will I get the adodc1 to refresh when txt1supplier_ID is changed? Thanks

 

[Sheco]

Any reason you couldn't just do it on the change event for the textbox?

Maybe something like this:
Adodc1.RecordSource = "Select * from tblitems where tblitems.supplier_id = " & txt1Supplier_ID.Text & ";"
Adodc1.Refresh