Access form still acting as if varchar field is char field 1

[cdck]

I have a form with text fields on it, connecting to SQL tables in the background. When the form was first created, the fields were nvarchar fields. In an effort to clean the database up, those fields were first changed to Char fields, and then to varchar fields. The linked tables have been updated since these changes occurred. However, any data entered in these form fields is stored to the table with trailing spaces padding them out, as if the fields were still char fields. Can anyone suggest a solution short of rebuilding the form?

Cheryl dc Kern

 

[genomon]

The linked tables have been updated

You have to relink the tables after they are changed for Access to "see" the changes.

Beir bua agus beannacht!

 

[dhookom]

Also, make sure you are not using input masks.

Duane
Hook'D on Access
MS Access MVP

 

[cdck]

I'm confused - do you mean I have to remove the tables and add them to the project again? I've already used the linked table manager to update all the linked tables, which is usually how I update the tables if a column has been added, deleted, or reformatted in my source tables.

If you are suggesting removing and re-adding the tables, is there any danger of this causing further complications with existing queries, forms, etc., which directly link to those tables? Will Access break those items due to its efforts to automatically rename objects where they are referenced?

There are no input masks on the fields in question, btw.

Cheryl dc Kern

 

[genomon]

suggest a solution short of rebuilding the form

Haven't tried this, but can you add VBA to the BeforeUpdate event of the text box to use the Trim() function to remove leading/trailing spaces (or LTrim or RTrim)?

Beir bua agus beannacht!

 

[cdck]

I did this as a temporary fix, but not on each of the fields - to close the form, the user has multiple options, all from buttons on the form (there are less buttons than text fields). I added the trim function as the first thing it does in the onClick for each of the buttons. This works wonderfully - but I guess my biggest concern is that I can't understand why the fields are behaving this way. I haven't found any settings in the preferences for any of the fields that would explain this behavior.

Cheryl dc Kern

 

[genomon]

What version of Access are you using?

Beir bua agus beannacht!

 

[cdck]

My apologies, I should have included that before.

I'm building the mdb in Access 2003, but it is set to save them automatically in Access 2000 file format, because most of our users are still that far behind (or that much more far behind).

Cheryl dc Kern

 

[genomon]

If you are suggesting removing and re-adding the tables, is there any danger of this causing further complications with existing queries, forms, etc., which directly link to those tables? Will Access break those items due to its efforts to automatically rename objects where they are referenced?

Create a backup, then try deleting & re-adding. Shouldn't be any problems as long as none of the table names change or tables are omitted. Also have you tried compact & repair?
Another last resort can be found here:

http://www.granite.ab.ca/access/decompile.htm

Still sounds exactly like behavior you see when the links are old (as long as there is no formatting in the form). Have you checked the format property in the text boxes to make sure nothing is selected?
Frustrating!


Beir bua agus beannacht!

 

[genomon]

One more thought: open the tables in design view on SQL Server and make sure these changes actually got saved, or that someone hasn't restored an older version somehow.

Beir bua agus beannacht!

 

[cdck]

I've verified that the form fields don't have any format selected. Also, I've already tried compact and repair, and twice checked that all the field formats in the SQL server still say varchar.

I tried the decompile option, and it appears to have corrected the issue, based on cursory testing.

Thank you all for helping figure out this odd behavior!

Cheryl dc Kern

 

[genomon]

YAY!!!

Beir bua agus beannacht!