Show a field based on 3 possible options

Y

YNWA

Registered User.
Local time
Today, 18:23
Joined
Jun 2, 2009
Messages
905
Hi,

I need a text field to show based on the selection in a combo box. Now I can do this if there is only 1 selection for the field to show on, but here there is 3 possible options that if 1 of them is selected I need a field to appear for the user to type into.

Code I have now for 1 option is:

Code: 
Private Sub cboSchool_AfterUpdate()
 If Me.cboSchool = "2" Then
  Me.txtSchool.Visible = True
 Else
  Me.txtSchool.Visible = False
  Me.txtSchool.Value = Null
End If
End Sub
 
Private Sub Form_Current()
If Me.cboSchool = "2" Then
  Me.txtSchool.Visible = True
 Else
  Me.txtSchool.Visible = False
End If

End Sub

But what I need is the txtSchool field to show if the user selected 2, 3 or 5 from the combo box.

I did try If Me.cboSchool = "2" Or "3" Or "5" Then but to no avail.

Is this possible?

Thanks
Will
 
You could do it:

If Me.cboSchool = "2" Or Me.cboSchool = "3" Or Me.cboSchool = "5" then
Me.txtSchool.Visible = True
Else
Me.txtSchool.Visible = False
End if

HTH

leddy
 
Would this go in After update or form current?

Would their not be any issues with other records having the boxers displayed when no options have been selected yet to to the lack of current and update features?
 
Is it a bound form? I don't use them much tbh, but if it was an unbound form, I would do it in the beforeupdate of the combobox
 
Hi, yes its a bound form. The control source of the menu and the new viable field need to be stored in patient records.
 
Hm, I'm not 100% then, but I *think* the txtSchool value would still be bound anyway, regardless of whether it is visible or not - therefore, the beforeupdate event would work fine.

As I said though, I don't really use bound forms much so hopefully someone will jump in here :)

Have you tested it?
 
Any other VBA wizz's got any input into this?
 
If Me.cboSchool = "2" Then
This leads me to believe you want to check for a number.... numbers do not use ""
If Me.cboSchool = 2 Then

Right now you want this with 2,3 and 5, but in the future 7 and 11 will need to be added.
Instead of hard coding this you should store this in your table as a yes/no field then it is simple to add this information into a hidden column of the combobox and use that value to determain the visibility on the form.
 
namliam said:
If Me.cboSchool = "2" Then
This leads me to believe you want to check for a number.... numbers do not use ""
If Me.cboSchool = 2 Then

Right now you want this with 2,3 and 5, but in the future 7 and 11 will need to be added.
Instead of hard coding this you should store this in your table as a yes/no field then it is simple to add this information into a hidden column of the combobox and use that value to determain the visibility on the form.
Click to expand...

My combo box has 2 fields (ID and fieldname). The bound colum is the ID column. The column widths are 0cm;2cm, so the ID field is not visable.

I am telling the form to look for ID "2".

Why would 7 and 11 need to be added?

My box has 6 options.

If the user selects School (ID 2), College (ID 3) or Employed (ID 5), then the box needs to appear for the user to wrtie in.
 
I would use a select case statement. Is the ID field a text field or a number field? If number then this will work in the after update event of your combo box...

Code: 
Private Sub cboSchool_AfterUpdate()

    Select Case cboSchool
        Case 2, 3, 5
            Me.txtSchool.Visible = True
        Case Else
            Me.txtSchool.Visible = False
            Me.txtSchool.Value = Null
    End Select

End Sub

If the ID field is a text field then you need to surround the number with double quotes.
Code: 
Private Sub cboSchool_AfterUpdate()

    Select Case cboSchool
        Case "2", "3", "5"
            Me.txtSchool.Visible = True
        Case Else
            Me.txtSchool.Visible = False
            Me.txtSchool.Value = Null
    End Select

End Sub
 
YNWA said:
My combo box has 2 fields (ID and fieldname). The bound colum is the ID column. The column widths are 0cm;2cm, so the ID field is not visable.

I am telling the form to look for ID "2".

Why would 7 and 11 need to be added?

My box has 6 options.

If the user selects School (ID 2), College (ID 3) or Employed (ID 5), then the box needs to appear for the user to wrtie in.
Click to expand...

Yes why indeed, well things change, maybe 6 isnt in the "to be selected" group and is added...
Maybe a 7th option is added to the list... Self Employed or
Things that may seem 'constant' like this ... disturbingly often turn out to be a variable after all.

My (strong) advice is to not hardcode this, instead make it a setup thing, add a column to the table and the combo (0cm offcourse)
Then in the code its easy:
Me.txtSchool.Visible = Me.cboSchool.columns(2)
 
ghudson said:
I would use a select case statement. Is the ID field a text field or a number field? If number then this will work in the after update event of your combo box...

Code: 
Private Sub cboSchool_AfterUpdate()
 
    Select Case cboSchool
        Case 2, 3, 5
            Me.txtSchool.Visible = True
        Case Else
            Me.txtSchool.Visible = False
            Me.txtSchool.Value = Null
    End Select
 
