Solved

Unbounded Checkbox in Continuous Form

Posted on 2004-08-11
20
4,908 Views
Last Modified: 2007-11-27
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?

Kevin
0
Comment
Question by:zorvek (Kevin Jones)
  • 6
  • 4
  • 4
  • +4
20 Comments
 
LVL 6

Accepted Solution

by:
knollbert earned 125 total points
ID: 11777788
Just create a small local access table

Don't link to SQL  when they change it, it would only change on their local computer
0
 
LVL 9

Assisted Solution

by:damerval
damerval earned 100 total points
ID: 11777860
Kevin,

"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?"

No.

"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.

Philippe
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 50 total points
ID: 11777935
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.
0
 
LVL 5

Assisted Solution

by:Emanon_Consulting
Emanon_Consulting earned 75 total points
ID: 11777949
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.

Cheers
Michael
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 11778083
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.

Kevin
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 75 total points
ID: 11778306
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)"

Exit_Command0_Click:
  On Error Resume Next
  Exit Sub
Err_Command0_Click:
  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 ...
0
 
LVL 9

Expert Comment

by:damerval
ID: 11779259
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

0
 
LVL 9

Expert Comment

by:damerval
ID: 11779305
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:
http://www.mvps.org/access/forms/frm0024.htm

Let me know if you need more information.

Philippe
0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 11779363
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.

Kevin
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 9

Expert Comment

by:damerval
ID: 11779372
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.
0
 
LVL 84
ID: 11779446
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!!
0
 
LVL 9

Expert Comment

by:damerval
ID: 11779533
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:
http://www.lebans.com/conditionalformatting.htm
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.

Philippe
0
 
LVL 84
ID: 11779719
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

0
 
LVL 9

Expert Comment

by:damerval
ID: 11783720
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.
Philippe
0
 
LVL 84
ID: 11783763
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.
0
 
LVL 14

Assisted Solution

by:ragoran
ragoran earned 75 total points
ID: 11788612
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)
   ChangeSelectCurrent
   Me.chkSelect.Requery
   ' <<<<<<<<<<<<<<  Move the focus to the first available control of the detail section
   Me.Text1.SetFocus

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)
   Else
      moSelect.Add wBk, wBk
   End If
   
proc_exit:
   Exit Sub
   
proc_error:
   If Err.Number = 3021 Then
      'click on new record
      Resume proc_exit
   ElseIf Err.Number = 91 Then
      'lost object
      Set moSelect = New Scripting.Dictionary
      Resume
   Else
      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)
   Else
      IsCurrentSelected = False
   End If

End Function


Private Sub SelectNone()
   moSelect.RemoveAll
   Me.chkSelect.Requery  
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  
   Next
   
   MsgBox wResult
   
   Set rs = Nothing
 
End Sub


Private Sub cmdList_Click()
   ListSelected
End Sub

Private Sub cmdNone_Click()
   SelectNone
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....





0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 11922683
All:

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!

Kevin
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12775812
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...


0
 
LVL 81

Author Comment

by:zorvek (Kevin Jones)
ID: 12775885
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.

Kevin
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

758 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