dropdown control does not show correct column

Hi
On my app I have a series of 4 cascaded dropdown controls. I.e. I have, each related to a table

[region]
[region].[sector]
[region].[sector].[section]
[region].[sector].[section].[base]

the idea is to start selecting the  [region] then, within it the [sector], then within it, the [section], and finally, the [base] as per the below screenshot:

Screen-Shot-2017-11-19-at-08.40.31.png
the controls are linked to the tables via a SELECT and the linked column is correct:

Screen-Shot-2017-11-19-at-08.45.30.png

so, starting with [region], on the [before update] event I do filter the subtables related to the "sub" controls, using their "rowsource" with the code:

Private Sub frmfld_regiaoOrgao_BeforeUpdate(Cancel As Integer)

    Dim strSectorRowSource As String
    Dim strSeccaoRowSource As String
    Dim strBaseRowSource As String
   
    strSectorRowSource = "SELECT zCT_Regioes.codRegiao, zCT_Sectores.codigoSector, zCT_Sectores.nomeSector "
    strSectorRowSource = strSectorRowSource & "FROM zCT_Regioes INNER JOIN zCT_Sectores ON zCT_Regioes.codRegiao = zCT_Sectores.codigoRegiao "
    strSectorRowSource = strSectorRowSource & "WHERE (((zCT_Regioes.codRegiao)= '" & Me.frmfld_regiaoOrgao & "'));"
    Me.frmfld_sectorOrgao.RowSource = strSectorRowSource
    Me.frmfld_sectorOrgao.Requery
   
    strSeccaoRowSource = " SELECT zCT_Seccoes.refRegiao, zCT_Seccoes.refSector, zCT_Seccoes.refSeccao, zCT_Seccoes.nomeSeccao "
    strSeccaoRowSource = strSeccaoRowSource & "FROM zCT_Seccoes "
    strSeccaoRowSource = strSeccaoRowSource & "WHERE (((zCT_Seccoes.refRegiao)= '" & Me.frmfld_regiaoOrgao & "'));"
    Me.frmfld_seccaoOrgao.RowSource = strSeccaoRowSource
    Me.frmfld_seccaoOrgao.Requery
   
    strBaseRowSource = "SELECT zCT_bases.refRegiao, zCT_bases.RefSector, zCT_bases.RefSeccao, zCT_bases.RefBase, zCT_bases.codBase, zCT_bases.nomeBase "
    strBaseRowSource = strBaseRowSource & "FROM zCT_bases "
    strBaseRowSource = strBaseRowSource & "WHERE (((zCT_bases.refRegiao)= '" & Me.frmfld_regiaoOrgao & "'));"
    Me.frmfld_baseOrgao.RowSource = strBaseRowSource
    Me.frmfld_baseOrgao.Requery
   
End Sub


Everything looks alright but and the dropdown controls do show the correct set of records for each "sub" table.

However, no matter what, the linked column shoen in the control is always the leftmost one [region] and not the correct column.

can anyone help?
João serras-pereiraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
I think you may just need to set the columns widths to zero (0) in the dropdown for those columns you do not want to show.

Right now you have visible columns widths 0.5;0.5;5. If you only want to show the 3rd column, this should read "0;0;5.0"

colwidth.jpg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I believe Neil has you straight, but you don't need all those Requery lines, like this one:

Me.frmfld_baseOrgao.Requery

Setting the RowSource triggers a Requery of the combo automatically.
1
João serras-pereiraAuthor Commented:
THANKS!!!!! It works now!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.