in Access do I use ListView (activex) or a datasheet or some other form, etc

I'm not sure what is the best option and least code. I am selecting records and displaying them in a listbox. I want to link the records back to a table. If the record is on the table I do not want it to be able to be selected. I want the records that have no link to the table to be marked already selected. Listbox allows the first 2 options.. What listbox does not allow is if the record is on the table I link to I want to change the forecolor. Listbox only allows color change for all items in the listbox. I also want the records that I find on the other table to not be selected. Either locked or not enabled. Listbox does not allow that. Unfortunately this is the display and function needed.
My question is what is the best way to code this? By best I mean easiest and least code! Listview (activex), a grid, datasheet or some other option?

Chuck LoweAsked:
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.

Dale FyeCommented:
Personally, I would use either a continuous form or a datasheet.  The key to this technique is to create a table (tbl_SelectThis) with two columns (PKID - long integer, IsSelected - YesNo).  To use this table, you must first delete all of the records from the table, then run an append query that adds the primary key from your main table into the PKID field and set the IsSelected column to either 0 or -1 based on "the record is on the table", I'm not quite sure what you mean by that.

Next, you would create a query that joins this table to your other query based on the PKID field, which will give you an updateable query (allowing you to check/uncheck the IsSelected checkbox).  Then create your subform, either continuous or datasheet from this query and add conditional formatting to one of the fields based on the value in the IsSelected checkbox.

One of the other advantages of this technique is that you don't have to worry about accidentially clicking on a row and unselecting the other selected rows.
Chuck LoweAuthor Commented:
Sorry, let me explain. We have a table of Plans (tbl_Plans), that need to be audited. Key is PlanId. When they are audited they are in the audit table (tbl_Audit). I need to display all plans, regardless of their status (being audited or not), from tbl_Plans for a client (ClientID). I link to tblAudit and if I get a hit I do not want to allow the Plan to be selected for auditing again. I'd like to have those plans with a hit in tbl_Audit to also have a different color and be locked out from being selected. Also all the plans that are not audited (not in tbl_Audit) I want to come up as being selected. 99% of the time all plans for a client will be picked for auditing. This is a user requirement and saves them from clicking the mouse. Currently I have this in a listbox. But listbox won't allow colors set by row and won't allow me to lock out by row. I thought listview would save me coding some since it's already in a listbox. But it you feel a continuous form or a datasheet will be better I'll try one of them. Which do you thinks is A. easier to code and B. more flexible?
While a ListView control is much more flexible than a Listbox, there is a lot more coding involved just to setup the ListView control and get it populated, so the answer to which is easier to code would be the subform as Dale mentioned.  As for flexibility, both controls have lots of flexibility, each in their own way.  The thing to remember is that your customizations in a ListView control are more or less done via code.  If you want to add another column for instance, you would add another line of code whereas in a continuous subform, you would add another bound control or drag one from the list of available fields (or modify the recordsource if using a datasheet view).  I'm not saying that one is better than the other in this regard, however, you did mention at the onset that you wanted less code.

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.

The datasheet view form suggested by Dale is by far the simplest solution to implement.  I doubt that it will take more than a couple of lines of code in the form to do what you need to do once the table that Dale described is populated.  You could have the combo select only unaudited plans then you only need a line of code in the form's AfterInsert event to update the audit list table to mark it as audited.  Then requery the combo to get the item out of the list.
Chuck LoweAuthor Commented:
Still being new to Access (formwise that is-I know SQL) what exactly do I do to add a continuous form or data sheet? And could you tell me what the differences are between the two?

I have other fields on the form. I can make a copy of the form. Do I then delete the Listbox and add the datasheet/continuous form and the necessary controls(code) of course?
Forms have three views - single, continuous, datasheet.  The Single view shows one record at a time.  The other two views show multiples.  The difference is that DS view looks (and acts) a little like a spreadsheet with rows and columns that the user can adjust in size whereas the continuous view is the same as the single view with the size and locations being static.

Subforms are simply forms that are placed on other forms.  So, to create a subform, you start by creating a form.  When you save the form, you might give it a name that reflects its position in life.  I prefix my forms with "frm" and my subforms with "sfrm".  Close the new form.  Open the original main form and make some space for where the subform should be.  Drag the subform from the form list and adjust it's size as necessary.

