After Update event help

[noaccessguru]

Hi All,



Hoping someone can help me out! (I’m using Access 2010)

I have a Form where a user will enter information, and I want the ‘Status’ field to update based on the selection of ‘Type’ (which is a Combo Box.)

So if a user selects the Type ‘Permit’ I want the Status field to automatically update to ‘Open’.
If they select the Type ‘Concession’ I want the Status field to automatically update to ‘Closed’
If they select the Type ‘Trial’ I want the Status field to automatically update to ‘Closed’

How do I write this in VBA as an ‘After Update’ event??

I seem to be getting stuck



Also, one additional thing, can I have the ‘Status’ automatically update to ‘Expired’ if the ‘Expiry Date’ field has passed? How would I write this?

(Thanking you in advance.....as I am not very good at VBA yet!)

[June7]

Why you do even need the Status field? If Permit always means Open and Concession/Trial always mean closed, an expression in query or textbox will serve:

IIf([Type]="Permit", "Open", "Closed")

[noaccessguru]

Thanks JUne7, but please excuse my ignorance....again Im not a master with Access!

So I don't need to display that field at all on the data entry page you mean? then in which field do I put that code? (I hope I am making sens)

Eventually, the user will come in and 'Edit' that record and change the 'Open' ones to Closed etc when they are ready.

[June7]

You want to allow user to change the Status regardless of what the Type is? Okay, then need field in table. Allow the code to set Status only if Status field is null. Put code in the Type combobox AfterUpdate event. In the event property select [Event Procedure], click the ellipses (...), type code in the procedure.

If IsNull(Me!Status) Then Me!Status = IIf([Type]="Permit", "Open", "Closed")

[noaccessguru]

BEAUTIFUL!!! Thanks June7, this is exactly what I was after and works great!

Thanks alot of your help

[noaccessguru]

Hi June7 (hopefully you see this)

In addition to this, I have an 'Expiry Date' field, is it possible to make the 'Status' field in the table update to 'Expired' once the date has passed? With an On Load event or something?
If so, how do I write this?

Not sure if it is posible or not..or should i be creating an update query to update this field and use a macro or code or something to execute the query when the database is opened?

[June7]

Yes, that is possible. Like:

If Me![Expiry Date] > Date() Then Me!Status = "Expired"

But that will only run when for the specific record opened in form. If you don't open a record for weeks after the expiration then data will be way out of date and your reports may be in error.

So you probably want to run a procedure daily that would update entire table. Do you have a form (like a Main Menu) that opens automatically when db starts? Can have code in that form Open or Load event if you want it that automated or a have a button:

CurrentDb.Execute "UPDATE tablename SET Status='Expired' WHERE [Expiry Date]>Date();"

[noaccessguru]

Yeah I have a form (FrmHomePage) that opens when you open the database. So I would ideally would like it to run when this Loads as I wont be the one using the database as much so dont want to have an extra step where another button is required tp be pushed.

So I just need to copy the code exactly as you have written it above 'On Load' event (in VBA) for this form? (obviously updating with my table/field name?)...or is this a code for a Macro or Expression Builder?

(again sorry!)

[June7]

That is VBA. There is macro equivalent but I don't use macros.

Minor but critical correction, use apostrophes around Expired instead of quote marks. I have fixed the post to reflect.

[noaccessguru]

Awesome thanks June7.....I was getting an error before when I was copying it into VBA, but it seems to be working a treat now!

Thanks again!