"Compact and Repair" that ACTUALLY repairs

[wjoc1]

Hi,

Im using Access 97 and I was just putting the finishing touches on a small db when the user made some minor changes to some of the information being captured. No fields changed but some of their properties were altered, I put in new input masks in some cases, changed the field size slightly stuff like that.

Now none of the forms in the database that are based directly on these fields and tables reflect these changes. The only way I have managed to reflect the changes is delete every single control and re-enter them on the form. If I have to do this for every control in every form in the db it is going to be a nightmare.

Surely there must be some other way of refreshing these controls. Surely?

Liam

 

[neileg]

When you create a control on a form it inherits some properties from the table or query it is based on. This is not a recursive procedure, just a one off. The result is that if you change these properties in the table, they don't feed through to the forms.

This is one reason why I prefer to leave my tables free of masks, formats, validation, etc, and apply these in the form.

 

[wjoc1]

Why why why!? What possible reason would Microsoft have for setting it up this way?

What you're saying is that if I create a form containing controls linked to fields so that a user can enter details into a background table then I alter an attribute of one of these fields in the table the change is not represented in the form/control which is directly and exclusively tied to this very table and field!!?? What kind of db sys is this?

I don't wanna say bad things about MS Access in a dedicated forum but really the more I see of this piece of "software" the less and less I like it. This is just stupid.

Maybe someone can explain to me some trechnical reason? Just to appease me

Liam

 

[chris_dono]

form level validation is the way to go, but microsoft put the one-off in there to help out if you had used table level validation, nothing more.

If the design hadn't changed, then I guess you wouldn't have found this out ??

A good quote that may be applied here:

"Measure twice, cut once"

 

[wjoc1]

form level validation is the way to go

Apologies for being so sticky but is it!? Is it really the way to go? In this particular db for example I have a form for adding info to the table, a seperate form for editing/deleting info and a third for viewing the information in the table. I need these seperate forms for various constraints etc. associated with each of the above actions.

Now if form level validation is the way to go, I have to go in and change all the controls on three seperate forms, emulating the exact same changes on the controls in each form. That really is serious "double-jobbing" to me.

The controls in each form are all based on/bound to the same fields in a particular table.

From even a logical/common sense point of view you go in, change the attributes on the fields in the table; (the root of this particular structure) and these changes are reflected everywhere? This just simply makes sense to me and seriously reduces the workload involved?

I'd be interested to hear what people think on this one?

Liam

 

[Parker]

I'd be interested to hear what people think on this one?

Liam

First; Don't feel bad about Knocking MS in a dedicated forum. I do it and so do many others if you read the posts. One way to clear up short falls is to complain and/or discuss them other wise haw would anyone learn from issues such as this.

One reason for MS strst. on this may be for the ease of compatability with other DB structures. I don't know because I havent investigated. But I recently learned a similar lesson and as Niel pointed out it may be better to;

leave tables free of masks, formats, validation, etc, and apply these in the form.

 

[wjoc1]

Gentlemen,

Lesson learned the hard way I'm afraid. I still think it's crap, a terrible design feature of Access and that I'm right What should have been 5 minutes work has now turned into 5 days literally.

Liam

 

[chris_dono]

Apologies for being so sticky but is it!? Is it really the way to go? In this particular db for example I have a form for adding info to the table, a seperate form for editing/deleting info and a third for viewing the information in the table. I need these seperate forms for various constraints etc. associated with each of the above actions.

Now if form level validation is the way to go, I have to go in and change all the controls on three seperate forms, emulating the exact same changes on the controls in each form. That really is serious "double-jobbing" to me.

The controls in each form are all based on/bound to the same fields in a particular table.

From even a logical/common sense point of view you go in, change the attributes on the fields in the table; (the root of this particular structure) and these changes are reflected everywhere? This just simply makes sense to me and seriously reduces the workload involved?

I'd be interested to hear what people think on this one?

Liam

how about a function that does the checking for you before the record is saved or when the data is entered ?

much easier to maintain, more powerful and very easy to implement

 

[Pat Hartman]

Perhaps your design is at fault?

I need these seperate (SIC) forms for various constraints etc. associated with each of the above actions.

- If you had used only a single form instead of separate ones, you would have had only one change to make. You can specify when you open a form that you want it opened in Add mode or read only. Read the help entry on the OpenForm Method and Action. You could have saved yourself a lot of work. As someone pointed out earlier, the passing of some of the properties when you create forms/reports was intended as a convienence.

I do believe however that you will get your wish when you upgrade to A2003. That version includes some developer friendly enhancements. And don't hold my feet to the fire but I think this is one of them. Also, as with AXP, you can work in a previous db format (but not as old as A97). That means that the developers can upgrade even if the users don't.

Even A2K has certain improvements. The feature is called Name AutoCorrect. However, be careful because in the initial build the feature caused other problems. So, you need to apply the most recent service packs.

You do realize that A97 is over 7 years old? I think that the fact that so many organizations don't feel the need to upgrade even though there are THREE newer versions out there says something about the product and I think that something is positive.

 

