More Reliable/Efficient: Setting form control value or using query to update table?

[Monterey_Manzer]

Hi All,



I'm curious which method is the better way to set/update values in a table. I have a form in which users are filling out data including start and end dates. In most cases the end date can be calculated from other pieces of data present (i.e. start date and time duration). In most cases I simply use a button and some VBA code to set the value of the control in the form. However, in some cases the time duration value is null and in those cases I prompt the user to enter this value and then use a couple UPDATE queries to set both the time duration and end date values directly to the table. I finish this up with a Refresh of the form to display the calculated end date. I was just wondering if there is one method that is demonstrably better than the other.

Thanks!
Ryan

[June7]

Are you using InputBox to get the user input?

Don't understand why you use UPDATE query. If the record and field are already available on form, just set the field value with the user input as you do with the code behind button.

Could just nag the user with a message box, set focus on the control, and have them input there.

[Monterey_Manzer]

I do use an InputBox to get User Input. The value the user is entering is kept on a separate table and for the vast majority of records it already exists. That is why I don't have a control on the form for that field. Honestly I really have to restrict what data users can manipulate in order to keep the data accurate.

I then use an update query to update the time duration data in the table where what is stored. In my code I go on to use the variable I assign to the InputBox value in the VBA code that sets the end date. However, I had thought of putting in a second update query that sets the calculated end date value in the table directly rather than going through the form and I was wondering what the merits of each approach would be.

[June7]

Using UPDATE to edit record in table that is also open on a form where an edit may have already been initiated could cause issue. This might be like two users trying to edit same record at same time. Seems I ran into that before and had to modify processes.

[Monterey_Manzer]

Perhaps I should have the time duration as a locked field on the form and when my code triggers the request for input, rather than having an InputBox pop up I could set focus and highlight the duration field. I could then trigger the calculation of the end date in the After Update event for the duration field. Do you think that would be more stable?

I would unlock the duration control to allow the user to enter the data and then lock in again during the After Update event.

[June7]

Yes, I do that same sort of process, except BeforeUpdate might be better event for calculating and populating the end date.