2nd Try, previous answer didn't work

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I have two tables: tblPhase and tblSubPhase.
First table has two fields PhaseID and PhaseType
Second table has two fields SubphaseID and SubphaseType

I would like a form that has a combo box with the PhaseType dropdown listed
for every record, but don't know how to get that to work without having a
form with 30 records to "page" through with the Nav Buttons.

How do you do this?

(I eventually want to pick one from the list and get another box to show the
Subphase types - one to many relationship already established)

I was told to follow this: http://www.mvps.org/access/forms/frm0028.htm but
it didn't work.

Rob
 
create an unbound form (no record source). add a combobox control with it's
record source as the field phasetype from tblphase. Create a subform by
using the form wizard, and as the record source select both tables. This
will create a form with a subform when chosen in the wizard. Now open the
first form (the unbound one) in design mode and select subform from the
tools menu. Make sure the wizard is on and link the subform with the
combobox and the phasetype field. That should do it.
HTH
 
For the Combo Box, I don't see a record source option, only rowsource. Do I
put tblPhase in the Row Source? If so, where do I put the field name,
because it isn't available in control source.

Thanks for helping,
Rob
 
Rob,
you need to add a foreign key from tblPhase into your SubPhase table. see
below.

SubPhaseID (PK - <autonumber>)
SubPhaseType
PhaseID (FK - long integer) *** add this

then you'll be able to use the example on the MVP website.

HTH,
Jeff
 
I figured it out.
Thanks for the help...here is what I did....

Unbound Form, two Combo boxes.
first combo box row source table/query, column count 2, column widths 0"; 1"
(to hide the id field)
second combo box row source table/query, column count 3, column widths
0";0";1" (to hide two id fields)
Code for second combo box:

Private Sub cboPhase_afterupdate()
Dim strSQL As String
strSQL = "SELECT tblSubPhase.PhaseID, tblSubPhase.SubPhaseID,
tblSubPhase.SubPhaseName " & _
"From tblSubPhase " & _
"WHERE tblSubPhase.PhaseID =[Forms]![frmPhaseSearch].[cboPhase]"
Me!CboSubphase.RowSourceType = "Table/Query"
Me!CboSubphase.RowSource = strSQL

End Sub

Now I can drill down into the tables and get to the dirt I want.

Thanks,
Rob
 
Back
Top