how to select individual records in Continuous Forms?

Hi experts,
I have a continuous form displaying records of employees, and would like to have a way to select (thru a check box) individual records in order to perform certain tasks, however I don't want to create a yes/no field in the employees table since this will be used by multiple users at the same time, therefore one user may select one set of records and another user a different set, what is the simplest way to accomplish that?
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Checkboxes in continuous forms must be bound to a field, so your only option would be to use temporary tables in each user's Frontend. If you do NOT have individual FEs for each user, then I'm not sure exactly how you'd do this effectively and reliably

Basically, a temporary table would be identical to the "root" tables the form is currently using, with a Select field included. This table would have to be filled each time the continuous form is opened.
0
bfuchsAuthor Commented:
Hi Scott,

well in that case it would not be possible to have this working in an ADP project as it does not support local tables.

Besides the fact that I don't want to committed to the idea of extra copy of FE, although users are instructed to always use their copy, I had instances were they had to be using the shared file on the server.

I know there is at least one way to do it, as I saw another programmer did it on our ADP, he did using item collection, however I didn't want just copy what he has done without understanding how that works..

Is it possible that I poste the code here and you explain me how that works?

Thanks,
Ben
0
bfuchsAuthor Commented:
Just realized another issue when trying to do according to what you suggested.

even when the form's recordset is a local table, but when filtering for something like the following:

" EmployeeID in (select employeeid from tmpPassThroughQry)"

the form becomes not updatable, and I am unable to check/uncheck fields.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BitsqueezerCommented:
Hi Ben,

you're right, adding a bit field would not solve your problem because of multiple users. The solution is simple: Create a table which consists of an ID field to be filled with the ID of the original table and an ID field which gets the ID of the user and of course the bit field.
When you want to select records you usually don't want the user to be able to edit the record at the same time (although that's possible). So you can now create a view over both tables which is filtered by the UserID so that one user gets his own list of records independent of others. When you open the form to select records you can simply first delete all entries in the selection table with the current UserID (so it reorganizes itself) and then INSERT all IDs with the current UserID into the selection table from the original table which you want to make selectable.
In the frontend you can now present the result of the view which combines these two to show the relevant information of the original table so that the user knows what he selects and the checkbox from the selection table which would be the only unlocked control in the record. As you are only changing one table (the selection table) here the view should be updatable.

Now you can let the user select what he wants and you can SELECT what he has selected from the selection table together with the ID of the original table.

You can also add a further field to this selection table if you want to use the same for other selection lists: Add a field with an indicator for which table it should be used, i.e. the table name itself or whatever you want. With this change the same selection table can be used for any table to select records and it is fully multiuser compatible.

