Sync two Combo boxes in Datasheet view of a FORM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Scott,
Thanks you very much, you helped a great deal
Brad

Scott Vincent said:
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


Brad said:
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
Click to expand...
Click to expand...
 
Back
Top