Question Update Record Through Form On Load or On Open

[Trevor G]

How can I update a record in a table through a form event when the initial form of the database is opened, which is not bound to anything.

Form name is frmStart, the table to update is

Table Name is quotations

Field to check is quotation_status

If the status shows "Pending" then change to "Quotation_Expired" if a field named "Pending_Date" is less than 30 days from today.

I basically have a list of quotations that when the database is opened I need the status to be updated before someone looks to activate and work on the records.

Status will sometimes change when qoutations are inactive, so using dates sometimes is necessary, if a date period elapses and nothing has happened.

 

[PNGBill]

Could you have the open form linked to a macro that all you want and then opens the form??

 

[Trevor G]

Hi Thanks for the reply, I thought the form events would be the correct thing. Not so sure about wanting a macro as this only runs something similair to code, without error handling processes.

 

[Lightwave]

Hi Trevor

I'm not totally answering your query but almost making an alternative suggestion

If you have a start date and then have another field with the expiry date. I would simply make a label on the form that could be triggered on load that runs something like

If
Me.dateexpiry >= date()
labelname.caption = "pending quotation"

Else

labelname.caption = "quotation expired

End if

That way anytime you go into the form it will look to the system date and compare it to todays date then you can get Access to format a big Quotation Expired in the label on the form. (Top Secret or confidential or anything really)

You can also make the default value of the end quotation date to be 30 days later than the start date triggered when a start date is added.

This will still allow you to manually alter the quotation end date if you so wish.

The problem with recording a quotation as expired in its own separate field you are duplicating an attribute that already exists. This is because the field with the end date in it is exactly the same information. In fact it is a richer source as it not only tells the status of the record ,it also indicates the date that status will change and if you know the system potentially can indicate when it started.

Thus a status field wouldn't even be required for querys as it can all be calculated from the actual expiry date field. Thus you are really only using the field to give a big flashing sign to the users that need some prodding.

I may be missing something but I can't see any downsides to it and quite a lot of positives.
No duplication of data
Calculates on opening of form
Reduced data storage
Still allows for calculation of pending records.

 

[pkstormy]

I personally would avoid any code (or macro) which updates a date/time/status recordset on the form's loading or opening event. I've only done this in rare situations and did it sparingly. Instead consider something as triggering the event to update off of an event such as an OnClick event of a button or AfterUpdate event of a field. You can really get into problems when you update data (especially creating new records) automatically when a form opens.

You may also want to consider a form which opens and shows all 'pending' type records where again, the user clicks a button to update the data. This would be preferable versus basing the updating of data only if the user happens to open that form. If the user doesn't open that specific form for days/weeks, it can cause issues.

If you need to 'automate' the updating of data based on some kind of date and/or status type field, you can also easily create a 'non-user' type mdb which automatically updates data when it is opened and then schedule this mdb to open on a nightly basis via the Task Scheduler. (ideally though, you could create a vb script to update the data directly to the MSAccess table and schedule the vb script to run nightly and avoid even opening an mdb file.) Or even more ideally, have the data on SQL Server and schedule a DTS or SSI package to run nightly.

Updating date/time/status sensitive data based on when a user 'might' open a specific form is not a good idea.

Also consider utilizing Conditional Formatting on a field to make the data show in red (or some other color) based on a status or date sensitive value.

 

[PNGBill]

If you have a TblQuote then this presumably holds such information as QuoteNum, DateRaised. Other specific data may be in TblQuote or TblCust or TblTrans.

A quote is not opened as such. A form is opened to view customers transactions - may show quotes for that customer and flaged as current, expired etc depending on the DateRaised & your default expire period - you could have a field in TblQuote for specific num of days for each quote.

Another form will only show all Vaild Quotes or all expired quotes over a given period however you set your query/sql but at this stage the quote itself is not "locked" just no longer visible in that form.

No dates changed.

You could have another form that identified Quotes about to expire and give you the option to extend (edit validity days) or ignore.

If you could password protect or user filter the forms then not all susers will be able to view expired quotes.

 

[Trevor G]

Thanks for the replies and I am sure it can work. The concept is that when the database opens the initial form will do a check to alter quotations that are made prior to someone looking at the list so then they can do the necessary work, if required, if the status falls out of the parameters then the record will be altered, this is the code which I have behind the form event on Open, but it tells me I have to few parameters, and gives me Error 3061.

CurrentDb.Execute " UPDATE tblQuotation " & _
"SET Status = '[QuotationStatus]' " & _
"WHERE Status='Pending' AND [QuotationDate] < DateAdd('d',30,Date());"

 

[JamesMcS]

I'm not sure but I don't think you need to encapsulate [QuotationStatus] in 's, won't that make Access just put "[QuotationStatus]" in Status?

Just a quick one - I'm sure that this method is the right way to go, rather than a macro. Did you write the statement yourself or use the query designer, then copy and paste? I'm rubbish at getting all the syntax right myself so I usually create graphically, switch to SQL view, then just copy and paste into VBA (and I usually use docmd.runsql rather than execute).

 

[Trevor G]

Hi James,

I wrote the code, but it has issues, the quotes didn't make a difference, I am sure I can get it to work, just need a little working out.

 

[JamesMcS]

SQL is my biggest bugbear. Does it work if you try the docmd method, and design the query graphically first?

 

[Trevor G]

I will give that a goe and let you know.