Of course there are other possibilities, also in ADP possible. You can i.e. use a MSForms Listbox which can simply add a checkbox as first column (Access Listboxes can't). Or you can use a checkbox in a continous form which is not bound to a field but a public function which calculates the selection state. I've created demo databases for that purpose which you can find on my download page here:
Bitsqueezer's Access Downloads
The MSForms checkbox method is in the file "CheckboxAuswahl" and the other method in "SelectRecordsV2".
Both methods don't need any additional table.

Cheers,

Christian
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could also use a MultiSelect Listbox, where the user could select as many items from that listing as needed.
0
BitsqueezerCommented:
Hi Scott,

yes, but not with a checkbox, which makes the selection process a lot easier, especially if you have a large number of items.

Cheers,

Christian
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
but not with a checkbox
True, and as you mention a multiselect listbox can be difficult to work with if you have large numbers of items - but it's simple to implement, and makes use of native Access controls, which IMO is a solution much preferred to using MSForms controls.

But it may not work in all cases ...
0
BitsqueezerCommented:
Hi Scott,

yes, that's why I mentioned it at the end only as possible variant, but I personally never use Listboxes, they can always be replaced with a subform which has no limitation, where i.e. the number of items in a Listbox is limited. Using the method with the selection table is IMHO the cleanest as this can be done easily with a continous form.

Cheers,

Christian
0
PatHartmanCommented:
I'm pretty sure this is what bitsqueezer was suggesting but I'll add a little more detail.  To handle this type of selection, I use a separate table.  The table has three (or four, if you want to make it multi-use, columns).   I also make a unique index on UserID/SelectedRecID/SelectedFrom(if used) to prevent duplicates.
RecID  (autonumber PK)
UserID
SelectedRecID
SelectedFrom (optional if you want to use the selection table to log selections from different tables)

To implement, I add a double-click event to a control if I am using DS view or use a button if I am using continuous view.  Behind the scenes, the event essentially works as a toggle.  If the selected record is already in the select table, the code removes it otherwise it adds it.

If you want to get really fancy, you can join the select table to the form's RecordSource and color code a field if a match is found in the select table.  That gives the users a visual clue that they have selected a record which they would otherwise not have.  Or, you can even have a checkbox field in the select table so you give the visual of a checked box if you like that better.
0
bfuchsAuthor Commented:
Hi Experts,
I appreciate all your suggestions, here are my comments,

@Scott,
List box solution is how I have it previously programmed, however besides of being difficult to select as you're mentioning, I have another issue that's annoying my users as follows.
I use continues forms as a way to display reports, where I have the top section with all filter options (it has about an inch in the height, place for 2 or 3 lines of controls), and the rest of the screen is for displaying the data.
now a ListBox  of 3 lines is definitely not ideal for selecting multiple records, the way I have done it is by having a button increasing the forms header size and also increasing the ListBox size and moving it to the middle of the screen...and then when clicking again it restores everything on place, however as I said users were not so happy with that, therefore I am searching for a different approach.

@Bitsqueezer, PatHartman
The issue I have with your solution is showing the users what they had selected.
re joining the select table as Pat had mentioned, it would work in some cases, however as I described above (ID:40287086), by using some filters the form would not be updatable, and if I just update the table behind the scenes, that would require a requery after each selection in order to reflect the changes.

@Bitsqueezer,
Will look into your downloads & let you know if they work for me.

Thanks,
Ben
0
PatHartmanCommented:
The suggestion I made would be updateable.  You need to separate the way records are selected.  The checkbox method is useful when there is no logical way to specify criteria.  I've attached a picture of one of the search forms I am currently working with.  A query is built behind the screens and fed either to a form for viewing, exported to excel or to a report for printing.  The query box is updateable although I haven't told the users that.  They're not ready for that yet.  The second picture is the Search Results form. Clicking on EMS opens the Client detail form.  If the search only finds one record, the Search Results form is bypassed and the Client detail is opened directly.
SearchForm.JPG
SearchFormList.JPG
0
bfuchsAuthor Commented:
Hi Pat,
I do have a filter in place for the logical part, its just that in addition to that the users want an option to select individual records without apparently any logic,  which means after they select for example all employees from a given city they get x amt of records, now from those records they want to exclude certain individuals, all done at the same form.

I understand that your suggestion in general is an updatable, but did you try filtering for something like I posted above ("where ID not in (Select EmployeeID from TmpPassThroughQry"), since the pass-through query in access isn't updatable looks like that causes the entire recordset to become not updatable.

BTW, I did like the design of your filter screen, very impressive tough.
just wonder what is the path...meant for?
also didn't see any EMS button, you meant the view list button or just clicking in the text box named EMS?

@Bitsqueezer,
I didn't know you have such a large selection of downloads, for that alone was worth starting this whole thread:)
While looking at those 2 databases, I seems to me that the 1st you mentioned (CheckboxAuswahl) would require a popup screen to apply that, which would in the long run require for me to design a new form for every place I need this type of selection.
 while your second database (SelectRecordsV2), looks like the type of idea I am looking to apply in my app, (if I am not mistaken this is how that works in our ADP as I mentioned above), however would prefer if you can provide some kind of explanation on how it works before I integrate, and one more point, is it possible to get rid of the jumping after each selection?
0
BitsqueezerCommented:
Hi Ben,

could you provide a screenshot of your current form? Would make it a little bit easier to follow. If you can provide a demo database it would even better to follow your "not updatable" issue.

Pass-Through-Query: I thought you use an ADP? There are no PT-Queries in ADPs as you should already know.

If you join two tables together like I explained above the selection itself should be updatable in any case as long as you do not try to also change anything in the main table at the same time. I didn't try using the IN filter but I cannot see why this should make the recordset not updatable. Normally it is more important that your recordset contains the primary key field(s) so Access is able to identify each record.
Another method is of course that you can make ANY view of the server updatable if you use INSTEAD OF triggers. You'll get the complete record like in the recordset in the two temp tables "inserted" and "deleted" and then you can extract the data of both tables and save the data into the right ones. It's a little bit brain jogging but possible.

Another thing is that you are forced to use the SQLOLEDB driver in Access if you want to bind a recordset to an Access form which should be updatable. That means, you can use the ADO connection your ADP provides which is always SQLOLEDB or, if you opened an own connection and used the method to open and set the ADO recordset to the form's recordset property, then you need to use this driver on your own (i.e. it would not be updatable if you use SQLNCLI etc.).

Downloads: I've created this page as my favorite forum in Germany was hacked and the provider closed the forum and made it read-only so nobody is able to download any attachment file there. Made this a few weeks ago so these are only the most important files of the old forum. Feel free to use any of them like you want, some of them are only not translated in English. For you the CCFilter classes are maybe of interest as this is a method to filter in any kind of form. It was made to have one standard way of filtering for any type of Access database, MDB/ACCDB and ADP, works with DAO and ADO and with Access filters or with the (only in ADP available) ServerFilter which is really the fastest filter. The current version unfortunately does not support multi select for one field, that's a thing I'll be implement in near future.
The code is completely documented, but in German. As there are also some demo forms it should not be hard to understand how it works.
It is made to automatically scan the datatypes of the recordset of the form, then searches the form for some controls named like each field (like a field "Surname" and a filter textbox named "Filter_Surname") and automatically assign event procedures to use that as a filter input box. The current version also implements a new way where you even not need to insert filter controls, you can insert a button to open a filter form and that dynamically creates all the needed fields and labels (up to 50) depending on the datatype and fields of the recordset of the form.
It was made to work with an ADP and has been running the last years in production without problems.

If I ever find the time to translate all the documentation in English I will write an article here about that, but nevertheless it of course works without that (standard language for the filter is English, can be switched to German).

Cheers,

Christian
0
PatHartmanCommented:
The Path field and builder button to the right are used when the export to Excel option is selected (or output to PDF on other forms).  Rather than hard-code a path, I let the user choose one each time.  A newer version of the application includes a setup table where users can define preferences and one of those is a standard path for exports.  In that version, the path is pre-populated from the setup table entry for the logged in user but the path can be overridden if necessary.
It looks like bit has answered the other questions.
0
bfuchsAuthor Commented:
Hi Experts,
@Bitsqueezer,

I am attaching a copy of the screen in question, including the underlying table.
The purpose of this screen is to list employees who are avail to work meeting a certain criteria, after filtering they have an option to click on Email/SMS button in order to send an email to selected employees, what is missing is the option to exclude individual records from the list.
the issue with the recordset becoming not updatable is when selecting for example from the languages list box one or more languages.

The Database in question is an Access mdb linked to sql BE (Partially still in Access BE).
What I meant above is that Scott's first suggestion with local tables would not work in an ADP, and since we have an ADP project in place, therefore would like a way that would fit to all.

Thanks,
Ben
0
PatHartmanCommented:
Create the table in the BE.  You need to include the User ID since the table will be shared.  Go back and read my description again of what needs to be in the table.

When you link to SQL Server and other RDBMS' Access REQUIRES a unique identifier on the linked table or it will refuse to update it and any query you use the table in will be not updateable.  That could be why your query is not updateable.
0
bfuchsAuthor Commented:
Hi Pat,

I forgot to attached, sorry for that.
Btw, it has an unique identifier and the only time I cant update it is when I filter as described above.
db2.zip
0
BitsqueezerCommented:
db3.zipHi Ben,

the form is really an example of a very chaotic style, code and design...

It looks like if someone has shaked a bucket of fields and spread it all over the title area with the intention to enclose as many fields as possible and the position is only set where there is an empty space left...
If I were the customer and someone would present me such a form I wouldn't buy that, really.

To show how you can easily win a lot more space and an easy to understand structure for the user I made a little example in the attachment for you. The code is left unchanged except a little addition to manage the switch between the filter group pages. I tried to insert the filter fields in some groups where it may be belong to as best as possible from what I could read from the form, but you can of course change all that to better group names and better grouping. Should only show the way. I also changed the formatting of all fields to fit one style (you used sometimes flat or sunken, different fonts, different color styles and so on which makes such a form look even more chaotic).

The elements on the form have no structure in naming, some are named with prefixes, some have a generic name, some have a name like a field. It's really hard for any programmer to work with such mixtures and especially the generic names (like "Command127"), the first thing you should always do is, create a unique naming convention for the whole application and then name all the elements in that way (including also elements you do not access programmatically like "label14" or something like that). The design and programming would be a lot easier.
Same with names used in the code like variable or procedure names and so on.

The problem with this:
EmployeeID in (select employeeid from tmpPassThroughQry)
is that "tmpPassThroughQry" seems to be a pass-through-query from Access. PT-Queries are always read only so if you use that mixed with local tables it is likely that Access make the whole thing read only. I cannot test that as I've not the needed environment.
But as you anyway use dynamic SQL to create the query you could easily create a function which gets the IDs from the PT-query and format that as a comma separated list to insert that into the SQL string. Try to replace the SELECT in the IN clause with some existing IDs to see if that works and if, you can use such a function to solve that problem.

It seems from the structure of the code that your table which you use to filter with the fields is already filled each time you use this form. This is the solution I described above, you only need to create that table in the backend (SQL Server) and add a userID and a selection field (bit) and then you can go on like I (and later on Pat) described above.
You can of course not use a PT-Query to bind such a table to the form as this is read-only, but you can open the table using an ADO recordset and assign that to the form's recordset property (which works in both types, ACCDB/MDB and ADP as a form's recordset property can use DAO and ADO). You only need to make sure that the ADO recordset is updatable, details about that can be found here:

