Forms with editable subform on some columns, best approach?

[kiwipeet]

Hi. Apologies if this has been done before. I'm unsure how to tackle this as it spans several aspects.

I've been given the task of updating a 20 year access db owned by our local library. It contains history about local streets. I've moved the back end to SQL Server and am trying to build the front-end in Access. The existing db is a mess, because of no safety or control on editing, so lots of fields have been fumble-thumbed.

I'm a SQL DBA, So I've moved the back end to SQL (there was a few reasons to do this), and enforced some referential integrity. But I'm not a Forms developer, so i'm fumbling my way through the next bit.

I have a main form with a subform. I want to open the form in read only, and allow editing after a user clicks edit. Something like this.

The subform holds citations and references to source information. The subquery return records from 3 tables. 2 of which are reference tables.

With my early tinkering I found I could edit the reference data directly through the subform, which is not ideal. It just leads to more accidents.

I want the user to be able to add a new citation, and pick the reference codes from drop downs. They should not be able to edit the reference data directly through the subform. So I'm now getting into unfamiliar territory, and it's making me nautious.

Given the list of challenges above what is the preffered/reccomended approach?

Edit: At a high level, Is what I'm wanting to do achievable? Are there any highlevel design decisions i need to make now before i go too far?

Cheers

Pete

 

[pbaldy]

You can set the Allow Edits, Deletions, and Additions properties to No. If you want to toggle them, they can also be set in VBA. So behind an "Edit" button you can set one or more to True, in the Current event set them back to False. The Current event fires when they change records.

 

[plog]

I think you've built your subform incorrectly. It just needs to be based on the Source Information table (you shouldn't use spaces in table nor field names--just makes coding and querying that much difficult later on).

On that form you make the Loc_Abbr and Type_Abbr fields drop downs. Those drop downs use the Loc_Abbr and Type_Abbr but display the values from their lookup tables. You can then limit drop downs to just the list. Here's a link about how its done:

MS Access 2003: Bind Combo Box to Primary Key but display a Description field

This MSAccess tutorial explains how to bind a combo box to a primary key (ie: autonumber field) but display a description field in the combo box results in Access 2003 (with screenshots and step-by-step instructions).

www.techonthenet.com

 

[Pat Hartman]

I would also enforce RI for all relationships to eliminate some types of errors. If the existing tables contain RI errors, Access won't let you define the relationship so I'm assuming that SQL won't either so fix the data first. If there are obvious typos in the fields you want to be combos, fix those errors as well.

The Access default for most versions is to define AllowZeroLength string as Yes. This is really poor practice and MS does it to "help" the novice. The upshot of this poor decision is to allow a text field to be empty when it should never be empty since a ZLS is a character. I would go through and change this property on all text fields. Unless I want a field to have an actual default value, I always set the default to null for all data types. ZLS and Null are not the same thing and you will get unexpected results if you have a combination of Null and ZLS for text fields. They "look" the same.

As plog suggested, the properties of the combos, can restrict entries to prevent users from entering items not in the list. When you do this, you will need to create additional forms that an admin can use to add new items to the combo. Personally, I NEVER allow users to add items to a combo on the fly (which the property settings already allow and that seems to be what is happening to you). That just encourages them to enshrine their typos.

The names are problematic. I would have fixed all the column and table names BEFORE moving to SQL Server. It would have been easier in Access.. At this point, you might as well leave them. They will be annoying but you can work with them by always surrounding the non-conforming names with square brackets. Be veeeeeeeeeeery careful of any column names that are the same as the names of properties or functions. Me.Date or [Date] will be fine in code/queries but Date will be today's date, NOT necessarily the Date column in the table in some contexts.

 

[kiwipeet]

I think you've built your subform incorrectly. It just needs to be based on the Source Information table (you shouldn't use spaces in table nor field names--just makes coding and querying that much difficult later on).

Thanks for your reply. I'm familiar with naming conventions and use of square brackets.

 

[kiwipeet]

I would also enforce RI for all relationships to eliminate some types of errors. If the existing tables contain RI errors, Access won't let you define the relationship so I'm assuming that SQL won't either so fix the data first. If there are obvious typos in the fields you want to be combos, fix those errors as well.

Yes i've scrubbed the data, and enforced constraints in SQL.

The Access default for most versions is to define AllowZeroLength string as Yes. This is really poor practice and MS does it to "help" the novice. The upshot of this poor decision is to allow a text field to be empty when it should never be empty since a ZLS is a character. I would go through and change this property on all text fields. Unless I want a field to have an actual default value, I always set the default to null for all data types. ZLS and Null are not the same thing and you will get unexpected results if you have a combination of Null and ZLS for text fields. They "look" the same.

Thanks for the tip, I'll no doubt come back to this.

