Public Function for controls

[BeataG]

Hello,

I have a form on which I put a few controls. I have one ComboBox and two TextBoxes. ComboBox's name is Dep, TexBox1 - Abr, TextBox2 - Fac.

Is it possible to automatically fill up TextBox1 and TextBox2 with the appropriate values depending on the value selected by a user from ComboBox?
I though about Public Function with a Select Case Statement, but I don't know how to refer to these controls.

Example:

ComboBox values: Man; Hlt; Soc; Lin
Textbox1: if Man -> M; if Hlt -> N; if Soc -> P; if Lin -> E


Thank you in advance for any help.

 

[darbid]

HI BeataG,

In my opinion your thoughts are on the right track, but I have one suggestion if your values are reasonably sure not to change.

You build your combobox to have 2 columns eg
Man -> M
Hlt -> N
Soc -> P
Lin -> E

Then in your column widths you make the second column 0 in width. Make your bound column 2.

Now the user will only see the 1st column. So when they choose Man then Me.myDep.Value = M (this is because the bound column is 2)

If you like this idea I use a table for all my values for all my comboboxes.

Your fields would be something like

Name -- Abr -- Cbo_name
Man -- M -- Dep
Hlt -- N -- Dep
Soc -- P -- Dep
Lin -- E -- Dep

Then you use an SQL query in your form open for this combobox using a WHERE Cbo_name = "Dep"

This will mean that in this table you could add further combobox values and just change the where.

Now also if the Dep values changes you do not have to change any code but you can change the table which means that you could allows users to change table values.

If you do want to hard code this by using a public function then you would need to do this in a module.

In this public function it would be strange to fill the text box from it. If you do want to then you use Forms![frm_Your_Form_Name]![Textbox1]. But this would limit your function to only this combobox/form and it would not really be a function.

A function returns something so what you would normally do is in the afterupdate event of the combobox you would use something like

Me.Textbox1.Value = YourFunction(Me.cbo_Dep.Value)

This means that you would have a public function somewhere called YourFunction which accepts one string variable and returns a string variable

Public Function YourFunction(cbo_value as string) as string

put select here



end function

 

[BeataG]

Thank you very much for your help, darbid.
The combobox is fine for my needs. Thanks again.
Regards