Combo box connections in a sub form

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

Guest

I have a Main Switchboard form which points to a sub form.
Within that sub form, I'm placing combo boxes. The first combo box will
have the user choose a plan name (tblPlan). After that choice, the user will
then go to the next combo box & choose the related product to that plan
(tblProduct). How can I code this so that the first choice drives the second
choice?
Thank you
 
Use code in the change AfterUpdate event of the first combo box to set the
record source of the second combo box. Here is an example:

Private Sub cboAuthors_AfterUpdate()
Dim strSQL As String

If Nz(Me.cboAuthors, 0) <> 0 Then
strSQL = "Select BookID, BookTitle "
strSQL = strSQL & "from Books "
strSQL = strSQL & "where AuthorID=" & Me.cboBooks

With Me.cboBooks
.RowSource = strSQL
.Requery
End With
End If

End Sub
 
Thanks Scott.

I think I'm doing something wrong. Well I know I am. It's not working. I
think it's my tables. It worked for a nano-second, & my celeberation was
short lived.

cmbPlan is linked to tblPlan which includes PlanId and PlanName.

cmbProduct is linked to tblProduct which from this table, I have only pulled
the ProductId and ProductMnemonic.

The PlanId and the ProductId are the unique keys for each table.

I wrote the code as such. Please help me figure what I have done wrong.

Private Sub cmbPlan_AfterUpdate()
Dim strSQL As String

If Nz(Me.cmbPlan, 0) <> 0 Then
strSQL = "Select PlanId,PlanName"
strSQL = strSQL & "fromProductMnemonic"
strSQL = strSQL & "where PlanId=" & Me.cmbProduct

With Me.cmbProduct
.RowSource = strSQL
.Requery
End With
End If

End Sub
 
It looks pretty good.
A coople things to remember:
- In design view, the cmbProduct shoud have no recordsource. That will
leave it blank until you select a plan
- When building the select statement make sure each row has a space before
the quote. If you look at it in debug mode you will see why. It puts things
together and Access can't figure out what to do.
- First of all, the query looks like it is selecting plan information to set
the values in the products box. I assume you are trying to set products
because that is the box you assign the recordsource to at the bottom and the
code is in the plan afterupdate event.
- You also missed a space after the word from
- You said the table name was tblProduct, but you used ProductMnemonic in
the code
- Based on your query, you were selecting plans based on the product ID in
cmbProduct

I wrot the code the way I think it should look based on the what I think you
are trying to do. I am assuming you have a PlanID in the products table.

Private Sub cmbPlan_AfterUpdate()
Dim strSQL As String

If Nz(Me.cmbPlan, 0) <> 0 Then
strSQL = "Select ProductId ,ProductName "
strSQL = strSQL & "from tblProduct "
strSQL = strSQL & "where PlanId=" & Me.cmbPlan

With Me.cmbProduct
.RowSource = strSQL
.Requery
End With
End If

End Sub

Feel free to send me an email directly if you still need assistance. Keep
in mind, my email address has been altered. I think you can figure it out.
 
Back
Top