When you add a subform to a mainform, Access will automatically create the master/child links if it can identify the relationship between the forms based on what you have defined in the relationship window.  You should always check though and if Access doesn't automatically populate the links, you must do it manually.  This is the glue that allows Access to synchronize the subform with the main form and to populate the foreign key of the subform when you add a new row.
Chuck LoweAuthor Commented:
First thanks for the explanation. I was able to create the form (subform) and put it on the main form. I changed the propertes so the fields can not be edited, added or deleted. I'm using the DS view. I'm sure there will be an issue with the users being able to change the size of the fields (like in Excel) but they don't stay changed when the form is closed and re-opened.  There seems to be no way to lock down the size of the fields. My issue is how do I mimic the Selection/unSelection as I had in the Listbox? You mention combo select.  It needs to be next to every record on the DS. I'm not sure where to put that exactly. Does it go on the subform or on the main form next to the subform? How is that placed on the form and and how is that tied to each record ?
You're welcome.

If you don't want the fields to be resizable, then use a continuous form.  If you don't have a lot of columns, you can get a similar effect but nothing can be moved or resized.  Older versions of Access remembered the user settings but current versions don't.  If you wanted to keep custom settings, you would need to record them in a table and reload the settings when the form opens.  Otherwise, opening the form again goes back to the defaults.

To mimic the select from the ListBox, you will need to add a column to the table and show that column on your subform.  If you define it as a Y/N field, you can have it display as a checkbox so its use is obvious.  Make sure you define the default as 0 (false) and mark it as required.  You don't want to end up with null values in this column since that would impact the selection.  Your query should include criteria that uses the checkbox.  If there already is data in this table, you will have to add the column, populate the existing rows appropriately, and then go back into design view and make the field required.  You can't mark it required if the existing data violates the rule.

Where thecheckfieldname = False

To requery the list to make the newly selected items disappear, press the Refresh All icon in the Records section of the Home ribbon.
Chuck LoweAuthor Commented:
Thanks. I'm understanding about the Y/N field. My issue is I do not allow Additions, Deletions or Edits on the Data. Since that is at the row level and not field level how do I stop them from changing other fields if I allow a checkbox?
Each control has a Locked property.  Set it to Yes to lock a field to prevent entry.  So, the only control with Locked = No would be the checkbox.
Chuck LoweAuthor Commented:
Thanks again. I'm from a heavy Sql background and mainframe world. Sometimes I get lost in Access not coding in it since 2003. I really appreciate the help, patience and quick response. I can't try it til Monday being a consultant and can't work from home. I'll let you know Monday.
Have a great weekend.
You have a good weekend also.
Chuck LoweAuthor Commented:
Is if better to use a temporary table or a query?  We have 2 Access databases. I'm using a backend (BE DB on our server network. The BE has all the queries and tables (lookup tables and tables that are appended/updated/deleted). Each person that runs the FE app has a copy of it on their network drive. Their network drive is also located in Philadelphia. We call the App the Front End (FE). All network drives are in Philadelphia. We have a few users that are on the west coast. We noticed extreme slowness when running like this for the out of state users. I'm assuming because their network drive is also in Philadelphia.  The number of records is not extremely a large amount but it seems to run slow for the west coast people. In fact there is currently only one table on the FE app. And it contains a version # that is checked every time the user initiates the FE against the BE version #.If they don't match it installs a copy of the new FE.

We are not allowed (unless extremely necessary) to have any tables bound to a form or permanent tables on the FE. .We use queries to bring in the records requested and update the rows on the BE tables.

We are allowed to if necessary make temporary tables on the FE, load them, do our work and upon exiting the form/DB, whatever the case may be, delete the table or delete all the records in the tables.

The number of concurrent users will probably be no more than 10 local and 4 remote. The number of rows that I need to work with is under 2,000 (at most) when processing.

I figure, since no production data sits on the FE, for the out of state (and maybe the local) users I can place the FE on their local C:\Drive.

I created a form (datasheet) and it's bound to the temporary table on the FE. I'm having issues with the code to bring over the records on the BE DB to the FE DB.

