Trying to record the NAME of a checkbox control if its value is yes. (1 Viewer)

[chefboyrz]

I am creating a simple Access database that allows users to record the time they spend servicing an application, or set of applications.

The form (ProTrak_1) is esssentially a set of check boxes for users to select the apps they work on. What I am trying to do is get the database to look (in AfterUpdate on the form) at each check box and if the value is True then put the NAME of the text box into a text field in a subform (AppsServiced), and therefore record the Name in another table. The subform field names are A1, A2, A3, ...

So, if I've checked the box called Illustrator, the value of A1 in the subform will become "Illustrator", A2 might become "Photoshop", etc.

The Form is set to call AppSelect([Form]) in the AfterUpdate event.

Here's the code at this point. I am not getting any errors but the data is not recording. I am not primarily a programmer, so any help would be appreciated.

Function AppSelect(AppsServiced As Form)
'On Error GoTo Err_Handler

Dim C As Control, A As Integer, xName As String, AppField As Control

A = 0
AppsServiced!AppField = Chr(3)
AppsServiced!AppField = "A" & A
For Each C In MyForm.Controls

Select Case C.ControlType
Case acCheckBox
If C.Value = True Then
A = A + 1
AppsServiced!AppField(Chr(3)) = C.Name
'Form_ProTrak_1.AppsServiced.Controls.Item(AppField.Value) = C.Name
End If
End Select
Next C
TryNextC:
Exit Function

 

[MarkK]

Welcome to the forum:
- It seems to me that the application someone worked on is data, and would most easily be manipulated in a table. Your proposal to name a control after a data point demands that if the data should ever change, and it will, demands that you need redesign your system, at least to the degree that you need to rename controls. This then destroys your historical data.
- If I was in your shoes I'd create a table called tApplication and get the user to make a selection from a combobox or listbox based on that table. Then you can so easily limit that list for certain users, or add new items, or remove or hide items.
- To name contols after your data is to 'hard-code' your options which seems to defeat the purpose of a database. In this design, your information is inextricably embedded in the document itself, and offers no significant advantage over a paper system.

 

[The_Doc_Man]

Not to mention that it appears to not be properly normalized.

 

[chefboyrz]

Thanks for your feedback.
If I use table-driven ListBoxes (I can see where that's better) I would need to allow multiple selections. How do you put each item selected into it's own cell in the record, so they can be queried/reported on individually? I am sure this is not a big deal, but I haven't found a clear solution.

Thanks again.

 

[MarkK]

Take stock of the discrete things you need to keep track of...
1) User
2) Application
3) Time (that a User spends on an Application)

So you need three tables.

[FONT="Verdana"][B]tUser[/B]
UserID (PK)
UserName

[B]tApplication[/B]
ApplicationID (PK)
AppName

[B]tUserApplicationHours[/B]
UserAppHoursID (PK)
UserID (FK)
ApplicationID (FK)
Date
Hours[/FONT]

This is a textbook many-to-many relationship. One user might work on many Applications and one Application might be worked on by many users, and the data that binds them is the date and number of hours spent.

You will rarely change records in tUser, and rarely change records in tApplication. Most of your traffic is going to be adding records to tUserApplicationHours.

And see how tUserApplicationHours offers very simple summaries of time spent by a unique UserID, or time spend on a single App, or time spent by a single user on a particular app last month?

 

[chefboyrz]

Thanks for that... You are absolutely right, and this is essentially how the database is set up. I know that I am on the right track as far as the logic goes, and I know how to create the queries that will produce what i need to report on. My specific issue, is that there are many applications, and the user needs to select them and they could work on more than one at a time. I haven't found out figured out code that will take the multiple items selected in a list box and put them into the database as text, each in an individual field. In other words, I am trying to record Application1, Application2, Application3... probably not more than 9 or 10 for each record. (The total # of apps available to choose from is roughly 50, categorized into 5 tables).

 

[MarkK]

"multiple items selected in a list box and put them into the database as text, each in an individual field."
This makes your data almost impossible to retrieve, which defeats the purpose of storing it.

 

[chefboyrz]

Argh......

 

[MarkK]

I think you need to step back from the programming problem and make some real world decisions about what you are trying to achieve. What is the purpose of your database? What is the problem you need to solve? What information do you want it to produce?
Then work backwards from those goals to establish what information to record and what structure it needs to have.

And in a database, and for most reasonable purposes, the smallest managable single unit of information is the record, not the field. As a result you never want to group multiple items of the same type in a single record. One record represents one thing and its attributes.