Access 2003 Form Help

[tpreece01]

Hello..

I'm building a database and want to know the best way to build one particular form.

The form is bound to a single table with the following user inputs:

[user id] [date] [error type] [error description]

Error type is a list box. Error description is only required if one particular error type is selected. It's mandatory for the one error, but must not be filled in for the others.

Can I design the form so that error description only appears if the particular error type is selected?

This could mean that [error description] is invisible until the error type is picked; that it's visible but greyed out until the error type is picked, or simply that it's only on a tab stop if the right error type is picked.

Whatever works easiest, the actual design is open, but I want to avoid the user having to select the error type and then having to press a button to open a subform if I can.

Any suggestions?

[beso]

You need to use VBA. I will try to introduce the essential elements bit by bit in my instructions, making it a case of "do it like this but this is better" but I think you will learn more this way then if I simply writing out the whole code as it should be from the start.

Firstly set the initial state of the DescriptionListBox to Disabled so that it won't be accessible until an ErrorType has been selected.

Open the Event tab in the Properties of the [ErrorType] Listbox. Click the right hand end of the After Update event. Choose Code Builder and the VBA editor will open with an empty a Sub procedure.

Enter a statement to this effect :

If [Error Type] = "Error Name That Needs Description" Then
[Error Description].Enabled = True
Else: [Error Description].Enabled = False
End If

For the tab stop change you would add the lines:

[Error Description].TabStop = True
etc

Putting it all together more concisely using the With grouping:

If [Error Type] = "Error That Needs Description" Then
With [Error Description]
.Enabled = True
.TabStop = True
Else:
.Enabled = False
.TabStop = False
End With
End If

--------------
Continued

[beso]

Continued:

But now you have to switch the Error Description listbox off again when you move to the next record. This is done using the On Current event of the Form. Drop down the Selection Type in the Properties of your Form and choose Form. Click in the On Current Event to start another VBA procedure.

You could use the same code in this event. Or you could simply enter the name of the first procedure as the command in the second.

However it is much better to name the original procedure something else and call it from the After Update and On Current events. Say you rename the After Update procedure to SetDescriptionBox. Just put SetDescriptionBox as the only line in the other procedures.

As the description is mandatory for the particular ErrorType you need to add more VBA code disable the record navigation buttons on your form until the description has been entered. Add the appropriate lines to the code above. Asd they refer to a different control they will be outside the original With grouping so you need to reorganise this into two separate With groups. If you contemplate the structure for a bit you will get the picture you need.

Then you must add an After Update event to the DescriptionListBox to turn the Navibation buttons back on.

Note that you cannot disable a control that is currently in focus. If you need an After Update event to defocus its own control you must first move the focus to another like this:

ControlName.SetFocus

If you need to refer to a control on another form you must add the path to it before the control name. But that is another subject of its own.

It really is worth the effort to learn VBA. It is immensely powerful. Once you get up the initial learning curve it becomes quite easy. If you are still struggling post back here and I will endeavour to provide further assistance.

Otherwise visit:

[beso]

http://www.access-programmers.co.uk

[vascop]

You could do this without using VBA, by using macros instead.

[tpreece01]

beso: thanks for that excellent answer.
vascop: thanks too. I'm going to stick to vba though and get myself back into the swing of things.

I've not had to use Access since about '98, so am very rusty but answers like this one and your previous one vascop are helping!