Access Form where selection of type enables/disables fields to fill in

[astewart]

Greetings, all.

I have a database I'm trying to make more user-friendly. I inherited the
database from somoene else, but it being the beginning of the year I need to
start a new table for 2013 anyway, so now's the time to make the changes.
I'm not much of an Access wizard, but I'm pretty good at following
directions and have figured out quite a bit from googling and reading on
forums.

Previously, we've done all of our data entry in the table. I've built a
simple form that will work, but it would be better if I could make it
apparent which fields the user has to fill in based upon their initial
selections.

Here's some background on my database:
In my primary table, each record is the record on one incident. Every
incident has a unique number, the primary key. Then, each record is a
certain type. We have 13 types of incidents, three of those are the most
common. There are a lot of fields, but they don't all need to be filled in
for every type of incident.

Basically, what'd I'd like to do is, on the form, the user selects the type


of incident from "incident type" - it's a drop down list - and the fields
they need to fill in become accessible OR the fields they don't need to fill
in grey out.

Can anyone point me in the right direction?
Thank so much!

[ssanfu]

Right off the bat, by saying "I need to start a new table for 2013" tells me your structure is not normalized.
By creating a for 2013, you will have to create new queries and reports (you say you don't use forms).

One method is to use VBA and using the combo box after update event, enable/disable or hide/show the controls. You could even change the background colors of the required controls.

[A S MANN]

When the user selects the type of incident from "incident type" - Drop Box
Than at after Update event property in VBA window type the codes
If Me.[incident type].Value = "X" Then
Me.Field1.Enabled = False
me.Field2.Visivle = False
ElseIf
Me.Field1.Enabled = True
me.Field2.Visible = True
Else
End If

Give appopiate names
Reply if you need more help

[ssanfu]

I would choose the visible property OR the enabled property, but not both. If the visible property is used, there will be gaps in the layout. The enabled property will "gray out" the control but leave it displayed on the form.

The first thing to do in the after update event of the combo box, is enable all of the controls. You can run a loop that will set the enabled property to TRUE.
Then, using use the "Select Case" construct, set the fields enabled property to FALSE

Depending whether the bound column of the combo box is a number or text, the code would look something like:

(Bound column = Number)

Select Case me.IncidentType
Case 1
  Me.FieldName.Enabled = FALSE
  Me.AnotherfieldName.Enabled = FALSE
  Me.StillAnothere.Enabled = FALSE

Case 2
  Me.TheFieldName = FALSE
  Me.CountyEnabled = FALSE
  Me.ThisFieldName.Enabled = FALSE

Case 3
.
.
.

Case 13

End Select

(Bound column = Text)

Select Case me.IncidentType
Case "Incident 1"
  Me.FieldName.Enabled = FALSE
  Me.AnotherfieldName.Enabled = FALSE
  Me.StillAnothere.Enabled = FALSE

Case "Hit and Run", "Property Damage"
  Me.TheFieldName = FALSE
  Me.CountyEnabled = FALSE
  Me.ThisFieldName.Enabled = FALSE

Case "DUI"
.
.
.

Case "Bodily Injury"
.
.
End Select

[astewart]

Right off the bat, by saying "I need to start a new table for 2013" tells me your structure is not normalized.
By creating a for 2013, you will have to create new queries and reports (you say you don't use forms).

Ha, I would definitely consider us not "normalized". We don't have a lot of records per year - less than a thousand, most likely half that. And really, at this point we don't do much with the data so recreating my few queries and reports won't be much of a hardship. I wanted to take advantage of the beginning of the year to make some structural changes that will help us out, and build something that will work for the next few years of data.

Thanks so much for the info, I think it's a good starting point for me. I'll play around with it on Monday and see if I can get it to do what I'd like. I'll post back if I have questions.

ETA: I just tried it out and it worked like a dream. I'll work through the entire project Monday and get back if I need to. Thanks again!!

[ssanfu]

Excellent

[astewart]

Ok, this worked beautifully! Now, when a user enters the incident type, the fields they don't need to fill in are disabled. When they change the incident type from one to another, the new selected incident type fields disable/enable. So helpful.

One question - when users hit the "add record" button at the end of the fields to save the current and move on to the next, the fields stay disabled until the incident type is selected. This isn't really an issue because they have to choose incident type first, but is there an easy way to clear the enable/disable when we move to the next record? More curious than anything else.

Thanks in advance. I'll also be posting another question related to my form, but I'll start a new topic.

[ssanfu]

Sure.. You can set them all to enabled or disabled - I would set them all disabled to force an incident type selection.

You have an "Add Record" button? What is the code for the button?
Add this code to the button:

'other code
'go to new record code here
'disable fields
   Me.FieldName.Enabled = FALSE   
   Me.AnotherfieldName.Enabled = FALSE   
   Me.StillAnothere.Enabled = FALSE
'end sub