Conditional Fields dependent upon Drop-Down Selection

[dexsquab]

I'm trying to find out how to restrict what can be placed in a text field based upon the element selected from a drop-down field.

I have a database with a drop-down box (a list of organisations), which gathers it's values via lookup from another table (this is working fine). I then have several subsequent fields which either [must be/may be/must not be] filled out, depending upon the drop down option chosen.

What I'd like to see is the additional fields greyed out for some drop-down options, open but ignorable for most, and mandatory for the rest.

Your help would be most appreciated by myself and by the clients for whom these forms are being designed.

 

[M8KWR]

You could use a select case statement, but it depends on how many drop-downs you have, and how many control on the form you need to re-assign value to, such as if it must be filled in.

Or you could create a table that stores all the value for each field, then you would have to create a dlookup procedure for if this drop-down is changed to alter all the fields on the form. If you did it this way it would not matter if you added extra companies, the code would not change, but you would need to update the table instead.

I hope this helps. If you need any assistance with the DLookup etc please let me know.

 

[dexsquab]

Thanks for offering your help.

At the moment I have 13 responses available under Organisation. Of those, six require a mandatory related address (et.al.) be entered, and two require the following fields be left blank.

After I've completed this it's being handed out to several offices who will update (add to) their Organisations table themselves; so I need to make it as robust as possible.

 

[M8KWR]

So in theory you would have only 3 possibilities of the way the field would have to be filled in.

If this is the case, then assign each company a number 1-3, this then would represent which set of field would need to be filled in. What you could do is to automatically assign a default number to this if a new record is created so that all field can not be filled in, this will make the user assign a value to this field.

This way the end user does not have to update any tables, it is done at the development stage. The only thing with any option is at a later date the fields may have to be changed due to some reason or other, but that is life.

 

[dexsquab]

Thanks for your assistance, however I still have had no joy.

I'll copy a code snippet, hopefully you can show me where I'm going wrong.

---------
Private Sub Referral_From_AfterUpdate()

If Me.Referral_From = "Self" Then
Me.[Name_of_Referee].Enabled = False
End If

End Sub
------------

This code element appears in the After Update field on the Referral_From field. Similar code appears in Before Update and Change. It does nothing... no errors, no disabling of related fields. I've tried square brackets around the Referral_From element, which doesnt seem to help. Set up as above it seems as though the code doesnt exist.

If I change "Self" to 4 (the ordinal value from the referrer table), it then crashes with a run time error whenever that option is selected. The error is as follows:

Run-time error '2465':

Microsoft Access can't find the field '|' referred to in your expression.

So, is it bad variable naming, incorrect syntax, or what? Any help would be wonderful.

 

[M8KWR]

The code that you gave me worked for me, but you would need to change it, and also do not forget to put the same code into the Form_Current.

What i would do instead of having the same code twice, is to create a public sub, put the code in there, so you only have to type in once, and you may forget to update the 2 parts and then wonder why it is not working.

Use the code below, I hope it explain itself.

Private Sub Form_Current()

UpdateField

End Sub

Private Sub Referral_From_AfterUpdate()

UpdateField

End Sub

Public Sub UpdateField()

If Me.Referral_From = "Self" Then
Me.[Name_of_Referee].Enabled = False
Else
Me.[Name_of_Referee].Enabled = True
End If

End Sub

Let me know if this works or not, I have tested it, but i have only used the 2 fields that you have said, bu ti know this way would work on more then one field etc

What version of access are you using...

If you have more then one option in the Referral_From then i would suggest using a select case. It would be something like below.

Private Sub Form_Current()

UpdateField

End Sub

Private Sub Referral_From_AfterUpdate()

UpdateField

End Sub

Public Sub UpdateField()

Select Case Referral_From

Case "Self"

    Me.[Name_of_Referee].Enabled = False


Case Else

    Me.[Name_of_Referee].Enabled = True

End Select

End Sub

I hope this helps and i have not confussed you. If you need anymore help me say.

 

[dexsquab]

Wonderful, this looks good. As soon as I clear a few other jobs off my desk, I'll give this a try. My thanks for the amount of effort you put into assisting.

 

[dexsquab]

Results: tried both your code segments, both yielding the same 2465 error... *shoots self*

Copied your code verbatim as it seemed correct, and it reported the error as soon as the form was loaded. When entering debug mode, it took me to this line:

Me.[Name_of_Referee].Enabled = True

Removing the line allows the form to load, but it doesnt disable related field.

I'm using Access 2003, and I'm ready to use evil voodoo magic on it. I am at a complete loss as to what I am missing.

 

[M8KWR]

I use Access 97. You may want to down-grade...

As you can imagine I can not see what you are seeing, and can only guess.

Change Me.[Name_of_Referee].Enabled = True to

Name_of_Referee.Enabled = True

It works actually the same, i do not know if Access 2003 does not like the me. function, but i am assuming it should.

Try typing me. in the code area, and scroll down to find "Name of Referee", I just want to make sure you have not spelt something wrong, not saying you have, but just want to check. After that press ".", it should then bring you another list, and make sure you can select "Enabled". " = " then select "True".

If you can do all that then I can not see why it is not working. If you can go into the form make sure that the control you are trying to alter is not the first control to have to cursor in it, if so change the tab order.

 

[dexsquab]

No luck. Dropping the Me caused no changes. Also, I checked the spelling etc. as suggested and it was all ok. Nevertheless, I tried the auto-complete option, built the lines as suggested and still had no luck. The control in question is about 14th on the form, it's definitely not the first in line.