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

 

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