Column or field width on sub form.

[Perfac]

We always have had frustrating challenges with this one. Whether in Design view or Lay out view. "Sometimes" it work sometimes not. Using the mouse to drag the width of fields on the sub form. Exit the form and when returning the fields are the wrong size as it was before. Searching the internet I found many who needed help with this. I found the code from someone as in image 1. Image 2 shows the error with that. Someone said that it worked when he created his form and subform with "form wizzard", didn't work for me, if I did everything right.

Ruben sometimes fiddle with freeze field and save it and we almost always come right, but not me today. Do you see something in the error message or teach me the way.

[ranman256]

i think the 2nd param, is not a string, or the 3rd. Null maybe?
The 1st param IS a string.
is 2nd param a subform?

make sure theres no nulls.

[Perfac]

Hi Ranman. Eish, these things increase arthritis. I lied a little, saying recreating my form and sub form in "Form Wizard". Yesterday I only recreated the sub form. During the past hour I recreated both through "Form wizard" . The prove is in my image below. When I was half way I made the first field very narrow by dragging with the mouse. I closed the form, when reopened it was still narrow, great! When finished I tested again and then it didn't work! I do not know what I did to change the result when I was half way to when I was finished.

I will try to make the VBA work, since where I got the advice from, the person said it works. I am not sure what you are telling me. As on the image my main form is named f02Payment, subform is f02PaymentSub. I will change the name in the properties to sf02Paymentsub, and test both. The control or field that I test the width issue on has a name txt_LdgaccID. The control is bounded to a field named LdgAcc_ID007. Where could there be nulls? I copied the suggested VBA from another forum, normally it works, I assume no problem with the zero. The current error is from the Open event. I am not sure if on the close event the ctrl.Columnwidth = 2 is correct?

[Perfac]

I found something. Under the event form_Current, we have the VBA below. When not there, then the field widths can be set, when it is there it doesnt keep the field widths. Our Payment form works in a way that in Batch payments for Salaries and Wages, the account that gets debited is "Salary Control" or "Wage Control". We still want the user to select the employees name. When a creditor is paid, either one at a time or in a batch, a creditor has a "Sub ledger account number". Employees do not have "Sub Ledger account numbers", unless they have a loan with the business or trade with the business. The code below changes the visibility of fields as needed. I am working at it to find exactly why this code prevent the fields to keep the width.

[Perfac]

More tests clearly proved that the VBA above got Access not to keep the field widths. My experience tells of all kinds of challenges with the event "Form_Current". My guess it is the first If statement, I will test more. I would like to keep this function in our system but also want to be able to set field widths and see it stay that way. A person on another forum tells about users being unhappy to think that he battles to fix the setting of this problem.

[Perfac]

This was an interesting lesson I learnt today. I "cut" the first If statement, removing it from the event "Form_Current". I pasted it to two other controls after update event and all is fine now. I can set the field widths, exit and on returning, they are the way I set it. And my function works. Great!

[Gicu]

Please have a look at the module attached, I have used it for many years with no problems to keep the custom width and order of columns in datasheet forms and subforms. The nice thing is that it preserves them on individual machines so when I deploy a new version of the front-end it will keep the selections for that particular user.
To use it you just need to follow the example at the bottom of the module.

Cheers,