http://support.microsoft.com/kb/281998/en-us

As your demo database didn't contain any demo data I couldn't test much in functionality.

Cheers,

Christian
0
bfuchsAuthor Commented:
Hi Bitsqueezer,

I new this will come...this is why i refrained from posting the form at the first place..:)

However you did a great job redesigning our other screen in the past, and my users happen to like it, therefore i decided its worth the chance.. will present them this time again and see their feedback.
(I can also see they complaining that is not so convenient if you cant see everything at once...)

re ethics in programming design, you're 100% right..

now back to the original issue,

from all options i saw, the one i liked best was as mentioned, the one avail at your download page named SelectRecordsV2, as in my eyes will require the least changes to the way the program currently works, meaning I don't have to change anything just add that check box and add that class to check the status of that check box.

Will keep you posted.

Thanks,
Ben
0
BitsqueezerCommented:
Hi Ben,

yes, that's the only disadvantage, that you can't see all fields at once. But "at once" is also not the case in your original design as you need to scroll that left and right all the time. In my current project I use the same trick with the tab control to also have groups of fields (real fields, not filter fields) in a continous form so that the whole data can be accessed in one screen without any horizontal scroll bar. My client says that it very hard to work with a screen where you need to scroll left and right even if all the fields are in one row.

I would only add a little bit code to the new design to change the color of the switch buttons in the case that in one of the filter group any of the fields were changed by the user. That makes it easier for the user to see in which group there are changes. Also a button to clear all filters outside the filter groups would be a little more comfortable. I didn't have the time to insert that..:-)

