Results 1 to 15 of 15
  1. #1
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    Query that is based on a checkbox in a form being ticked.


    I have an append query that transfers some records to another table if a checkbox named [Check_ACTIONED] is ticked on a form [frm_TEST]. The form is based on a query showing all records where the ACTIONED field is unticked. The user will tick a number of the ACTIONED checkboxes then run the query by clicking a button. However I can’t get the criteria under the ACTIONED field in my append query to only show results where Check_ACTIONED is ticked on the form. Can anyone help with the syntax on the criteria line of a query that says “where this field on a form is ticked”?

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    This sql will copy ALL records from [some table] to [some other table] that match the following condition.
    Code:
    INSERT INTO [some table]
    SELECT * FROM [some other table]
    WHERE [some other table].[ACTIONED] = True;
    *Note: this doesn't care about your form. if the above isn't sufficient we'll need to know more details about your queries, tables, form, and work flow.

  3. #3
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    kd2017, Thanks that looks like it might be what I need however not sure where I would put SQL code. I have some very basic Knowledge of VBA and think I am OK with Queries but not a clue with SQL but happy to give it a go with a little help

    of VBA

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Is [Check_ACTIONED] a field in your table? Is it a bound field on your form?
    What kind of form are you using? A continuous form?


    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    moke123, ACTIONED is the field name in the table, yes it is bound to the Check_ACTIONED control on the form and the form is continuous. So the user would tick a number of the checkboxes then tick a button to trigger the append query to run. but I cant get the append query to only show results of ticked checkboxes on the form. Thanks

  6. #6
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Could be because the changes have not been saved.

    If this is a multi user database you may also run into conflicts.

    Why the transfer to another table?

    Can you post a stripped down copy with just the relevant objects?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  7. #7
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Click image for larger version. 

Name:	Access query.JPG 
Views:	16 
Size:	34.9 KB 
ID:	46058I am trying to get this query to only show where the checkbox is ticked on the form.
    If I put true it returns all the records, if I put false it returns none. Thanks

  8. #8
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Use the table value not the form
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware that "Name" and "Date" are reserved words in Access and should NOT be used for object names.. Additionally, "Date" is a built in function.
    "Name" and "Date" are very poor field names because they are not descriptive: "Name" of what?? "Date" of what??

  10. #10
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Thanks but I think that's my problem, the form is based on a query but the query only shows records where ACTIONED = False therefore if I tick ACTIONED on the form then base the query on the query the form is based on, I get no results because as fare as the query is concerned ACTIONED = True so not there. Hope this makes sense but wat I really want is just the records ticked on the form. I am trying to think of a work round but struggling.

  11. #11
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    @Steve - glad I refreshed my browser. was just about to post the same thing.

    Still wondering why you want to transfer to another table. Very often it is sufficient just to flag a record, such as "Inactive" , "Closed", or "Actioned", rather than move it to another table.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Surely the form value should be the criteria for the actioned field ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    The Field names of NAME & DATE was just a very quick example so I could shear a basic screen shot of the problem, its not like that in the actual DB. The reason for moving to a different table is: The reason for the DB is to create a CSV for upload into an accounting system, the ACTIONED records are current months records I want to add to the CSV. After the CSV is created and uploaded, I will clear the table and start again for the following month. If I just created the CSV file based on all actioned from the original table, it would include the previous months records as well.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, well, that info about the CSV is more enlightening. I would look into kd2017's example (Post #2) .... maybe instead of having to select the records, use a date range for the month to select all the months records.
    I'll step out of this thread.....

    Good luck on your project.

  15. #15
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    If I just created the CSV file based on all actioned from the original table, it would include the previous months records as well.
    You would run an update query to set them all back to 0 before selecting them.

    Since you have a date to go on then kd and steve's method is the way to go. Open a recordset with the date field between a start date and end date.

    https://www.w3schools.com/sql/sql_between.asp
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Auto Date when checkbox ticked
    By 2410Davie in forum Access
    Replies: 12
    Last Post: 02-22-2016, 01:52 AM
  2. Insert comment if checkbox is ticked
    By Nokoff in forum Access
    Replies: 28
    Last Post: 07-16-2015, 05:12 AM
  3. Replies: 4
    Last Post: 06-24-2013, 03:36 AM
  4. If Checkbox is ticked, Data Must be entered
    By DTK0902 in forum Access
    Replies: 8
    Last Post: 12-01-2012, 04:32 PM
  5. Open report if checkbox is ticked
    By Patience in forum Reports
    Replies: 3
    Last Post: 06-23-2010, 08:34 AM

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