Combo Box Dependancy in Subform

[David]

Dawn

Such a long time with no replies!

I have exactly the same problem; the selection in one
combo box used as a filter for the contents of the second.

On the 'About creating a list box, combo box ...' page,
the Access Help File only says "Or you may want to create
a ..box to filter the records in another ...box." without
any clues as to how you do it! Thanks Microsoft!

The MSN Group for Access & SQL mentions Knowledge Base
Article - 289670 which seems to solve the problem, only it
doesn't! After correcting the spelling mistake and the
logical mistake, it doesn't do what it says it will.

The Event Procedure is like:
Me.Boxname2.RowSource = "SELECT Table2Field FROM Table2
WHERE ForeignKey = & Me.BoxName1"
Me.BoxName2 = Me.BoxName2.ItemData(Col# of Table2Field)

When you remove the '&' from '= & Me.BoxName1' you are
prompted to enter a value (i.e. Table1PrimaryKey value)
for Me.BoxName1, which the Event Procedure transfers as
Table2ForeignKey, and when you do this it all works OK.

However, replacing the '&' to stop the prompt for manual
entry, the Table1PrimaryKey is not transferred to
Table2ForeignKey and nothing is displayed in the contents
list of Box2.

I'm sure it's a simple fix, but what is it? I feel sure
my setup of the two combo boxes is OK because manual entry
makes it work OK.

Come one you experts. Help us novices out here!

David

 

[Emilia Maxim]

The MSN Group for Access & SQL mentions Knowledge Base
Article - 289670 which seems to solve the problem, only it
doesn't! After correcting the spelling mistake and the
logical mistake, it doesn't do what it says it will.

The Event Procedure is like:
Me.Boxname2.RowSource = "SELECT Table2Field FROM Table2
WHERE ForeignKey = & Me.BoxName1"
Me.BoxName2 = Me.BoxName2.ItemData(Col# of Table2Field)

When you remove the '&' from '= & Me.BoxName1' you are
prompted to enter a value (i.e. Table1PrimaryKey value)
for Me.BoxName1, which the Event Procedure transfers as
Table2ForeignKey, and when you do this it all works OK.

However, replacing the '&' to stop the prompt for manual
entry, the Table1PrimaryKey is not transferred to
Table2ForeignKey and nothing is displayed in the contents
list of Box2.

David,

what you are doing wrong is to concatenate the control names instead
of their values. the RowSoure string should be put together like this
(assuming, ForeignKey is numeric):

Me!Boxname2.RowSource = "SELECT Table2Field FROM Table2 " _
& "WHERE ForeignKey = " & Me!BoxName1

If ForeignKey is text type, the content of BoxName1 must be enclosed
in quotes (I use single quotes, watch out for them):

Me!Boxname2.RowSource = "SELECT Table2Field FROM Table2 " _
& "WHERE ForeignKey = '" & Me!BoxName1 & "'"

Me.BoxName2 = Me.BoxName2.ItemData(Col# of Table2Field)

I must confess I don't know what you want to achieve with this line.
If it still doesn't work, please post the whole event procedure as it
is (i.e. the whole AfterUpdate code of the first combo).

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de