The names are problematic. I would have fixed all the column and table names BEFORE moving to SQL Server. It would have been easier in Access.. At this point, you might as well leave them. They will be annoying but you can work with them by always surrounding the non-conforming names with square brackets. Be veeeeeeeeeeery careful of any column names that are the same as the names of properties or functions. Me.Date or [Date] will be fine in code/queries but Date will be today's date, NOT necessarily the Date column in the table in some contexts.

Yep. Solid advice with names and reserved words. I bear the scars of mnay years worth of being burned by this stuff

 

[kiwipeet]

Thanks to all who have replied so far, Lots of handy tips.

I think what i'm looking for is higher level reccomendations. Is what I'm proposing doable using straight forward techniques?
Looking at the whole requirement, would it lead you to take a different approach? For example, A looong time ago I built a form that selected/cached a record set. Do I need that level of wizardry? Is linked tables via ODBC ok?

Are dropdowns within a subform easily doable? Applying read only/editable permissions per field? Is my fundamental approach ok?

Cheers

Pete

 

[MajP]

First of all the answer is Yes, because anything can be done with some work. Forget all these complex ideas from loong ago you are making this overly complicated. Your questions are kind of all over the place, but I will take a guess.

In your subform "Source information and Location" comes from a joined table in the query. If the user edits that then all records linked to this field are altered. You do not want that, but you want them to select this information. If this subform is for data entry then that should not be a text box but a combo box. I assume you are storing the foreign key for the source information. So this should be a combo box that displays the Source information but is bound to the source information ID and stored in the data table. The user can try to edite the display, but it will not be possible because they can only pick from the list.

 

[Pat Hartman]

The picture of the schema does not show RI being enforced.
The two lookup tables seem to have a text value as the PK. There is nothing inherently wrong with this. I don't do it and neither to most developers but it isn't wrong. Another potential reason for your update problem was alluded to by MajP. If you join the main table to the lookup tables (via left join I hope) and select additional columns to display, you MUST set the locked property of those controls to prevent accidental update. I use this method frequently when the lookup table has additional columns that I want to show. In a single view form, you can do this by writing code to copy the additional columns from the RowSource of the combo to unbound fields but in a continuous or DS form, this won't work and the join is far better. Just set the locked property of the fields you don't want the user to update.

I've been developing with Access since the early 90's. I was working in COBOL and someone suggested Access for a particular app. Once I found that I could create bound forms that actually updated the DB2 tables on the IBM mainframe, I was sold and haven't looked back. This is the most efficient development environment I have ever worked in. Access is of course not a general purpose language but what it does, it does exceptionally well especially if you can bring yourself to do things the "Access" way.

My applications almost always use ODBC BE's - SQL Server, Oracle, DB2, Sybase, etc are just a few of the BE's I've used with Access. I use whatever the client supports for his other applications. All of the above work just fine as long as you have current ODBC drivers. Bound forms work 99.9% of the time as long as you use good client/server techniques This differs from many of the examples you see where the forms are bound to tables and filtering is used to get to the records you want to work with. Because one of the objectives of using a real RDBMS as the BE is data retrieval efficiency, this technique defeats the purpose. An Access form becomes visible as soon as enough data has been downloaded from the server to fill the first record. Access then sits quietly behind the scenes with a straw sucking down data until all the rows have been downloaded. Obviously, the larger the number of rows, the worse this is for the network. You really want the server to do the heavy lifting and return ONLY the rows/columns needed for the immediate task. Therefore, you bind your forms to queries and the queries have selection criteria that return one row. If I don't have an easy way to do that, I create a search form that builds an SQL String and returns a recordset to an intermediate list type form. Then the user double clicks on the row he wants to update and the edit form opens. A lot of the poo pooing about bound forms comes from people who converted existing Jet/ACE apps that used the filtering techniques and then wondered why the Access app was slower when bound to SQL Server tables than to the original Jet/ACE tables. In the 25 + years I've been developing with Access, I can count the number of unbound non-search forms I created on one hand and none of them were edit forms. They were always list forms generated by searches.

One thing that can help if you are experiencing slowness with complex joins is to create views on the server and link to the views. Make sure they are updateable and they work just like tables. The only pass through queries I use are ones that are doing bulk updates, especially deletes. On very rare occasions, I have had to resort to stored procedures for very complex reports.

But, always start with linked tables and standard Access querydefs with bound forms. Then optimize if you have a problem. Funny thing about the QBE (query builder), when I was working with COBOL and embedded SQL in the 15 years prior to Access, I used to dream about a tool such as the QBE to take away the tedium of typing and typos when creating SQL strings. Despite this "life-long" dream, it was difficult for me to adopt QBE. It is quirky and limited in some ways but that doesn't stop me from using it to build the basics of a query with the select and join parts. Then if the WHERE clause is complex, I switch to SQL view. This is of course where the QBE is the weakest. I keep hearing rumors of improvements in the not too distant future though.

There are a couple of gotchas when you switch to ODBC as the BE. One of those is default values. If the defaults are defined at the table level, they will not populate until the record is actually saved so you do not see the values on the form as you are creating a new record. If this is a problem, you have to move the defaults to the form.