[neileg]

If we are knocking MS products, why does Excel interpret a valid formula as text, just because there's no = at the start? Supercalc 2 could cope with this back in 1984 when I started using it.

 

[wjoc1]

If you had used only a single form instead of separate ones, you would have had only one change to make.

Pat, I agree. In a perfect world that'd be the answer. However there were design constraints and I don't find one form in Access is flexible enough to cover all the different scenarios between adding/editing/viewing etc.

Just for example on the viewing form I have unbound fields in the header which offer the user various search options on the fields (i.e. essentially they build the query themselves) and the results are displayed on the details section.

I need different forms. They just aren't flexible enough to use for everything.

Granted, companies are slow to upgrade but that may be more to do with cost constraints in respect to these packages rather than feeling that they could do with a serious upgrade People make do, simple as that... but that don't make it right

Liam

 

[Pat Hartman]

It interprets it as text because you may actually want the cell to show a text version of the formula! DUH! Perhaps EXCEL should have used the equal to say "display the text contents" rather than "treat the contents as a formula". But given the equal sign's use in math and logic it makes more sense the way it is currently used. The "=" says "here comes a formula". I think if you analyze your average spreadsheet, you'll find far more columns with "text" than "formulas".

 

[neileg]

It interprets it as text because you may actually want the cell to show a text version of the formula! DUH! Perhaps EXCEL should have used the equal to say "display the text contents" rather than "treat the contents as a formula". But given the equal sign's use in math and logic it makes more sense the way it is currently used. The "=" says "here comes a formula".

We're talking about default behaviour here. As I said Supercalc 2 had a default behaviour of accepting the entry as a formula. It would be just as logical to require the apostrophe for text fields.
In any event, if Excel can interpret your default text entry as either a date or a number, it will. I need to deal with blocks of data that have text labels in the form 9X999. When my alpha character is an E, e.g. 1E234, Excel is happy to take that as exponential notation for a number. If it is 1K234, it treats it as text. That's not consistent, and it's certainly not convenient, because Excel actually converts my E label into a number so the cell contents don't even look like the data that was entered.

I think if you analyze your average spreadsheet, you'll find far more columns with "text" than "formulas".

Not for me, and not for most people in my field (accountancy). Text entries fo me are mostly row or column labels.

 

[Pat Hartman]

I put text in parentheses because I ment it to be interpreted as non-formula rather than specifically text as opposed to any other data type. I know that most spreadsheet columns contain numbers of some sort. And that leads us to the bias in interpretation. If a column COULD be interpreted as a number, it will be. That makes its differing interpretations of 1E234 and 1K234 at least rational. Again, it does this to save you from having to start every text entry with an apostrophe. I would guess that the situations where a string could be interpreted as scientific notation are small enough to not sway the designers into forcing all text to start with an apostrophe.

 

[Len Boorman]

If we are knocking MS products, why does Excel interpret a valid formula as text, just because there's no = at the start? Supercalc 2 could cope with this back in 1984 when I started using it.

Seem to remember that Lotus could also detrect a formula. Can't quite remember if Visicalc could dothis interpretation. Was a while ago now though. Seem to remember that these products also sharpened up the algebra and made you understand the syntax.

Len

 

[o1110010]

This is one reason why I prefer to leave my tables free of masks, formats, validation, etc, and apply these in the form.

Ohh. I did not hear that lesson yet. Is there any time when you make these attributes in the table instead of the form? Would anyone recommend going through current databases and un-doing the specifications done at the table level? (Perhaps small databases?) Or just keep going until a problem arises?

 

[Dugantrain]

Lotus .123 detects mathematical calculations by default. So if you're typing in 03/04 and mean for it to be a date, it would automatically update to .75 with the apostrophe being the way to get around this. Maybe it's because I started with Excel, but I abhorred this default behaviour when I used .123.

 

[Parker]

Seem to remember that Lotus could also detrect a formula. Can't quite remember if Visicalc could dothis interpretation. Was a while ago now though. Seem to remember that these products also sharpened up the algebra and made you understand the syntax.

Len

Yes Lotus did do this and I agree with Neileg. Excel is a number cruncher and should, therefore be designed to crunch numbers by default. Problem is that Access has always been considered by MS as an additional product and so Excel has to double up.

Lotus SmartSuite always included a powerful db MS office always charged extra for the privilege.

 

[neileg]

Lotus .123 detects mathematical calculations by default. So if you're typing in 03/04 and mean for it to be a date, it would automatically update to .75 with the apostrophe being the way to get around this. Maybe it's because I started with Excel, but I abhorred this default behaviour when I used .123.

Yeah, it's what you're used to.

However, if you type 03/04 into Excel, you actually get 03-April displayed and the value saved is 03/04/2004 (at least on my UK installation of Excel 2000). Now if I wanted this to be a financial year indicator, then this isn't what I want, either, so I have to use the apostrophe. I suspect that the behaviour would have been different if the USA used / as a year delimiter and the date format was the same as the UK.

I love all this shouting into the wind. It makes you feel better even when you know that it makes no difference whatsoever!