Add Value to Table from checkbox

[SharonInGa]

tblEmployeeProfile as EP
tblApplicationList as AL
tblEmployeeAssignedApplications as EAA


I have a list of 125 different applications that need to be associated with
employees. I created a Main Form that includes the Employee Profile. The
Admin User needs to pull a specific employee and check one or many of the
applications that apply to that employee.

How do I take the yes/no value from each checkbox and insert it into the
EmployeeAssignedApplication table that shows the applications for each
employee?

 

[Klatuu]

Is this a bound form or an unbound form?
If it is a bound form, then the data source for the check boxes should be
the associated fields in the table.
If it is an unbound form, you will have to write some code to assign the
values of the check boxes to the associated fields.

 

[SharonInGa]

Can you either give me an example of code that asisgns values to the check
box or can you direct me to a website where I can get code?

 

[Klatuu]

Lets assume all your Application Fields are named App001 thru App125 (This
will make life easier) and that all your check boxes are named chkApp001 thru
ChkApp125. Otherwise, the coding would be long and tedious. Please note
this has not been fully tested!

Sub umum()
Dim rst As Recordset
Dim fld As Field
Dim intX As Integer
Dim strCtl as String

Set rst = CurrentDb.OpenRecordset("MyTable")
Do While Not rst.EOF
With rst
For Each fld In rst.Fields
If Left(fld.Name,3) = "App" Then
strCtl = "Me.chkApp" & format(intX,"000")
fld = Eval(strCtl)
strX = strX + 1
End If
Next
End With
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

 

[Rob Oldfield]

Sorry to jump in but that is bad. A little bit bad when Sharon wants to
count how many applications a particular user has, overcomplicated when she
wants to add another application, and downright abysmal when she wants to
report on the names of those assigned apps.

It's a many to many relationship. The data structure you're suggesting is
breaking normalisation rules and will lead to Sharon having to throw her
computer out of a window.

Sharon... you could always try posting the code that Mr Mandeno and I have
asked for in the other version of this thread.

 

[SharonInGa]

Grahmn & Rob,
Here is the code that Previews a multi selected list in a report(credit to
Allen J Browne). How can I get this code to "Add the selections
(multi-select or checkbox) to a table"?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptApplicationUsers"

With Me.lstApplications
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[AppSvcID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "ApplicationServiceNames: " & Left$(strDescrip,
lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub