Unbounded Checkbox in Continuous Form

I want to add a checkbox to a continuous form for temporarily marking selected records in a muti-user shared environment. I have been asked to do this without adding any new tables or fields to existing tables. I have reviewed as many of the previous ee Q&A as I can find on this topic and have only been able to find solutions that either suggest adding new database elements or writting rather complex VB code with subforms and dynamically created controls. I am not very experienced with SQL but have excellent VB coding skills. I would rather not go through the excesses of writting a lot of VB code when adding a simple table to the database would do the trick. But, because the owner of the database does not want to add new elements and some users will be accessing via a low bandwidth connection, I would like to do as much locally as possible.

Is there a way to build an SQL query that adds a temporary field to the resulting recordset that is not stored in the database but that can contain a changeable value?

Is there a way to add an unbound checkbox to the detail section of a continuous form that is instanced once per record, can contain it's own value, is accessable from VB, and provides to VB the record number to which it applies?

LVL 81
zorvek (Kevin Jones)ConsultantAsked:
Who is Participating?
knollbertConnect With a Mentor Commented:
Just create a small local access table

Don't link to SQL  when they change it, it would only change on their local computer
Philippe DamervalConnect With a Mentor Senior Analyst ProgrammerCommented:

"Is there a way to build an SQL query that adds a temporary field to the resulting recordset that is not stored in the database but that can contain a changeable value?"


"Is there a way to add an unbound checkbox to the detail section of a continuous form that is instanced once per record, can contain it's own value, is accessable from VB, and provides to VB the record number to which it applies?"

Yes. Use bookmarks. A bookmark is a GUID string that identifies the current record. It is a property of the form's underlying recordset. Simply build a string array and in the unbound checkbox array event procedure add a couple lines that add the current record's bookmark to the array. Then to retrieve the list of selected records iterate through the string array, each time setting the form's recordset bookmark property to the string that you stored. Easy as pie.

GRayLConnect With a Mentor Commented:
Q1. Select [Item], [No], [Price], [No] * [Price] * 1.07 as [Total Plus Tax] from Table;

In this query of four fields, three are from the table and the fourth is computed. It does not form part of the table after the query is run.

Q2. A checkbox cannot contain a value other than 0 or -1. You can put an unbound checkbox on a form and ascertain its value by - Forms!FormName!Checkname which will return either 0 or -1 depending on whether it is unchecked or checked.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Emanon_ConsultingConnect With a Mentor Commented:
Hello Kevin

Re >> Is there a way to build an SQL query that adds a temporary field to the resulting recordset that is not stored in the database but that can contain a changeable value?
You can create a field in your SQL using an IIF statement and assign the (check box) field a -1 or 0 value (true or false).  I think the answer will depend on exactly what you are wanting to do with this field...

Re >> Is there a way to add an unbound checkbox to the detail section of a continuous form that is instanced once per record, can contain it's own value, is accessable from VB, and provides to VB the record number to which it applies?
When you start working with continuous forms sometimes things can get a bit tricky or bit more difficult to do - depending on what it is you are wanting to do of course.

eg.  if you were to write an On Current Event to change the value of a check box on a continuous form.  You would change the value of all the unbound check boxes (if in detail section) based on the currently selected record.  However if you were to place the unbound check box in the form header...

I think if you were to give an example of what you were wanting to do, then all the experts here would be able to assist better.

zorvek (Kevin Jones)ConsultantAuthor Commented:
You guys are fast! Thank you for your responses thus far. So far I believe the solution that seems closest to meeting my criteria is from knollbert: creating a local table.

Knowllbert: Can I do this without creating a local physical file? If so, how? I am relatively new to Access.

For all: I want to place an updatable checkbox in the detail section of continuous form so that I can, as a user, mark non-contiguous records for a future action. The checkbox setting only needs to persist during a single query - in other words, I don't mind if it is reset when the selected record set changes. I can't use the solutions that place a calculated value in a query field as I need to be able to change it from the form. The bookmark solution works but not with a checkbox. I can only make the bookmark solution work with a single checkbox or command button located outside the detail section of the form...and there is no visual feedback on each record showing which records are selected and which are not.

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can create a temp table "on the fly" like this:

Private Sub Command0_Click()

Const conTableExists As Long = -2147217900

On Error GoTo Err_Command0_Click
'/create the table
CurrentProject.Connection.Execute "CREATE TABLE tmp (ID int, ChkBox YesNo)"

  On Error Resume Next
  Exit Sub
  Select Case Err
    Case conTableExists
      '/if the table already exists, just delete everything in it
      CurrentProject.Connection.Execute "DELETE * FROM tmp"
    Case Else
     MsgBox Err & ":" & Error$, vbCritical, "Form_Form1" & ": " & "Command0_Click"
  End Select
  Resume Exit_Command0_Click
End Sub

I did this behind the Click event, but you can put this anywhere you need ... I can't see how this would violate your client's wishes ... it's no more invasive than writing to text files on a hard drive.

You can also, of course, delete the table entirely upon exiting the application:

CurrentProject.Connection.Execute "DROP TABLE tmp"

thereby leaving no trace of any table ...
Philippe DamervalSenior Analyst ProgrammerCommented:
GrayL and Micheal: The question was, can a temporary value in a table be CHANGEABLE. The answer is of course not. There are dozens of ways of using calculated fields to do many wonderful things but they cannot be CHANGED by the user (except of course by changing a value in one of the fields that go into calculating the field).

As to the temporary table: This was specified as not being allowable, did I miss something? If it IS allowable then of course it is the right answer. I was merely showing a way around that, that's all.


Philippe DamervalSenior Analyst ProgrammerCommented:
More specifically, now that certain misunderstandings are out of the way:

"The bookmark solution works but not with a checkbox. I can only make the bookmark solution work with a single checkbox or command button located outside the detail section of the form...and there is no visual feedback on each record showing which records are selected and which are not."

That's because Access creates only one instance of an object per form, so in continuous forms this poses a problem as ALL checkboxes will come checked or unchecked if you use the checkbox as is. The checkbox's result is irrelevant to you - all you want is to react to there being a tick in it (someone clicked it and its value is now true). To do this, you programmatically create a separate instance of the checkbox for each record in the continous form. I'm sorry I didn't specify this earlier. It is basically applying the same principle as what used to allow to format items conditionally on a continous form before Access 2000 (and still today for more complex requirements).

A good example of the technique can be found here:

Let me know if you need more information.

zorvek (Kevin Jones)ConsultantAuthor Commented:
Philippe is correct. A table is not a preferred approach - either locally or in the main database. However, if the only way to do this is with a table then I would probably try to convince the database owners to put a marker table in the database.

Philippe DamervalSenior Analyst ProgrammerCommented:
Especially if, as LMSConsulting was pointing out, you can create a temporary table. It's really the "right" answer. Anything else is tricking Access into doing things it wasn't designed to do.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure how you'd programatically create checkboxes, unless you open the form in design view and add the number of controls to your form as needed ... this is assuming, of course, that the frontend is an .mdb file instead of an .mde (as I'm sure you're aware, you cannot open an Access form in an .mde in design view). Even then, adding/deleting controls to Access forms "on the fly" is not recommended, and can cause corruption of your objects. Of course, with a pure VB solution this is simple, but Access doesn't support control groups as does VB ...

I hate to say it, but sometimes you must "coach" your client and convince them it's the right thing to do ... temporary tables are a very, very common programming tool (most big RDBMS engines create temp tables constantly, so there is little issue with this). The remote users will create a temp table on their local machine, therefore the performance impact would be minimal (if any).

FWIW, I've never been able to do what you're wanting without using temp tables, and this question is asked ad nauseum in the ms newsgroups, and the answer is always the same - temp tables. I'm hoping someone comes up with something else, 'cause there's several places in my current projects I could use a trick like this!!
Philippe DamervalSenior Analyst ProgrammerCommented:
AFAIK opening a form in design mode is not the only way to add controls to it. It would be really impossible to do certain basic things in Access otherwise. Why would you recommend *against* adding controls to forms on the fly? I do it all the time and it hasn't been a problem yet.
You are correct in saying Access does not support control groups as VB does, but I'm not sure what this has to do with our topic. Can you elaborate?

I agree entirely about the best practice use of temporary tables, I do it myself often enough and it is my recommendation too of course. No issue there.
Except that it was specifically announced as not an option.
I don't "coach" my customers/users, to me they are kings and queens. I have found that if I don't listen carefully to their requirements, I regret it in the end, even if what they want me to do is not "industry standard". Better create something unorthodox that will work for all rather than a beautiful new impediment to productivity.
So, with these stringent but not showstopper requirements, I suggested the conditional control management approach. It's not "natural" to Access nor efficient, but it was the answer to the question AS FORMULATED.

A good example of the technique I am talking about can be found here:
It goes beyond displaying controls in different colors depending on their value, it also shows how to achieve pseudo enable/disable of an unbound control on a form in Continuous View. It rather cute, really.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Huh?? How do you ADD controls to a form in Access at runtime WITHOUT first opening the form in design view? The only way I know is to use CreateControl, and (unless I'm missing something) that is only available in design view. I must admit this is one trick I'd like to see ...

RE: adding controls at runtime - ANYTIME you alter the object model of a running application you run the risk of causing troubles. Adding a control can very easily decompile your application - and a decompiled application is an unstable application.  Most experienced developers deploy Access apps as .mde files (which completely disallow the alteration of forms and other objects) specifically to guard agains decompiling.

RE: Control groups - we've been discussing adding controls at runtime. With VB control groups, doing so is a trivial matter - in Access, which doesn't support control groups, this is NOT a trivial matter.

RE: "coaching" your clients - I guess we are of two different mindsets here. I most certainly listen to and respect my customers requirements, and agree that incomplete requirement gathering will always bite you in the end. I'd also agree that customers should have a great deal of input regarding the overall design of the system, and of course have the final say and your product must fill their need. But if you're a paid consultant, it's your job to recommend the best course of action. In this case, the best course of action would be the use of temporary tables on the local workstation. It would be the most stable solution, require the least amount of cost, and would most certainly fit the bill. If, after discussing with the client these options, they still were adamant against going this route, then it would be time to entertain other options - but in my mind, I would have done my job by offering them the best, least cost solution.

And, I certainly agree that my answer did not conform to that posted by the OP. However, my intent was to show the OP that you can use temp tables without "hardcoding" them to the database, which some may object to, and which may have given them the opportunity to discuss this option with their client

Philippe DamervalSenior Analyst ProgrammerCommented:
I guess I see where you're coming from. Let's use the space here to answer the question and not quabble about approach philosophies - I'm sure yours is just as good as any other.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Agreed - although I'd still like to find out how you add controls at runtime without opening a form in design view ... and I'm not saying this to pick an argument, I'd REALLY like to know ... I'd be glad to open a question and provide max points, as I think this would help many, many EE members.
ragoranConnect With a Mentor Commented:
Hi guys,

I agree that using a temporary table is a viable solution.  Although I was intrigue by damerval suggestion to use bookmarks in an array.  So I took some time to built a prototype around it and I manage to get an interactive check box also.

I am using scripting.dictionary object instead of an arry because it manage memory itself (don't have to change the size of it), it has a Exists method that is very usefull for us as well as a removeAll one.  You do need to set a reference to MS scripting runtime for this to work.  The code could be adapted to work with an array instead.

To try my solution, create a continuous form based on a table and drop some field on the form (text boxes, whatever).

In the detail section, add a checkbox and name it chkSelect.

In the form footer, add two command button:  cmdNone (caption "Select None") and cmdList (caption "List Selected") .  The later is just for test purpose.

Paste the following the following code in the form module:


Private moSelect As Scripting.Dictionary

Private Sub chkSelect_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
   ' <<<<<<<<<<<<<<  Move the focus to the first available control of the detail section

End Sub

Private Sub Form_Load()
   Set moSelect = New Scripting.Dictionary
End Sub

Private Sub ChangeSelectCurrent()

   Dim wBk As String
   On Error GoTo proc_error
   wBk = Me.Bookmark
   If moSelect.Exists(wBk) Then
      moSelect.Remove (wBk)
      moSelect.Add wBk, wBk
   End If
   Exit Sub
   If Err.Number = 3021 Then
      'click on new record
      Resume proc_exit
   ElseIf Err.Number = 91 Then
      'lost object
      Set moSelect = New Scripting.Dictionary
      Debug.Print Err.Number & "-" & Err.Description
      Resume proc_exit
   End If
End Sub

Private Function IsCurrentSelected() As Boolean
   Dim wBk As String
   On Error Resume Next
   wBk = Me.Bookmark
   If Err.Number = 0 Then
      IsCurrentSelected = moSelect.Exists(wBk)
      IsCurrentSelected = False
   End If

End Function

Private Sub SelectNone()
End Sub

Public Sub ListSelected()

   Dim rs As DAO.Recordset   ' <<<<<  need DAO reference if not already set
   Dim wBk As Variant
   Dim wResult As String
   Set rs = Me.RecordsetClone
   'looping on all selected records
   For Each wBk In moSelect
      rs.Bookmark = CStr(wBk)
      wResult = wResult & vbCrLf & "Record: " ' <<<<<<<<  put something relevant here  
   MsgBox wResult
   Set rs = Nothing
End Sub

Private Sub cmdList_Click()
End Sub

Private Sub cmdNone_Click()
End Sub

' ============================================

You will need to adapt yhe code where the comment shows <<<<<<<<<

Now set these property for the chkSelect control:

controlsource:  =IsCurrentSelected()
enabled: Yes
Lock: Yes
Tab Stop: No

Run the form, it should work if I haven't forgotten to write something....

zorvek (Kevin Jones)ConsultantAuthor Commented:

Please be patient...I am evaluating the various ideas presented here along with some other ideas I have received from different sources. I am getting close to a resolution pending the answer to another question I might need to post. Lots of great ideas to work with.

Thank you for your help thus far!

Comments on the suggestion:

This thread has a good discussion on different approaches that can be used to solve a problem.  Many suggestions were put forward.  I also understand that sometimes, "no possible" is the only answer to a question.  But in this case, I believe that giving all the merits to LSM is not correct.  Don't take me wrong.  LSM solution is correct but it does not address all the issues, namely that the user can't create a temporary table.

Based on damerval ideas, I put a prototype of another solution that does not rely on a table.  This solution is also viable. Since then, I put a demo on the web for all to see  http://www.geocities.com/ragoran_ee

I suggest that points should be split between LSM, damerval and I.

Too bad the author has not come back to give us his final solution...

zorvek (Kevin Jones)ConsultantAuthor Commented:
Sorry this took so long from my end. I ended up using a solution that was never really discussed here. I created a local recordset instance, copied the fields from the query and added a boolean field for the checkbox. This created a number of problems that had to be dealt with such as forcing the local recordset to behave the same as a recordsource and getting the checkbox to behave like a real checkbox. It was a mess and I'll never do it again.

I tried many of the ideas presented here but could not get any to produce better results than a local recordset. I tried to split the points based on how reasonable the idea seemed and how much effort was provided. I appologize for taking so long and if I failed to recognize anyone or a specific solution.

If faced with this type of constraint in the future I will try using one of the many list-based controls and just load the query into it and let the control do it's thing. Access continuous forms, and Access in general, just doesn't behave as well as I would like when you start hacking around.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.