Display value from a different table in an unbound control

[Seiquo]

Good morning Forum,

I am used to create forms with filter controls, based on queries. I recently found this website and was so thrilled to discover that I could apply a filter on a form with VBA, without any query or SQL…
Good news: I successfully adapted this code to my form.
BUT…
In my form - based on Table1 - I have a combo – CmbFilterA - based on a query (based on Table2) with 3 columns. I would like an unbound controls – TxtUnboundA - do display the combobox column(1) value. I tried the only way I know:

Me![TxtUnboundA] = Me![CmbFilterA].Column(1)

on the Activation event, Open Form event, GetFocus Event but it doesn’t seem to work.

Is there a solution or do I have to get back to the query method?
For your information, the form will be used in read-only mode by users. They can apply several criteria in order to get the information they need.


Thank you very much in advance.

[Minty]

Firstly use Me. as it will give you the benefit of intellisense.

If you are stepping through and adding records you will want this in two places: After Update event of the Combo, and the OnCurrent event of the Form.

[Seiquo]

Thank you very much Minty, you helped me partly solve my problem.
I did what you said, replaced "!" with "." and put the code both on the OnCurrent event and AfterUpdate event.
BUT… (I like those!)
It works only for the unbound control that's on the first tab in my form. I have three other unbound controls on the fifth tab, and the whole code I put in both places is as follows:

Me.[TxtUnboundA] = Me.[CmbFilterA].Column(1)  'on tab1
Me.[TxtUnboundB] = Me.[CmbFilterA].Column(3)   'on tab5
Me.[TxtUnboundC] = Me.[CmbFilterA].Column(4)   'on tab5
Me.[TxtUnboundD] = Me.[CmbFilterA].Column(5)   'on tab5

Do you have any idea why it wouldn't work for the other three?

[Minty]

Is this based on a Navigation form by any chance ?

[Seiquo]

What is a Navigation form?

I do have 4 buttons on the form, allowing users to go from a record to the following or previous one, and to the last or first one. Is this what you mean?

[Minty]

No, there is an in built Navigation form wizard that will set up a menu for you to navigate around tabbed forms
If you tabs are on one of those you will have an issue.

If not I think I'd need to see the forms - zip up a copy of your DB and post it here.

[Seiquo]

If I enclose a print screen of the form is it enough? My DB is large, with linked tables and confidential information. It is going to take me a lot of time to make a dummy DB. And don't get me wrong, I am well aware that it would be a lot easier for you but just let's give it a try with print screens and if it leads to nothing, then I will try and find some time today to make the dummy DB.

So here is my main form, in Edit mode (sorry, I work in French). The combo I am talking about is the one with Proprietaire inside:




And the query behind the Proprietaire combo:



The unbound control that works fine is the Independant one, just next to the Adresse label.
The other three that remain blank (though they should show information) are on the Contacts tab. All fields are Short Text.

Does it help?

And just so you know, Bail in French doesn't have the same meaning as in English. It means Lease

And may I add that even if I move the three controls to tab1 (Informations générales), they remain blank too…

[Minty]

On reflection - take away the [ ] from your control names. They normally indicate a field rather than a control.

On your Form in the VBA editor if you have Intellisense switched on, and you type Me. it should bring up valid control names as you type.
Bit of a stab in the dark.

[Seiquo]

So I did what you said and still the same; I had no mistakes in my control names but good news! I took my lunch time to make a dummy DB.

Please don't be too hard on me I have no studies in VBA or Access, and just do it because I love it and because it is very helpful to my colleagues.

Thank you so much.

Edit: by the way, I moved the three reluctant controls to tab 1. You'll recognize them: they are blank.

[Minty]

Well no beating yourself up...

Change the column count to 5. All will be well in the world.

[Seiquo]

Good morning Minty,

I'm so embarrassed…

But thank you so much for your help and your time. Have a great great day and week-end!