Complex issue in MS access

[ganeshvenkatram]

Hi All,
Im doing a project in access which i have created tables, forms and reports i have applied user secuity in my project.some users have editable permissions and some doesnt have.
In my form i have two fields which will capture system date & time as start time and end time.
start time will capture the time when the user starts keyin and the end time will capture the time after the record has been inserted in the table.
My problem is for non-editable permission users,the end time is not storing the time after inserting record since it will capture only after insertion and it goes to update state for whom doesnt have update permissions.
Hence i need code to capture and store the end time after the users filling all fields and before insert in to table.


Hope you guys got my problem. Plz provide me a solution.

[ssanfu]

Welcome to the forum.

In my form i have two fields which will capture system date & time as start time and end time.
start time will capture the time when the user starts keyin and the end time will capture the time after the record has been inserted in the table.

Is the data entry form unbound?

How are you inserting the record?

[ganeshvenkatram]

Hi Thanks for your reply

Unfortunately I think u didnt get my exact problem.

According to your project , u will update the endtime after inserting a new record.
In my project some users doesnt have update permissions so that i cant able to update the end time.
I have even tried using form's before insert event.

What happened is, the form's before insert event was fired when the new record got the focus and it is not firing again before inserting the record.

For example
i have 5 fields in my form which include Name, age,city, start time and end time. I have wrtiten the following code in form'before update event

<Code>
Private Sub Form_BeforeInsert(Cancel As Integer)
txtEndtime = Format(Now, "dd/MM/yy hh:mm ss")
End Sub
</code>
The above event got fired when the new record got focus in first field. Suppose i have started code at "10/07/2011 10:10:10" and end time has that value. After filling all records and when moving to new record, the form's before insert event is not firing again hence the end time remains at
"10/07/2011 10:10:10". This is the problem i'm facing.

Please Suggest me.

[SteveH2508]

Why not use the AfterInsert event?

[ganeshvenkatram]

Hi Thanks for ur reply

After insert means that it will go to update mode for which some user doesnt have that permissions. They have only insert permission

[ssanfu]

Unfortunately I think u didnt get my exact problem.

Which is why I asked questions.

What happened is, the form's before insert event was fired when the new record got the focus and it is not firing again before inserting the record.

The before insert event fires just before the new (blank) record is created in the table. It only fires when a new record is created, so it won't fire again.

For example
i have 5 fields in my form which include Name, age,city, start time and end time. I have wrtiten the following code in form'before update event

<Code>
Private Sub Form_BeforeInsert(Cancel As Integer)
txtEndtime = Format(Now, "dd/MM/yy hh:mm ss")
End Sub
</code>
The above event got fired when the new record got focus in first field. Suppose i have started code at "10/07/2011 10:10:10" and end time has that value. After filling all records and when moving to new record, the form's before insert event is not firing again hence the end time remains at
"10/07/2011 10:10:10". This is the problem i'm facing.

Please Suggest me.

I would use the form before update, not the before insert.

Private Sub Form_BeforeUpdate(Cancel As Integer)
txtEndtime = Now()
End Sub

Formatting the date/time only affects how data is displayed, not how it is stored in the table, so it is not needed.

[ganeshvenkatram]

Hi
Thanks for your valuable suggesstion. I got the output. I have used Form's before update event and i got my expected output.

Many Thanks