Autodate

[harryfraser]

Afternoon all,

Can someone help me - i'm building an account amanagement system in access and I would like to add an auto date field to a form I am working on...sounds simple enough you say - however here's the twist.

I want the form to return the current date and time only when the user inputs a comment into another form field - i.e. so the user has a record of when we last spoke to the instiution and what was said. I then intend to peform an append query on the comments and dates - exporting it into another table which will keep a record of all correspondence between us and that institution.

I'm fine with the append query, im just having trouble with the VBA coding so would appreciate some guidance...

Cheers

H

 

[CBrighton]

That's fairly simple to do.

I would put something like this in the AfterUpdate event of the comments control:

'Set declarations
dim db as database
set db = currentDB
dim rst as recordset
'Open the table using a where clause to pick the record currently being vierwed in the form
set rst = db.openrecordset ("SELECT * FROM [B]TableName[/B] WHERE [B]UniqueIDField[/B] = '" & [B]ControlNameHoldingBoundToUniqueIDField[/B] & "'")
 
'Move to first (and only) record
rst.movefirst
 
'Update record
rst.edit
rst![B]DateTimeStampField[/B] = Now()
rst.update
 
rst.close
set rst = nothing

:edit:

Bold needs to be replaced with real table/field/control names.

The above was written on the assumption that the ID field is text format. if not you don't want the where clause to include 's around the control name.

 

[harryfraser]

Hi,

Sorry, tried that and didnt work. I'm a relative novice at access so im a bit unsure of the lingo that you have put and how it works in VB - thought I had it but didn't work - any chance you could explain in more basic WYSIWYG terms?

Thanks

H

 

[harryfraser]

The above was written on the assumption that the ID field is text format. if not you don't want the where clause to include 's around the control name.

Just for the record, my ID field is number as in employee ID and I'm entering the data in form view.

 

[CBrighton]

It's the data type of the field in the table which matters. The recordset is opening the table with a where clause, basically the recordset is a virtual query.

In a where clause a field with a number datatype should not be surrounded by 's so the code I provided will work on text data type fields.


Using my code should be as simple as replacing the bolded parts with the field / control names.

e.g. If the table is called tblMain, the field in the table is called EmployeeID and the textbox control on the form which is bound to that field is called txtEmployeeID then:

set rst = db.openrecordset ("SELECT * FROM [B]TableName[/B] WHERE [B]UniqueIDField[/B] = '" & [B]ControlNameHoldingBoundToUniqueIDField[/B] & "'")

becomes

set rst = db.openrecordset ("SELECT * FROM [B]tblMain [/B]WHERE [B]EmployeeID [/B]= '" & [B]txtEmployeeID [/B]& "'")

If you are still having trouble it may be easier to attach a copy of your database so I can have a look (obv remove real data, a record or two showing the correct format of the data is good to keep though).

 

[harryfraser]

Hi,

Please find attached a copy of the file I am using. I have attached a briedf description of what I am looking to to in the contacts comments.

Cheers

Harry

 

[CBrighton]

Which form am I looking at?

 

[harryfraser]

ContactsSub

 

[CBrighton]

Private Sub contactcomment_AfterUpdate()
commentdate = Now()
End Sub

That should do the job. Works fine when I tested here (and it's a bit simpler than the recordset solution!).