Cascading Combo Disaster

[Skeletor]

Hi. I hope somebody can help with this.

I am trying to modify the Northwind db. I have changed the Customer Details
form so that when I type in a Suburb name in the combo box "Suburb", the
"State" and "Post Code" List boxes fill with the correct data.

I have created a new table called "Pcode", that contains a Unique ID,
Suburb, State, Post Code columns.

The "Suburb" combo box selects the correct name as you type, but regardless
of what code I put in the other two combo boxes; "State" and "Postcode", I
cannot get it to populate properly.

I have tried every suggestion that is listed on this forum, but nothing
seems to work. I am a very basic Access user and therefore fairly unfamiliar
with the steps one needs to do to make this happen.

Could someone please outline what needs to be done from start to finish (or
write the code so I can copy it), what, if any table relationships are
required and what is needed to store that info in the "Customers table.

Any help would be greatly appreciated.
Mike

 

[Skeletor]

Thankyou for your help. I have the 'State' combo box populating perfectly,
but the same cannot be said for the 'Postcode' combo box. It is not poulating
at all.

1. Should the AfterUpdate Event; me.PostCode = me.comboxname.column(3)
appear in the AfterUpdate of the 'State' combo box or be added to the
AfterUpdate event of the 'Suburb' combo box?
Thankyou again.
Mike

 

[Skeletor]

I have the 'postcode' combo box now showing an actual postcode--- just not
the right one. It appears to be selecting a postcode from somewhere else in
the list.

 

[Dale Fye]

What is the SQL in the RowSource for each of your combo boxes?

If it references the value in the other combos, then you will need to
requery the combo before setting its value. As an example, I would normally
have the combo box for the state setup with a rowsource like:

SELECT DISTINCT State
FROM PCode
WHERE [Suburb] = Forms!myForm.cboSuburb
OR Forms!myForm.cboSuburb IS NULL

This would give you a list of all the states in your Pcode table, if nothing
was selected in cboSuburb. However if you select a value in cboSuburb, and
want that value to filter the items available in cboState, then in the
AfterUpdate event of cboSuburb, you need to requery cboState:

Private Sub cboSuburb_AfterUpdate

me.cboState.requery
me.cboState = me.cboSuburb.column(2)
me.cboPostCode.Requery
me.cboPostCode = me.cboSuburb.column(3)

End Sub

If you are not actually cascading these combo boxes, then you don't need to
do that.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Skeletor]

It is working - Sort of.

The 'Postcode' combo is showing an incorrect postcode for the 'Suburb' and
'State' combinations, yet it appears to be placing the correct postcode in
the table. Weird!!!

To get around this issue, I have allowed the 'State' combo box to display
both the State and Postcode colums from the Pcode table.

Thankyou for all your help.

Did Dale’s suggestion worked?

In the second combo “Stateâ€, does the SQL string have a criteria refering to
the first combo “Suburb�

In the third combo “PostCode†does the SQL string have a criteria refering to
the second combo “State�

It appears the “UniqueID†is not index in the table. A best guess assumption
is made here to give you a workable solution.

I have the 'postcode' combo box now showing an actual postcode--- just not
the right one. It appears to be selecting a postcode from somewhere else in
the list.

Thankyou for your help. I have the 'State' combo box populating perfectly,
but the same cannot be said for the 'Postcode' combo box. It is not poulating

[quoted text clipped - 42 lines]

Any help would be greatly appreciated.
Mike