Help MS Access - basic n00bie thingy

[Jupitus]

Guys,

I'm good with Excel but shite with Access, so I need your help.

I have a database which contains descriptions of client problems enountered. I would like to add to each record 3 levels of problem category. Given the large number of entries, I would like to use a form with drop down lists of the possible choice for each category to make inputting the info easier.

How do I do this please?

Example:

Category 1 possible values:
Client hardware
Client software

Category 2:
Unit failed
Slow performance
Crashed

Category 3:
Data loss
Data delayed
Data incorrect

So, for the following description:

Client application process stalled causing the system to crash. Data was lost for a period of 15 minutes

I would input

Client Application - Crashed - Data loss

all from drop down lists.

A slightly more advanced option would be to have the choices in the drop down lists for cat 2 and 3 be context sensitive to whatever was in category 1, ie if application then only display choices relevant at sub levels, but that's just a nice to have.

Help ObiWan Ke-Freddies! :wub:

 

[Bob007]

Not 100% sure what you after. But as i understand it you want "combo box" selections for different options within a form. Few ways to do this. This is the way i would go for the example you've given.

Note, I did this in officer 2007.

Make a table. Call it combo. In design view make the first three rows (first column) Category 1, Category 2, Category 3 going down. In the next box change the field input to "Text" if it hasn't automaticaly. Be sure to remove the Primary Key from row 1, column 1 (looks like a key). Just Rclick it and hit the primary Key option.

Switch to Dataview and add Client Software, Client Hardware In the table below Category 1, Add the other options below there title in the same table. Save the table. These are now your options for your combo boxes. Editing these will change the options for the combo boxes we add next.

Without knowing the version of access, next bit might seem vague at first.

Next open a new form in design view. From tools list we need to add one combo box for each column. Select the Combo Box tool and drag the size of box out you want on your design sheet. An options box will pop up asking if you want to enter data or use data from a table, in this case we will use from a table. So select from table and click next. On next screen we see a list of avail tables. Select the Combo table and click next. Then we have are list of options we entered into the table in design view in step one. For the first combo box select category 1 and click the single arrow to move it accross. Click next. Next two boxes are for order and width. Play about as you need and click next on each then finish.

Switch from design view to form view. Test your combo box. If it does what you wanted add the next two combo boxes to your form and its done for the first request.

You can remove the "Title" boxes for the combo box from the form to help in placement for the combo boxes within a form. Just select the title box next to the combo box in design view and hit delete.

I'll look into conditional selections based on choices later and see what i can remember/find out. Or with luck someone else will post and save me

Hope this helps and don't confuse you more.

 

[Bob007]

10 min timer. bla.

Anyway, crappy picture showing some of what i did. Still with primary key in table, remove this or access goes all stupid

Also you can spend a lot more time on layout to make it look better

 

[Jupitus]

Thanks Bob - I'll give this a go today and let you know how I get along!:wub:

 

[Bob007]

Next part was selection based on previous selection. Try to cover that for you now.

note, this may turn into wall of text

Easiest way to do this is with multi tables. Taking your example. I'll expand a little and hopefully we'll create something of great wonder

Ok first lets set up 6 tables. name them "Table1", "Table2" ....."Table6"
No tables have primary key, remove it from each as you go.

Table 1. Category 1 in design view. Text. In data view Add Hardware and Software in column 1 going down.
Set up the remaining tables as shown in this image.

Now we need to build a form.

Open a new form in design view and drop a combo box on it. data comes from table and select table 1.
Select the properies of this combo box and rename it to Combo1.

Add another combo box. this time cancel the pop up and just leave it blank, select properties for this and rename it to Combo2.
Do one more combo box and cancel this 1 to and rename it Combo3.

Still in design view. Right click Combo1 and and select "Build Event" and select "Code Builder" from pop up.

Delete everything in the code box and paste the following.

Private Sub Combo1_AfterUpdate()
   Select Case Combo1.Value
      Case "Software"
         Combo2.RowSource = "Table2"
      Case "Hardware"
         Combo2.RowSource = "Table3"
   End Select
End Sub

Private Sub Combo2_AfterUpdate()
   Select Case Combo2.Value
      Case "Slowed down"
         Combo3.RowSource = "Table4"
      Case "Crashed"
         Combo3.RowSource = "Table5"
      Case "Unit Failed"
         Combo3.RowSource = "Table6"
   End Select
End Sub

This is all the code needed for Combo1 and Combo2. Close the code windows and back to our form in design view.

On the properties of Combo1, check the event tab for "After Update" should say Event Procedure, if not, click the down arrow and select it. Switch to Combo2 combo box. Select the properties for that and on events, After Update, change that 1 too to Event Procedure.

Switch to form view and test.

Breaking the code down a bit.

Private Sub Combo1_AfterUpdate(), This tells it that after you select an option do the following.

Select Case Combo1.Value, this part tells it what to look for.

Case "Software"
Combo2.RowSource = "Table2", this part says, if you select "Software", use table2 for combo2

Case "Hardware"
Combo2.RowSource = "Table3", Same as above but table3 for "Hardware"

End Select
End Sub, ends this event.

This code for combo2 to combo3 is same but just has extra table option for the extra feild option in table2.

Can send you the DB files if you want to look at mine. Or you can poke me on MSN or drag me IRC if you have any questions. Just PM here and i'll get to back you.

Edit, this can also be done with a single table and an SQL query. But gets a bit more codey.

 

[Jupitus]

Thanks alot Bob - got that working!

The example was much simplified in terms of the number of options each time, but I can expand what you have done for the task in hand.... muchos gracias, Signor!!!