Change DATE if record is Added OR Updated (edited) in TABLES

[alinka]

Hi,

In TABLES, I know how to create a column for the "as of date" so that when a record is added - it shows that day's date....but I don't know how to make this column show the "as of date" if a record is edited. Basically, I not only want the date to update when a I add a record row into a table, but I also want for this date to change when a record is edited in any way.

Could someone please teach me how this can be done?
THANK YOU SO MUCH!

 

[Corticus]

Hi,

I would suggest allowing form-only entry into your table. Set your form up, and then disable all of the textboxes, and any controls that can change values in the table.

Then, add a button or checkbox labeled 'edit' with code such as (txtdate is a textbox with its control source set to the date field in the table):

Private Sub button1_click()

txt1.enabled = True
txt2.enabled = True
txt3.enabled = True
txtdate.enabled = True
txtdate = now()

End Sub

Now, your user will see all of the available fields, but won't be able to change anything unless they hit edit, which puts the current date in the textbox linked to this field.

You will also need some code to disable those fields again, like:

Private Sub Form_AfterUpdate()

txt1.enabled = false
txt2.enabled = false
txt3.enabled = false
txtdate.enabled = false

'if you used a checkbox to edit add this
chkbox1 = False

End Sub

Now they can move around the records to see them, but they can't edit them until they hit the edit button again.

Let me know if this doesn't work for you...

HTH,
Corticus

 

[alinka]

Works but only for Edits

Hi Corticus,

Thanks for all your help. This form works - but I only have an edit button. I need the same exact thing to happen for the Add button when I want to Add Records....How do I do that? Thanks!

 

[Corticus]

I didn't think about that,

You could go to the form properties, and turn the navigation bar off, and then use the wizard to insert buttons on your form that bo back a record, go forward a record, and create a record. Witha a button for creating a new record, you can attach the same code to the click() event as a gave before, enabling all the text boxes, and putting the current date in txtdate.

Since you have a button for all of you record navigations, you can attach the supplied code as appropriate for each button.

HTH,

 

[alinka]

Add Button!

Hi again,

Thanks for the info. That's exactly what I did. I used the wizard to make a button that "adds new records"...but this is what happens when I try to add a record in the live form:

Since we disabled all fields (for edit record purposes), when I click on the "Add Record" button, all fields are disabled...

 

[Corticus]

Hi,

Before this line in the code the 'add record' executes:

DoCmd.GoToRecord , , acNewRec

add the code to enable the textboxes again, and put the data in txtdate. You will also need to disable them if they go forward or backward a record. This might take finnagling for your needs, but the idea is, with buttons for your form navigation, you have events that you can capture to make sure you only stamp the date when someone is editing a record, or adding a new one, the disabled text boxes keep them from editing unless they hit edit, or go to a new record, which will update the date. I like making the textboxes disabled instead of invisible so that the user at least know they're there, but can't be used.

HTH,
Corticus