Do you have any examples of vba code to display the 4 or 5 fields I have on the form? Bound or boundless? I would like to start out with creating and loading a temporary table (under 2000 rows) and display that info, do my selections (clicking on a Boolean field we discussed previously), create the Audit row for the plan on the BE. I would use the same table/rows do work with on other audits and upon exiting that form (or somewhere before exiting the app) I would either delete the rows or delete the temporary table.

My issue is talking with the subform that replaced the Listbox.

Hope I didn't confuse you. Been a long day.

Thanks, Again!
BE's contain ONLY tables.  If there are queries there, they are only for technical people to use.  They are not used in the LOB application.

It is almost always better to use a query rather than a temp table.  Temp tables cause bloat.  Access cannot recover space used for internal work areas or occupied by deleted records except via a compact so each time you run the make table query, the resultset occupies a new area of memory/disk space.  The only time I use a temp table is if I am working with a huge table (millions of rows) and I am aggregating data AND that data could be used for multiple reports if I saved it locally.

Access transfers a large amount of data over the network so it shouldn't be used this way.  The first thing to try would be to move the FE's from the shared drive to the local PC.  At least, that transfer will happen only once per session and you are just left with actual data movement.   Access is constantly reading and writing to the FE during normal operations so having it on a network drive can make it slow.

Assuming your forms are bound to queries with selection criteria (which is what it sounds like), you will get better response time using a SQL Server BE.  This is because Access attempts to "pass through" all queries to the server and the server sends back only the selected records.  Of course you can defeat Access so it is important to read and understand some of the optimize for Client/server articles so you know what things to watch out for.

I'm not sure what you are trying to "talk" to the subform about.  Typically, a mainform does not talk to a subform because the mainform doesn't know what record it should talk to.  The subform can "talk" to the mainform if necessary.  Usually by populating a hidden unbound control on the main form.

Regarding the overall structure of the application, I would use Citrix if at all possible.  Remote Desktop (RDP) is also an option and will be cheaper and more likely to get approved if you only have a couple of remote users.  With Citrix, your remote users could potentially get better response time than your local users if the Citrix session is configured to avoid network traffic.  In theory, the BE and the user's personal directories could all be on the same server so there would be no network latency between the FE and BE since they are on the same computer.  With Citrix, the user's session runs on the server and only pictures of the desktop get sent to the user computer and only key and mouse strokes go from the user to the Citrix session so bandwidth requirements are minimal.

We've gone way beyond your initial question.  It would be best to close it if you have your answer and start a new thread.  If I haven't answered all your questions, please post a link to this thread or copy my answers to the new thread and we'll pick up from there.

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
Chuck LoweAuthor Commented:
Thanks. I'm aware of the inner workings of SQL Server. I was not that aware of Access issues. I appretiate the info. It gives me some things to reflect on going forward and also with my current application(s).
As for SQL Server as the BE I agree. Unfortunately that is out of my control. Access is used heavily and the organization has many,many apps over the years they depend upon written in Access.
Once again thanks for the info. It was a great help.
Changing to using SQL Server as the BE does not in any way mean that you cannot use Access as the FE.  In fact, I use Access as the FE to Oracle, Sybase, DB2, Progressive, etc. in addition to SQL Server and yes even jet/ACE.  It really is a matter of client and application needs.  The whole point of "Access" from the beginning was its ability to connect to relational databases using ODBC.  I can't say for certain but that is a pretty good guess as to how the product got its name.   It was only with the second release of Access that MS started bundling Jet.  The two are now completely fused in the minds of those who don't understand what Access is (a RAD tool NOT a database engine) and so people don't see Access and jet/ACE as separate products when in fact they are.  There are many shrink-wrapped products on the market that use Jet as their BE including a prominent tax preparation package but that use something else to build the FE.  

The Access - Jet/ACE relationship is symbiotic though.  "Access" uses jet/ACE to store its own objects so it needs Jet/ACE to run.  It just doesn't need Jet/ACE to store its data.  And Jet/ACE rely on "Access" to provide a graphic interface for creating objects.  You can of course create objects using DAO and ADO but "Access" is the face of jet/ACE so to speak.
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.