Continuous Form, Checkbox, Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Continuous Form, Checkbox, Query

Brian DeMott

51 days ago

I have a simple Continuous Form based on my tblAccountNumber (ie: 10000, 10005, 21000, etc)
I have another table tblAccountDetail with the field AccountNumber and several transaction records (there's no ForeignKey to tblAccountNumber because this is an ODBC connection and I am makeshifting a query).

I want to add a CheckBox to the Continuous Form. User checks the box for the AccountNumbers that they want and the Query will return the results for those AccountNumbers.

I am unsure what to do here. At first I left the CheckBox unbound but then when I checked the box it would check everything. I then bounded it to the AccountNumber field in the tblAccountNumber but that caused the value to change to 0 and -1.

I am curious what I am doing wrong. I hope I don't need to add another field to tblAccountNumber for the checkbox?

Thanks!

	Alex Lewis @Reply 51 days ago
If you could post some screenshots of your query, we'll see what we're working with. Check boxes usually store values as 0 or -1 (0 means FALSE, -1 means TRUE in Access).

	Brian DeMott OP @Reply 50 days ago

	Brian DeMott OP @Reply 50 days ago
Alex I have attached an image of the UNION SELECT query

Raymond Spornhauer @Reply

50 days ago

Brian

Using an ODBC connection does not mean there should not be Foreign Keys.

Additionally, we would need to know if you're using a Pass-Thru Query, or if you're using Linked Tables.

My first thoughts are:
1. Save your UNION SELECT Query as it's own query.
2. Add the Check boxes to your form
3. You'll need a button for the User to click in the Form Header or Footer
4. Use a Recordset Clone to loop thru all the records using      VBA to build your new SQL.
     SELECT *
     FROM "YOUR UNIONQUERY"
     WHERE tblAccountNumber = (first checked) OR
     tblAccountNumber = (second checked) OR
     tblAccountNumber = (third checked)....

This will be inside the Do... Loop.  Once the recordset has gone thru all the records, you'll update the Forms RecordSource to your updated SQL generated in VBA.

Hope this  helps.

-Raymond

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View