Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / April 2005

Tip: Looking for answers? Try searching our database.

Add Value to Table from checkbox

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SharonInGa - 20 Apr 2005 17:13 GMT
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 - 20 Apr 2005 21:20 GMT
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.

> tblEmployeeProfile    as EP
> tblApplicationList  as AL
[quoted text clipped - 8 lines]
> EmployeeAssignedApplication table that shows the applications for each
> employee?
SharonInGa - 20 Apr 2005 21:46 GMT
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?

> 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
[quoted text clipped - 14 lines]
> > EmployeeAssignedApplication table that shows the applications for each
> > employee?
Klatuu - 20 Apr 2005 22:26 GMT
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

> 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?
[quoted text clipped - 17 lines]
> > > EmployeeAssignedApplication table that shows the applications for each
> > > employee?
Rob Oldfield - 20 Apr 2005 22:46 GMT
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.

> 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
[quoted text clipped - 46 lines]
> > > > EmployeeAssignedApplication table that shows the applications for each
> > > > employee?
SharonInGa - 21 Apr 2005 21:31 GMT
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

> 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
[quoted text clipped - 67 lines]
> each
> > > > > employee?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.