Results 1 to 14 of 14
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Using Checkbox to append Table

    Hi
    I am trying to use a checkbox to append a field in a table. Actually 2 fields, depending on which or both check boxes are used.

    Form = frmNotUsed
    Append Query = qryAppendUnits

    I think I have to used the AfterUpdate to store somewhere the value of the check boxes. Then when I select Submit, then it should run the append query.

    Can someone steer me in the right direction? Attached is the test db and import excel.




    Test.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Not really understanding what you want to do. You want to import data from Excel file? The Excel file has only 1 column. Exactly what needs to be imported to where? You say 'append a field' but do you really mean 'update a field'?

    If you want to allow arbitrary selection of records for import, will need to save the Y/N value into table so that value can be used as filter criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am also confused about what you are trying to do.

    Looking at your tables/code/queries, I can't follow the process.
    Part of the problem is that you refer to 2 check boxes on a form. Check box values are either FALSE/0 or TRUE/-1.
    The two fields, Ex1 & Ex2 in the 2 tables are text type fields, not Y/N or numeric types.
    You have a query that has criteria of "Yes" (a text YES, not a Boolean YES). The query will never return records because a text "Yes" will never equal a text "1".




    Can you describe what you are trying to do? No Access/computer jargon.....

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    This is what I have so far: I have a table with 2 lists of units. I want to import an excel into a new table, then compare what units that have not been designated in the original table. A form will pop up if there are units not addressed. (got this far) Next you choose which exercise the unit needs to be in by checking the box. It can one or the other or both. Once you submit the information it adds the unit in the appropriate list.

    I intend to add the unit lists (tblUnitsbyEx) by using qryAppendUnits.

    Ex1 & Ex2 Queries identify in the imported table which exercise the unit belongs to.

    qryEx1 & qryEx2 are the output

    qryUnits extracts a complete list of units identified in the imported table.


    My logic of what I am doing.

    Open the database, a pop up form opens automatically. You select the import button.

    First old tbldata gets deleted
    The new tableData gets inported

    3 fields are added
    Ex1 -Short text
    Ex2 - Short text
    EXID -Autonumber

    Then the update queries Ex1 and Ex2 populate tblData EX1 and/or Ex2 if a unit name from the tblByUnits list matches with a 1.

    qryNotused looks at tblData Ex1 and Ex2. one or both need to be filled.

    If not then it filters out what unit is not identified.

    The frmnotused pops up and the user selects by checking the box for what exercise to add that unit to tblUnitsbyEx in the right column

    Then Ex1 and ex2 queries should run again to check that all the units have been identified.

    If it is complete, then spit out the 2 excel into the same folder as the original excel.

    Note this db is minimized from the data intended, but can easily be adapted.

    Thanks

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    First step is to bind the checkboxes to yes/no fields - change the Ex1 and Ex2 fields or change the checkboxes to comboboxes.

    Then write code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7
    The Ex1, Ex2 fields in tblData are not for the check boxes to update. I am using the Update query EX1 and Ex2 for that. I intend to use the chekboxes check5 and check7 to update the tblUnitsbyEx with the Unit name identified on the form frmNotUsed (which is derived from qryNotUsed).

    I did change tblsData to YesNo so it displays a -1 or 0

    I change qryNotUsed to:
    Code:
    SELECT DISTINCT tblData.Unit, tblData.EX1, tblData.EX2, tblData.EXIDFROM tblData
    WHERE (((IIf([Ex1]=0 And [EX2]=0,"Not"))="Not"));
    I think the my issue is the binding. If I bind the forms check boxes to the ones in tbldata, I cannot check them, data entry=yes or not. I gather you can't bind through a query. If I try to use it direct from tblData, I cannot use a checkbox.
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    An UNBOUND control will just show the same value for EVERY record on the form (unless the ControlSource is a dynamic calc). If you want to be able to selectively indicate which records to update, then checkboxes need to be bound to fields.

    tblUnitsByEx makes no sense to me. By what logic are Unit2 and Unit10 assigned to record 2? Why is Unit9 alone on record 1?

    Yes, forms can have queries as RecordSource and controls bound to those fields. The query must be updatable (editable).

    Yes, a table can display a checkbox. Set the Lookup DisplayControl to Checkbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7,
    Record22 indicates that unit is playing in both exercises. The actual excel I am importing has much more data and fields to it. That same row needs to be exported to both excel spreadsheets. I already knew about displaying check boxes in a table. I'll look into making the query editable.

    Correction, I know I can change the yes/no into check boxes, when I open the table manually. how do you do it in code?

    DoCmd.RunSQL "ALTER TABLE (Table Name) ADD COLUMN [(Field Name)] YESNO;" allows the -1/0 but not check boxes.

    I do NOT want to update tbleData with the check boxes on frmNotUsed. I only want to append tblUnitsbyEx dependent on what was checked on the form.


    Thanks

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Example of code to modify field properties:

    CurrentDb.TableDefs("tablename").Fields("fieldname ").Properties("DisplayControl") = acCheckBox

    CurrentDb.TableDefs("tablename").Fields("fieldname ").DefaultValue = ""

    CurrentDb.TableDefs("tablename").Fields("fieldname ").AllowZeroLength = False


    Okay, you don't want the checkboxes bound to tblData Ex1 and Ex2 fields. Doesn't alter my first statement in post 7.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7,
    I figured another way,, plus not to use update, vice Append to add new data to a field in a table.

    I am also using a temp table to store data from the form and using that to append the original table.

    Now I am confusing myself, I have the following append queries using the same source field to field 1 or field 2 depending on the check boxes.

    Code:
    INSERT INTO tblData ( Ex1 )SELECT Unit.Unit
    FROM Unit
    WHERE (((Unit.Unit)=IIf([Unit].[Check1]=True,[Unit].[Unit])));
    Code:
    INSERT INTO tblData ( Ex2 )SELECT Unit.Unit
    FROM Unit
    WHERE (((Unit.Unit)=IIf([Unit].[Check2]=True,[Unit].[Unit])));
    How can I combine these? I want the unit to be on the same line if both check boxes are true.


    Thanks

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I am rather confused as well. Maybe:

    INSERT INTO tblData (Ex1, Ex2) SELECT IIf(Unit.Check1, Unit.Unit, Null) AS E1, IIf(Unit.Check2, Unit.Unit, Null) As E2) FROM Unit WHERE Check1 OR Check2;


    Really should give fields names different form tables. Duplicating names is more confusion.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    June7,
    I figured some out. Next step is to not put nulls in tblUnitsbyEx and not ensure that LostUnit input is not repeated in tblUnitsbyEx.

    I attempted to de-duplicate the field names to lessen the confusion.

    Suggestions?

    Attachment 31244Attachment 31244Data.zip
    Last edited by Thompyt; 11-12-2017 at 10:05 PM.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sorry, don't understand that statement. Think I've done all I can do to help.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks June7, I think I have it figured out.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. Replies: 11
    Last Post: 03-16-2012, 11:46 AM
  3. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  4. Replies: 1
    Last Post: 12-01-2010, 11:01 AM
  5. Append Checkbox Data
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 06-29-2010, 04:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums