continuous forms/check boxes

Hi Everyone,

This is my first time posting a question, and I'm a beginner, so here goes.....

    I have a bound form, that displays rows of information from a LOG table.
    I would like to add a check box, so that selected rows will be written to a WORK table.

    The checkbox is unbound and I do not want to have a checkbox field in the  LOG table.

    By the way:
              a) After I added the checkbox to the row, in design mode,
                 when I clicked on view mode, the checkbox appears next to each row, but when I       click on it, 1) a check mark does not appear,
               2) to experiment: set events: got focus, click, after update, with a debug.print, but none seem to fire......

    From researching on the web and an access vba book,
                      I learned that  the conditional formatting feature is available for text and combo  boxes, only, when using continous forms.

    Does anyone have a technique, to have a checkbox in a continuous form work?


Who is Participating?
heer2351Connect With a Mentor Commented:
You can use a temporary table that will hold the value of the check box, use an outer join to link it to the log table.
mytfeinAuthor Commented:
Hi heer2351,

Some more background:
a)   On employee profile form, user has the ability to archive an employee. Archives are written to a  T_000_STAFF_INFO_LOG  

b)   Created archive history form based on T_000_STAFF_INFO_LOG.
      User can view archive history by a specific date or <all> via a combo box.
      So the T_000_STAFF_INFO_LOG is joined to STAFF1 to get employee's name.

c) the archive history form is opened as popup/modal from the employee profile form,
    the archive history form is opened using arguments (specific date or <all>)
    sent by the employee profile form)

d)   Before employee's chart is sent to an offsite storage location, want to paste a label to the outside of the chart that explain when and why the employee was archived.
A checkbox would allow user to mark employees, for labels......

Based on your suggestions:
1) created work table with check box called:    T_000_STAFF_INFO_LOG_for_label
2) went to record source property of form, and added LEFT join to sql statement (that already had an
INNER join of 2 tables). When to the sql, behind the query design and pasted below:

             STAFF1.[Last Name],
             STAFF1.[First Name],
                  INNER JOIN STAFF1      
                                              ON T_000_STAFF_INFO_LOG.Ssn = STAFF1.Ssn)
                  LEFT JOIN T_000_STAFF_INFO_LOG_for_label
                                              ON STAFF1.Ssn = T_000_STAFF_INFO_LOG_for_label.Ssn;

3) before closing the query design grid, executed the query, the check box shows up

4) however, when I close the query, and go to view mode of the form, nothing happens when
I click the check box......any thoughts about this problem? If you want to see the mdb, I would be glad to send it......

mytfeinAuthor Commented:
from mytfein....

also, forgot to mention, that assigned a control source to the checkbox: LabelPrintCheckBox
from T_000_STAFF_INFO_LOG_for_label
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Yep send me the .mdb so I can examine it; address is in my profile.
heer2351Connect With a Mentor Commented:
I received your database and checked it, you have to change three things to get it working:

1) Set AllowEdits on the FRM_ehs_history_by_date to Yes
2) Set the index of InfoAutoNum to No Duplicates in table T_000_STAFF_INFO_LOG
3) Remove the fields Ssn, InfoDate, InfoTime and InfoLogCode from table T_000_STAFF_INFO_LOG_for_label
    I would recommend to make field ForeignlAutoNum the primary key in this table

This will make the form functional.

So why is all this required?

It is obvious that you will never be able to change the checkbox if the AllowEdits property of the form is set to no. The other requirements are not so obvious.
If you leave the index on duplicates allowed access will treat table T_000_STAFF_INFO_LOG as the many table instead of the one table. It can therefore not decide which value to place in table T_000_STAFF_INFO_LOG_for_label when a record is created.
The primary key fields of table T_000_STAFF_INFO_LOG_for_label are not required since you link on ForeignlAutoNum, if you leave them in you have to create relationships for them as well otherwise access does not know how to populate these fields when a record is created. Since you do not need, just remove them.

mytfeinAuthor Commented:
Hi heer2351!

Thank You !!! It works!!!  Where do I click to award you the points?

Some questions.....please advise if you want a new question opened for them:

