[ms office - 2013] Access Field Dependencies

[GRitchie]

Hello,

I'm creating a database (as explained in an earlier post) and I have a drop down field for a 'status'.

It's simply 'Open' or 'Closed'.

 

When the staff member changes the status to 'closed' I want another field (completion date) to automatically fill in the date and time that it was closed.

 

Is this possible?

If so, how?(!)

[SimoninEaston]

Just to get you started, (which is another way of saying, 'I don't know'...) I would start be going to the properties of the form you're using and then do something with one of the Event properties for the combo box used to select the Status - there's one of interest - On Exit, so your code should say something like, "on exiting this control, check to see if the contents are a, then do x, if b do y..." sorry I can't help with the code - Google is your friend!

[LosOjos]

Macros. You'll want to look at the status box's 'Change' event. Click the elipsis next to the 'Change' property (under the 'Events' tab) when in design mode.

 

You'll need to change the object names (cmdStatus and txtDateTime) to match the actual names as defined in your form, but this is a very basic way of doing it:

 

Private Sub cmdStatus_Change()
   If cmdStatus.Value = "Closed" Then txtDateTime.Value = Now()
End Sub

[SimoninEaston]

Oh, and the VB class/property of interest is DateAndTime.Now, as in...

 

Dim ThisMoment As Date

' The following statement calls the Get procedure of the Visual Basic Now property.

ThisMoment = Now

Edit: see earlier post... ;-)

Edited November 27, 2014 by SimoninEaston

[GRitchie]

Macros. You'll want to look at the status box's 'Change' event. Click the elipsis next to the 'Change' property (under the 'Events' tab) when in design mode.

 

You'll need to change the object names (cmdStatus and txtDateTime) to match the actual names as defined in your form, but this is a very basic way of doing it:

 

Private Sub cmdStatus_Change()
   If cmdStatus.Value = "Closed" Then txtDateTime.Value = Now()
End Sub

 

Hm, trying this - where would I find the names that I need to adjust?

Not really getting this!

[LosOjos]

Hm, trying this - where would I find the names that I need to adjust?

Not really getting this!

 

Every object on a form has a corresponding 'object' in code. You use the objects name to interact with the object in VBA.

 

So, in my example above, cmdStatus is the dropdown box containing the status (Open/Closed). To find this, select the combo box on the form while in design mode, then look at it's property sheet for it's 'Name' property - that's the name you use to refer to it in code.

 

Likewise, the text box that you want to populate with the date/time (assuming it is a text box, if it's another type of object we may need to tweak the code) will have a name; in my example above, it's "txtDateTime".

 

As for the method that contains the code ("Private Sub cmdStatus_Change()") - Access will generate that part for you when you click the ellipsis next to the combo box's 'Change' event in the property sheet and select 'Code builder'