Results 1 to 6 of 6
  1. #1
    myates is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    Brunei
    Posts
    3

    Using checkboxes on a continuous form to export data to a query

    Hello,

    I'm sure this is a rather simply syntax issue but it is driving me crazy.

    I have a continuous form that is bound to a table (class information). I have created a temporary yes/no field in that table. What I want to be able to do is select multiple rows from the continuous form and run a query on those fields. What I have done is used an expr: [Forms]![myFormName]![Checkbox] and set the criteria to -1, then set the criteria for the primary key to the Form Textbox that.

    This seems to work but limits the values in the query to the last box checked on the continuous form.



    All I want to do is allow people to select multiple rows (with a checkbox) then when a button is pressed a select query is run and only the rows with a checked box are returned. I will then use the results of the query to export the data to Excel and then Email it. I know how to do the last two parts, but the initial query is not working as I intend it to. Thank you.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That expression doesn't make sense to me.

    Post the query SQL statement.
    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
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    when using something like [Forms]![myFormName]![Checkbox] it will only reference the current record.

    If your checkbox is bound to the table field you refer to here 'I have created a temporary yes/no field in that table' then your query needs to reference the table -

    SELECT *
    FROM myTable
    WHERE checkboxfield=true

  4. #4
    myates is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    Brunei
    Posts
    3
    Hello June7 and Ajax,
    Thank you for responding.

    The SQL statement is:
    SELECT tblStudents.INEISID, tblStudents.StudentName, tblStudents.StudentGender, tblStudents.StudentReligion, tblStudents.StudentRace, tblStudents.StudentBirthCertNum, tblStudents.StudentICNum, tblStudents.StudentICColour, tblStudents.StudentDOB, tblStudents.StudentCitizenship, tblStudents.StudentPOB, tblStudents.StudentRAS, tblStudents.StudentFatherName, tblStudents.StudentMotherName, tblStudents.StudentGuardianName, tblStudents.StudentParentJob, tblStudents.StudentParentCN, tblStudents.StudentAddress, tblStudents.StudentSelectTmp, [Forms]![frmEditStudents]![chkTMP] AS Expr1
    FROM tblStudents
    WHERE (((tblStudents.INEISID)=[forms]![frmEditStudents]![TxtStudentID]) AND (([Forms]![frmEditStudents]![chkTMP])=-1));


    I'm likely going about this the wrong way, as I do not want to save this information and only put the Yes/No field in the table to be able to select it. Basically I have a query that lists student names and the class they belong to. The primary key is the StudentID field (Unique identifier for the student). I have a filter on the form that allows the users to filter the students by class. Now I just want to be able to select any number of students and use the StudentID's selected to run two queries (only on the selected Student ID's). Those two queries will then be exported to excel and emailed to another school (for example if the student transferred mid year, or the students are going from Middle to High School).

    I have a background (about 10 years ago) in rDB's but i'm just a lowly teacher now trying to figure this thing out . Any help would be appreciated.

    Thank you.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming your temporary yesno field is in tblStudents and called chkTMP then modify your where clause to

    WHERE (((tblStudents.INEISID)=[forms]![frmEditStudents]![TxtStudentID]) AND (([chkTMP])=-1));

  6. #6
    myates is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Location
    Brunei
    Posts
    3
    Thank you. Ended up putting a bit more thought into it and found just saving the checkbox data in the table, running a query on that table, then updating all checkboxes to false works.

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

Similar Threads

  1. Replies: 6
    Last Post: 09-12-2016, 06:52 AM
  2. Replies: 2
    Last Post: 08-03-2014, 11:00 AM
  3. Checkboxes in a form, how to save the data
    By alex423 in forum Forms
    Replies: 2
    Last Post: 08-12-2013, 12:28 PM
  4. Replies: 13
    Last Post: 07-03-2013, 03:08 PM
  5. Replies: 3
    Last Post: 11-07-2012, 08:44 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