Solved

Create a check box on a continuous subform

Posted on 2008-10-16
10
538 Views
Last Modified: 2013-11-28
Hi everyone! I have been trying to get this to work and have not been able to.  I am sure I am missing something and would greatly appreciate any help.
I created a form which contains a continuous subform based on a query.  I created a check box for each row but when I click on it, all of the rows are checked/unchecked.   I could not figure out a way to bind the check box to the query (it is a pass through query to Oracle) so I bound it to a separate table.  When I try to check the box, nothing happens except for a message appearing in the corner that the Form is read only.  The table I bound the check box to is not read only.  

I think that is everything, please let me know if you need additional information.
Thanks,
Josette
0
Comment
Question by:jkoneil
  • 5
  • 4
10 Comments
 
LVL 75
ID: 22735138
"all of the rows are checked/unchecked"

Well, there is only one physical control. So, without a control source, that's what happens.  You can't really bind a control to an entire table, only a field in the table.

mx
0
 
LVL 75
ID: 22735141
What is the purpose of the check box ?

mx
0
 

Author Comment

by:jkoneil
ID: 22735193
I should have been clearer, I bound the check box to a field in the table.
When the user checks the box a row is inserted into a temp table.  If they uncheck it, the row is removed from the temp table.  Once they check all of the rows they are interested in, they click a button to insert the rows from the working table into the actual table.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 75
ID: 22735261
I see.

ok ... in the table to which the query driving the subform is connected, to ... add a Yes/No field in that table - lets call it RecSel or RecChk.  Then, on the form -  make that field the Control Source for your check box.

mx
0
 

Author Comment

by:jkoneil
ID: 22735514
Is there another way to include a Yes/No field in the query?  I can't add a field to the table.
Thanks.
0
 
LVL 75
ID: 22735745
"Is there another way to include a Yes/No field in the query? "

Not really, because any 'fake' field you add to the query will not be updateable, ie you won't be able to check it.  

I understand what you are trying to do, but the way I do this sort of thing is by having that RecSel field in the table.

mx
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 22736632
Adding a selection flag field to your data table is an easy option (when you're not forbidden from doing so that is ;-) but it harms the concurrency of your application (picture two users simultaneously selecting rows for whatever reason - it's effectively mass and near random updates - can't you just hear them... "Umm... that one... that one.. ermm... that one... that, that and that one..." :-)
You can use other methods of determining selection though.
For example a local table (not any table you're not permitted to touch - one of your own making).
Such a table would hold the PK of the data table and the required boolean field.
Basing your form on the query joining the two (using an Outer Join of course - so that all records are offered) allows simple selection.
If you look in the List Select demo in the examples page listed in my profile then it's the first method shown (Simple Select) in that.
Also in there (I believe - it's been a while since I looked at it) is an alternative using code to assign selections to a given parent record... (Subform Checkbox).
There's a not dissimilar method which uses no data at all - first developed by Albert Kallal (great bloke) where you can use simple function assignments to a checkbox control source and a collection in code is used to simulate boundd selections.
Finally comes my data free but none the less bound ADO methods.
These are rather more complicated - but an option if you're interested.  They're found in the last two example methods on the fore mentioned examples page.  (They're actually in "RecordsetBind Example.zip" and "RecordsetShape.zip" ).
Given then you say your data is inherently read only - the original query solution might not be the way for you.  Hence the more code oriented methods are options.

See how you go.
0
 
LVL 75
ID: 22736665
Hey Leigh ... MIA!

"picture two users simultaneously selecting rows for whatever reason -"
Mine would be accompanied with a UserID of course.

mx
0
 

Author Comment

by:jkoneil
ID: 22761623
I was able to create a local table and use that as the record source of the form rather than a query.  I use a query to populate the local table based on a selection in a dropdown list and then delete the content when the user is finished making their selections and updating the database.  It worked well in the tests that I ran anyway.

Thanks for your help.
0
 

Author Closing Comment

by:jkoneil
ID: 31506907
Thank you for your help.  I really needed to work this out.
Josette
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Macro to import XML in Access 2013 2 40
unable to create table-based data macro in MS ACCESS 2013 11 25
Create macro from runcode 30 26
2 IIF's in Access query 25 32
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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

832 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