Solved

continuous forms/check boxes

Posted on 2004-03-24
6
662 Views
Last Modified: 2008-02-01
Hi Everyone,

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

Background:
    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?


Thanks!


0
Comment
Question by:mytfein
  • 3
  • 3
6 Comments
 
LVL 23

Accepted Solution

by:
heer2351 earned 125 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.
0
 

Author Comment

by:mytfein
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:

SELECT T_000_STAFF_INFO_LOG.Ssn,
            T_000_STAFF_INFO_LOG.InfoDate,
            T_000_STAFF_INFO_LOG.InfoTime,
            T_000_STAFF_INFO_LOG.InfoLogCode,
            T_000_STAFF_INFO_LOG.InfoComment,
            T_000_STAFF_INFO_LOG.InfoUser,
            T_000_STAFF_INFO_LOG.InfoArchiveBox,
             STAFF1.[Last Name],
             STAFF1.[First Name],
            T_000_STAFF_INFO_LOG_for_label.LabelPrintCheckBox
FROM (T_000_STAFF_INFO_LOG
                  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......

Thanks!
0
 

Author Comment

by:mytfein
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
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 23

Expert Comment

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

Assisted Solution

by:heer2351
heer2351 earned 125 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.

0
 

Author Comment

by:mytfein
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

                   and

             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

   db.Close

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

   db.Close

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


0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now