Form to Edit Records only

[94Sport5sp]

Hi:

I have created several forms to allow users to edit existing records from different tables. The user is not allowed to delete existing records nor are they allow to add records. I handle that with the form property Allow Edit/Addition/Deletion set to yes, no, no. On the form I have an unbound field to display yesterdays date, a calculation of Now() -1, and this unbound field is causing me problems for an edit only form. With the form property Allow Edit/Addition/Deletion set to yes, no, no the unbound field cannot calculate yesterdays date. The unbound field is Null. If I change the form property Allow Edit/Addition/Deletion set to yes, yes, yes then the unbound field calculates yesterdays date but the users can add records. Any way around this? Am I missing something?

Thanks

 

[94Sport5sp]

Hi:

I do not understand why on edit only forms the unbound fields do not calculate. So I have changed my forms to allow additions of records and then on BeforeUpdate I test if fields (which the user cannot change) are zero. I can then Undo the addition. Not a very elegant way to handle the problem but it works.

 

[mdlueck]

I have created several forms to allow users to edit existing records from different tables. The user is not allowed to delete existing records nor are they allow to add records.

One solution to that requirement would be to use a read-only form listing the records in a multiple-items style. Then once the desired record is located, bring that one record into edit mode in a edit record form which is unbound from the table.

VBA code will need to populate the form fields. Then VBA code harvests the values from the form and places them back in the database table if the person pushes the commit (save) button. If they wish to rollback (cancel) their edit, then simply close that edit record form.

 

[mdlueck]

I do not understand why on edit only forms the unbound fields do not calculate.

What calculation were you expecting Access to do? In most cases, you are in complete control with unbound forms. Unbound vs bound is comparable to automatic and manual transmissions in autos.

 

[94Sport5sp]

Hi Michael:

The unbound control displays yesterdays date. It is a simple =Now()-1 for control source. With the form properties set to allow Edit/Addition/Deletion of records the date display properly and I can use the date in my code to control what the users see. With the forms set to Allow Edit = yes and Additions/Deletion = no the unbound date field is Null and , of course, I cannot use the date field in my code. The table attached to the form has 7 fields that the user is not to change and 3 fields which the user can change. Everything woks fine except I cannot stop the addition of records, which the user has no need to do.

Thanks for your thoughts

PS:
The complete process, if it helps understand what I am doing, is really a 3 step process. I have a control where the user selects a department to be processed. I pickup the department selected, add to it yesterdays date (the user will only be working with yesterday) and display the records to be changed. The form only allows the user to select one of the three fields they can edit. When done the user selects a different department and the process repeats. When done the user close the form. If necessary the user can go back and make more changes to the three fields until tomorrow when a new set of records become available to the user.

 

[mdlueck]

The unbound control displays yesterdays date. It is a simple =Now()-1 for control source.

You should be able to place a call in Form_Load() which will default the unbound date field control, and then allow users to change the date if necessary. I am using the DatePicker support in A2007. (Properties sheet, Format tab \ Show Date Picker)

A long time ago when I was using the DatePicker OCX control, I used the technique to default the control to Now() and then blank it out, if I wanted to not have it set, but when enabled the fact that Now() had been its value at one time (when the form was first opening) it would snap back to today as its first offer. So, with that control, two LOC's to set the default / blank value.

With the forms set to Allow Edit = yes and Additions/Deletion = no the unbound date field is Null and , of course, I cannot use the date field in my code.

I have never messed with those properties! I found those, Form properties \ Data tab. I leave all of them set to Yes. Does not matter on unbound forms as they are "unbound" after all!

 

[94Sport5sp]

Hi Michael:

Sorry, I am still not being clear enough. The form is bound to a table and the user will be editing certain fields in the table. On the form I have several unbound fields, some to display calculated data and some for user input. I also have a combo box bound to a different table to list departments. The user selects a department they wish to edit, I filter the data the form displays by the department and the calculated date. The calculated date is displayed so the user knows which data they are modifying. Now, nothing prevents me from using VBA to calculate the date to be processed and then displaying it in an unbound field which does not have any control source set. I would just like to clarify that access considers unbound fields with calculations as being Null when the form is set to Edit only. Does not make sense to me.

Thanks

 

[mdlueck]

I would just like to clarify that access considers unbound fields with calculations as being Null when the form is set to Edit only.

Edit only, meaning that you may not add new records?

On my read-only multiple items forms I have some unbound static reference / context fields. I simply set those field's Value property to the desired value, and it gets displayed.

For edit forms I only use unbound forms for them.

I would think that if you are populating the calculated fields (I will call them application context fields), then I would think you simply set the field's Value and that be that. What do you mean that Access considers the fields null? What do you think the field's Value should be?

 

[94Sport5sp]

Hi Michael:

Thanks for sticking with me.

[QUOTE Edit only, meaning that you may not add new records?[/QUOTE]

Yes user may "Edit" fields in the records but cannot add new records nor can they delete existing records. The data I am working with is historical and is being used for reports, after user makes some changes, to management only.


[QUOTE What do you mean that Access considers the fields null? What do you think the field's Value should be?[/QUOTE]

The unbound field contains =NOW()-1 as control source and has a date format applied. I expect the unbound field to display yesterdays date in the format I requested. AND, it does as long as I set Allow Additions to be yes.

So just to be clear, I make no changes to the form except to Allow Additions is set to NO, save the form, and the form behaves. The form displays and the unbound date field displays yesterdays date and I can proceed with all the thing I want done on the form except the user can add records. So, I change the form, the only change, to Allow Additions = NO, save the form and unbound date field is empty, does not display anything, has no value.

I was in meetings most of the day, dull and tedious, and I had time to think. With the form set with Allow Additions = NO, the unbound date field displays no value. A MSGBOX request to display me.SalesDate (name of unbound date field) produces a meaningless runtime error with error number -2456789439 ( a big negative number) and executing halts. Debug flags the MSGBOX saying that is the problem. Any reference to me.SalesDate produces an error and eventually I got Access to say "Error 2427 You entered an expression that has no value". Now I just have to find out what is stopping Access from calculating yesterdays date for that control.

At any rate more testing and more info later.

Thanks

PS: I am working with A2003 in case that makes a difference.

 

[mdlueck]

... AND, it does as long as I set Allow Additions to be yes.

...

So, I change the form, the only change, to Allow Additions = NO, save the form and unbound date field is empty, does not display anything, has no value.

Sounds like you are forcing Access into corners by fiddl'n with those settings and now it is mad and going to bite back.

I would suggest:

1) Putting all of those Allow settings back to Yes
2) Go totally unbound for your Add/Edit forms
3) Use ADO objects in order to SELECT records to be edited / gather field values and check in into the table with INSERT/UPDATE statements. You may find some sample code of all three queries here:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

4) And for calculated / read-only fields, set them to Locked and No Tab Stop.

 

[94Sport5sp]

Hi Michael:

Thanks for the links and the suggestion.

For now I am handling the problem by canceling the update on a new record. I do not allow the user to access the dept field so if the dept field is zero then cancel update and move the user back one record.

Thanks