Solved

Create a check box on a continuous subform

Posted on 2008-10-16
10
514 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now