Sync two Combo boxes in Datasheet view of a FORM

[Brad]

I have a SubForm in "Datsheet view" and have two combo boxes that I want one
to limit the list of the other
The follow are the names and inforamation
---Main Form: [FMainTCC] View: Single Form
---SubForm: [FMainSubTD-TE2] View: Datasheet
---------Combo Box1: [TP-Key]
----------------------TP-Key bound column
---------Combo box2: [TPSub-Key] (Want to limit list of this combo from
[TP-Key]combo)
----------------------------TPSub-Key is bound Column
-----------------------------TP-Key is one of the columns

TP-Key is the field in both combo boxes that I want linked

I have tried everything. I read all the helps limiting the list of one combo
box with another and tried most, but none have worked. I think it is because
I am in Datasheet view, which I need to be in.

I tried placing the following the "Criteria" of the row source query bulider
of the combo box2, [TPSub-Key], the one I want to limit the list of but none
worked
-----Forms![FMainTCC]![FMainSubTD-TE2].Form![TP-Key]
----Forms![FMainTCC]![FMainSubTD-TE2]![TP-Key]
-----Forms![FMainSubTD-TE2]![TP-Key]

I read about and tried subQuery, placing a SQL statement in the Criteria
area of the row source query builder.
In the TP-Key column of the query I place in Criteria area
<=(SELECT [TP-Key] FROM [TP] WHERE [TP-Key]=
Forms![FMainTCC]![FMainSubTD-TE2].Form![TP-Key] )

Nothing seem to work, they all would work I thing if I was not in Datasheet
view, how can this be done in Datasheet view.

Thanks,
Brad

 

[Scott Vincent]

Try using VBA code to set the RowSource property of the second drop down box
based on the selection from the first. Here is an example where I used the
Customer selection to filter the list of orders. You can get the SQL for the
combo box by looking at the RowSource property. I simplified it by removing
all of the table name prefixes just so it is easier to read.

Private Sub cboCustomers_AfterUpdate()

Dim strSQL As String

strSQL = "SELECT OrderID, OrderDate "
strSQL = strSQL & "FROM Orders "
strSQL = strSQL & "WHERE CustomerID='" & Me.cboCustomers & "';"

With Me.cboOrders
.RowSource = strSQL
.Requery
End With
End Sub

 

[Brad]

Scott,
Thanks you very much, you helped a great deal
Brad

Try using VBA code to set the RowSource property of the second drop down box
based on the selection from the first. Here is an example where I used the
Customer selection to filter the list of orders. You can get the SQL for the
combo box by looking at the RowSource property. I simplified it by removing
all of the table name prefixes just so it is easier to read.

Private Sub cboCustomers_AfterUpdate()

Dim strSQL As String

strSQL = "SELECT OrderID, OrderDate "
strSQL = strSQL & "FROM Orders "
strSQL = strSQL & "WHERE CustomerID='" & Me.cboCustomers & "';"

With Me.cboOrders
.RowSource = strSQL
.Requery
End With
End Sub

--
Happy Coding,

Scott

I have a SubForm in "Datsheet view" and have two combo boxes that I want one
to limit the list of the other
The follow are the names and inforamation
---Main Form: [FMainTCC] View: Single Form
---SubForm: [FMainSubTD-TE2] View: Datasheet
---------Combo Box1: [TP-Key]
----------------------TP-Key bound column
---------Combo box2: [TPSub-Key] (Want to limit list of this combo from
[TP-Key]combo)
----------------------------TPSub-Key is bound Column
-----------------------------TP-Key is one of the columns

TP-Key is the field in both combo boxes that I want linked

I have tried everything. I read all the helps limiting the list of one combo
box with another and tried most, but none have worked. I think it is because
I am in Datasheet view, which I need to be in.

I tried placing the following the "Criteria" of the row source query bulider
of the combo box2, [TPSub-Key], the one I want to limit the list of but none
worked
-----Forms![FMainTCC]![FMainSubTD-TE2].Form![TP-Key]
----Forms![FMainTCC]![FMainSubTD-TE2]![TP-Key]
-----Forms![FMainSubTD-TE2]![TP-Key]

I read about and tried subQuery, placing a SQL statement in the Criteria
area of the row source query builder.
In the TP-Key column of the query I place in Criteria area
<=(SELECT [TP-Key] FROM [TP] WHERE [TP-Key]=
Forms![FMainTCC]![FMainSubTD-TE2].Form![TP-Key] )

Nothing seem to work, they all would work I thing if I was not in Datasheet
view, how can this be done in Datasheet view.

Thanks,
Brad