Cheers,

Christian
0
bfuchsAuthor Commented:
Hi Bitsqueezer,

Still waiting for users feedback..

Re the select solution, I tried the following (I think its basically the idea you're using in SelectRecordsV2).

Dim colCheckBox As New Collection
Dim bPrintAll As Boolean, bSMSOverLimitOK As Boolean

Public Function IsChecked(vID As Variant) As Boolean

   Dim lngID      As Long
   
   IsChecked = False
   
   On Error GoTo exit1
   
   lngID = colCheckBox(CStr(vID))
   If lngID <> 0 Then
      IsChecked = True
   End If
   
exit1:
   
End Function



Private Sub Command189_Click()

 If IsChecked(Me.EmployeeID) = False Then
      colCheckBox.add CLng(Me.EmployeeID), CStr(Me.EmployeeID)
   Else
      colCheckBox.Remove (CStr(Me.EmployeeID))
   End If
   Me.Check187.Requery

End Sub

MyCheckBox=IsChecked([employeeid])

         If (IsChecked(Me.EmployeeID) = True) Then....

Open in new window


So far that works, where the Command189 is a button next to the check box.
it happens to be i have another advantage using this method, as the recordsource can have multiple records per employee, and what I needed was that when an employee gets selected then all records belonging to that employee should get selected as well, and that is how this works..

Thanks,
Ben
0
BitsqueezerCommented:
Hi Ben,

sounds good, but I cannot say much about that as that would need your environment to see what happens.

I would only shorten the "IsChecked" function a little bit:

Public Function IsChecked(vID As Variant) As Boolean
   On Error Resume Next
   IsChecked = colCheckBox(CStr(vID)) <> 0
End Function

Open in new window


And I don't know why you need an additional command button, it should be enough to implement the checkbox and bind that to the function as in the demo forms like "=fnGetSelection([ctlID])", so that a change of the checkbox only affects the current record.
Nevertheless, if it works for you, it's OK...:-)

Cheers,

Christian
0
bfuchsAuthor Commented:
Hi,
1- you right i shortened the function as you suggested.
2- not sure how yours work as when i do it with mine, control source = "=IsChecked([Employeeid])" the check box is not updatable , i get the message that is bound to expression IsChecked.., whats the magic behind your version?
0
BitsqueezerCommented:
Hi Ben,

it's the same in my demo form: If you look into the status bar it always says that it can't be changed as it is bound to the formula.
The trick is that it is indeed not changed by clicking the checkbox, the clsCCRecordSelect does the magic. When you initialize that as an object in the form it will bind a "MouseUp" event of the checkbox to the "MouseUp" event procedure in the class module:

Private WithEvents prv_Checkbox     As Access.CheckBox
...
Public Sub InitSelect(ByRef frm As Access.Form, ByRef ctl As Access.Control, ByVal strIDFieldName As String, _
                      ByVal strSource As String, Optional ByVal strWhere As String, _
                      Optional ByVal intSelectionMode As EnmCCRecordSelectionMode = EnmCCRecordSelectionMode.enmCCRecordSelectionMode_Multiple)
...
    If TypeOf ctl Is Access.CheckBox Then
        Set prv_Checkbox = ctl
        prv_Checkbox.OnMouseUp = cEventProcedure
    Else
        Set prv_Option = ctl
        prv_Option.OnMouseUp = cEventProcedure
    End If
...
End Sub

...
Private Sub prv_Checkbox_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ChangeSelection
End Sub

Private Sub prv_Option_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ChangeSelection
End Sub
...
Private Sub ChangeSelection()
    Dim strCurrentID As String
    Dim ctl As Access.Control
    strCurrentID = Nz(prv_Form.Recordset.Fields(prv_strIDFieldname))
    If Not strCurrentID = "" Then
        If prv_SelectionMode = enmCCRecordSelectionMode_Single Then
            If prv_Option Is Nothing Then
                Set ctl = prv_Checkbox
            Else
                Set ctl = prv_Option
            End If
            ' Get all IDs again and fill the collection
            Me.InitSelect prv_Form, ctl, prv_strIDFieldname, prv_strSource, prv_strWhere, prv_SelectionMode
        End If
        
        If Not prv_ColIDs.Count = False Then
            If prv_ColIDs(strCurrentID)(1) = True Then
                prv_ColIDs.Remove strCurrentID
                prv_ColIDs.Add Array(strCurrentID, False), strCurrentID
            Else
                prv_ColIDs.Remove strCurrentID
                prv_ColIDs.Add Array(strCurrentID, True), strCurrentID
            End If
        End If
    End If
    prv_Form.Recalc
End Sub

Open in new window


So it ignores the error displayed in the form that the checkbox can't be updated. The checkbox need to be "Enabled=Yes" and "Locked=No" so the mouse events will fire anyway. The captured MouseUp event calls the "ChangeSelection" sub which finds out the ID of the row by using the form's recordset and the specified ID field in the Init procedure. The ID is then added or removed to the collection of selected IDs and at the end a "Recalc" is fired to let Access recalculate all selection checkboxes in the form. As "Recalc" doesn't change the position of the current record it seems to the user that he has changed the current checkbox only by clicking it, in reality all checkboxes get their current value by executing the bound formula. The click could theoretically be anywhere else, you could for example bind a form DoubleClick event in the class module to do the same, in result you could also make a double click on the record selector to change the selection also. You could also add a click event to all controls of the row so the user can click on any point on the row to change the selection - whatever you want. In this case you even do not need the checkbox itself as this is only needed to show the selection optically (I also added a locked textbox in the demo which is placed in the background and used conditional formatting to color this yellow to make it easier to see which row is selected).

The trick is really only that the MouseDown event will change the current row so that the record selector points to the record you want to choose and so you can read out the ID of the current row. The MouseUp event can then be sure that the row is the wanted one, read the ID out and add it to the collection. That's the reason why you can also use a radio button like shown in the demo - because normally a radio button can only be selected but not unselected again like a checkbox using the normal behaviour. As the result is calculated it can in this case. You can also do the same with any other kind of control (which is able to use a formula). For example, if you think the checkbox or option button is too small and too ugly, you could create a box control in the background and position a textbox over it, using a font like Wingdings to let the formula return the wanted character (=symbol in case of Wingdings) to be displayed in the textbox when the form is selected or unselected. This would only need slight changes in the code.

Cheers

Christian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Hi Bitsqueezer,

Its always a pleasure to deal with you, I guess its time to finalize this  thread, its possible that there are more then one way to solve this issue, however as described above this looks like the most ideal to our environment.

1- re the design of the screen, users are still debating if that is worth, I guess because its easier for them to use the scroll bar then to have to switch between tabs (I know my customers...)

2- In order to apply your suggestion about check box mouse event, I would need a little more learning/practice in dealing with classes, I am not a fan of copying code that i don't really understand how they work (unless of course its crucial for the app).

Thank you very much!!
Ben
0
BitsqueezerCommented:
Hi Ben,

you're welcome, glad if I could help you.

Cheers,

Christian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.