[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


continuous forms/check boxes

Posted on 2004-03-24
Medium Priority
Last Modified: 2008-02-01
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?


Question by:mytfein
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 23

Accepted Solution

heer2351 earned 500 total points
ID: 10672524
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.

Author Comment

ID: 10677773
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......


Author Comment

ID: 10677909
from mytfein....

also, forgot to mention, that assigned a control source to the checkbox: LabelPrintCheckBox
from T_000_STAFF_INFO_LOG_for_label
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 23

Expert Comment

ID: 10681279
Yep send me the .mdb so I can examine it; address is in my profile.
LVL 23

Assisted Solution

heer2351 earned 500 total points
ID: 10708402
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.


Author Comment

ID: 10718532
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  (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate_topic6.asp)

        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


Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question