1) How does the checkbox form, REALLY create a record in the (T_000_STAFF_INFO_LOG_for_label) table, when clicking on a checkbox?

     a) how come it works without having to code for the on-click event, something like;
            1) DoCmd.GoToRecord , , acNewRec          -or-
            2) an sql "INSERT"
         does it work implicitly because of the join, and that the checkbox is bound to a table field?

     b) the (T_000_STAFF_INFO_LOG_for_label) right now only has 2 fields:
         foreignLAutoNum   and LabelPrintCheckBox

         if would add a 3rd field, that is not defined on the bound form (such as user name retrieved from windows),  how to get it into the  (T_000_STAFF_INFO_LOG_for_label) table?
or would this field need to be defined but visible=false?

2) Taking this exercise some steps further:
     a) want the  (T_000_STAFF_INFO_LOG_for_label) table to really become a temporary table
that is created, when user opens the form: W_000_TABLE & strUserName
    b) got code from Microsoft's web site  (

        to create table, create primary key, create relationship
        the create table, create primary key code work nicely, listed code at the end of this post.....

        problem with create relationship:  getting an error msg that the primary table name is in use

         "run time error 3211 - the database engine could not lock table
         T_000_STAFF_INFO_LOG because it is already in use by another or person or process"

         I shut down, what I could, and got this message again, when trying to execute this statement:     db.Relations.Append rel

    c) want to delete the table, when user exits the screen (clicks close button)
        how is this delete coded? does the index and relationship need to be deleted as well?

    d) by the way using DAO, because read on microsoft web site:

         Note   The Microsoft Jet database engine doesn't support the use of CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet database engine databases. Use the DAO Create methods instead.
           which if faster:  DDL statements or DAO create methods?

    d) if delete temp table, upon close, will the mdb still be bloated, in other words would
       a compact and repair need to be run, if so have you coded a compact and repair, using vba?

    e) what the difference when creating an index between:
             idx.Primary = True


             idx.Unique = True

    f) if 2a-2f are resolved, could i leave the hardcoded record source of the
       FRM_ehs_history_by_date form (the form that has the checkboxes) to do the LEFT join
       to the (T_000_STAFF_INFO_LOG_for_label) table, but in VBA change the record source to
       LEFT join to the dynamically created temporary table?
Thanks so much for your time and help!!!
    code for 2a below:

     Sub DAOCreateTable(strTableName As String)

   Dim db      As DAO.Database
   Dim tbl     As DAO.TableDef
   Dim fields  As DAO.Field

   ' Open the database
   Set db = CurrentDb

   ' Create a new TableDef object.
   Set tbl = db.CreateTableDef(strTableName)

   With tbl
      ' Create fields and append them to the new TableDef object.
      ' This must be done before appending the TableDef object to
      ' the TableDefs collection of the Database.
      .fields.Append .CreateField("ForeignlAutoNum", dbLong)
      .fields.Append .CreateField("LabelPrintCheckBox", dbBoolean)
      .fields("ForeignlAutoNum").Required = False
      .fields("LabelPrintCheckBox").Required = False
   End With

   ' Add the new table to the database.
   db.TableDefs.Append tbl


End Sub

code for 2b below:

Sub DAOCreateIndex(strTableName)

   Dim db As DAO.Database
   Dim tbl As DAO.TableDef
   Dim idx As DAO.Index

   ' Open the database
   Set db = CurrentDb

   Set tbl = db.TableDefs(strTableName)

   ' Create Index object append Field object to the Index object.
   Set idx = tbl.CreateIndex("ForeignKeyIndex")
   idx.Primary = True
   idx.fields.Append idx.CreateField("ForeignLAutoNum")

   ' Append the Index object to the
   ' Indexes collection of the TableDef.
   tbl.Indexes.Append idx


End Sub

code for 2c below:

Sub DAOCreateRelationship()

   Dim db  As DAO.Database
   Dim rel As DAO.Relation
   Dim fld As DAO.Field

   ' Open the database
   Set db = CurrentDb

   ' if want to delete an existing relationship
   'db.Relations.Delete "LogToCheckBoxForLabel"

   ' Create the relation
   Set rel = db.CreateRelation()
   rel.Name = "LogToCheckBoxForLabel"
   rel.Table = "T_000_STAFF_INFO_LOG"
   rel.ForeignTable = "W_000_TABLE"

   ' Create the field the tables are related on
   Set fld = rel.CreateField("InfoAutoNum")

   ' Set ForeignName property of the field to the name of
   ' the corresponding field in the primary table
   fld.ForeignName = "ForeignlAutoNum"

   rel.fields.Append fld

   ' Append the relation to the collection
   db.Relations.Append rel

End Sub

All Courses

From novice to tech pro — start learning today.