End Sub

If the ID field is a text field then you need to surround the number with double quotes.
Code: 
Private Sub cboSchool_AfterUpdate()
 
    Select Case cboSchool
        Case "2", "3", "5"
            Me.txtSchool.Visible = True
        Case Else
            Me.txtSchool.Visible = False
            Me.txtSchool.Value = Null
    End Select
 
End Sub
Click to expand...

Hi, its a autonumber field. The combo box is linked to a ref table with different options.

What do I do for the Form_Current() option? How do I set that up?
 
namliam said:
Yes why indeed, well things change, maybe 6 isnt in the "to be selected" group and is added...
Maybe a 7th option is added to the list... Self Employed or
Things that may seem 'constant' like this ... disturbingly often turn out to be a variable after all.

My (strong) advice is to not hardcode this, instead make it a setup thing, add a column to the table and the combo (0cm offcourse)
Then in the code its easy:
Me.txtSchool.Visible = Me.cboSchool.columns(2)
Click to expand...

I get you know, yes if I add a new option Self Employed then I can simply go into the code and enter "7" as well can I not?

Have I read you wrong?
 
Yes you can go in and change code.

The thing though, that needs someone with some technicaly knowledge and skills.

Instead if you make this table driven and supply a maintenance screen. Anyone can go into the maintenance screen and add/delete/change this type of thing.
 
namliam said:
Yes you can go in and change code.

The thing though, that needs someone with some technicaly knowledge and skills.

Instead if you make this table driven and supply a maintenance screen. Anyone can go into the maintenance screen and add/delete/change this type of thing.
Click to expand...

Ok cheers. As its only a small set up, I would be responsible for the maintainence of it as well. We do not like handing over "admin" tasks to nurses.

If I did this as you suggest, is it just a case of a new field in my "main table", new column on combo that is already there (set width to 0cm).

Would I need to add a field to my reference table that the combo box currently looks at? It consists of ID and type fields. Do I need another field for this?

Then in the after update of the combo put these lines in...

Me.txtSchool.Visible = Me.cboSchool.columns(2)
Me.txtSchool.Visible = Me.cboSchool.columns(3)
Me.txtSchool.Visible = Me.cboSchool.columns(5)

Or are those (numbers) referring to columns in the cboSchool rather than rows or IDs?

Cheers

What is the exact code?

Wou
 
YNWA said:
Ok cheers. As its only a small set up, I would be responsible for the maintainence of it as well. We do not like handing over "admin" tasks to nurses.

If I did this as you suggest, is it just a case of a new field in my "main table", new column on combo that is already there (set width to 0cm).
Click to expand...
Yes, and even if you are your own admin it makes your own work a little easier :P
YNWA said:
Would I need to add a field to my reference table that the combo box currently looks at? It consists of ID and type fields. Do I need another field for this?
Click to expand...
Yes add a field to your table and to the combobox

YNWA said:
Then in the after update of the combo put these lines in...

Me.txtSchool.Visible = Me.cboSchool.columns(2)
Me.txtSchool.Visible = Me.cboSchool.columns(3)
Me.txtSchool.Visible = Me.cboSchool.columns(5)

Or are those (numbers) referring to columns in the cboSchool rather than rows or IDs?

Cheers

What is the exact code?

Wou
Click to expand...

Well the columns refers to what column you want to fetch data from ...
Columns(0) is the first column (id) and is probably hidden
Columns(1) is the name column and is visible
Columns(2) would be this new column and hidden
In this case to me it sounds like Columns(2), unless there are more columns....
Its easily testable though and make sure to remember it is zero based, the first column is not 1 but 0.

So you should only need:
Me.txtSchool.Visible = Me.cboSchool.columns(2)

Good luck :)
 
namliam said:
Yes, and even if you are your own admin it makes your own work a little easier :P

Yes add a field to your table and to the combobox



Well the columns refers to what column you want to fetch data from ...
Columns(0) is the first column (id) and is probably hidden
Columns(1) is the name column and is visible
Columns(2) would be this new column and hidden
In this case to me it sounds like Columns(2), unless there are more columns....
Its easily testable though and make sure to remember it is zero based, the first column is not 1 but 0.

So you should only need:
Me.txtSchool.Visible = Me.cboSchool.columns(2)

Good luck :)
Click to expand...

Cheers, yes only the 3 columns (with new field) so it will be (2).

Just running this through my head now, how will the textbox know when to appear when I select an option?

My reference table will have 5 or 6 rows, so when I need the text field to show if 1 of a possible 3 outcomes of the reference table is selected?

I'm lost at this point?
 
The same way you would do it with your hardcoded IF thing... using the after update event I think....
 
Do I need to do a Form Current() code as well?
 
To be honest I am not sure on that front,
 
You must log in or register to reply here.

Similar threads

J
Search for possible declarations
Replies
9
Views
350
sonic8
S
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
600
CJ_London
C
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
363
Mike Krailo
Mike Krailo
D
Solved Is there a better option for VBA to change background color
Replies
5
Views
239
donkey9972
D
A
Filter Records
Replies
1
Views
233
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom