Solved: Complex issue in Access

[ganesh3084]

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.

[hansup]

So users in question can INSERT but not UPDATE.

In that case re-define "end time" as the time the record insertion is attempted rather than waiting for the insert to complete and then updating end time.

Your form can include a text box control named txtEndTime bound to the end_time in the form's record source. Then in the form's before insert event, you can assign your Date/Time value to txtEndTime and it will be saved along with the values of your other bound data controls.

If you don't want your users to see the txtEndTime text box, you can set it to hidden, and it will still work.

[ganesh3084]

Hi

Thanks for your reply.

i have gone through as you told. What happened is, the form's before insert event is 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.

[hansup]

What happened is, the form's before insert event is fired when the new record got the focus and it is not firing again before inserting the record.
The before insert event will never fire more than once for the same record. After it has been inserted, you can only update or delete it.

Based on your description, the new record is somehow saved as soon as it gets focus. I don't know why that is happening. But that initial record save is when the insert happens.

[ganesh3084]

Hi
Actually in my form, the record is not inserted when a field got focus.Only the form's before insert event get fired soon after a field gets focus. The record is inserted only after the insertion.
What i mentioned is i cant able to capture the end time in 'before insert' event.
can u give any other suggestion to capture the end time before insert???

Thanks.

[ganesh3084]

Hi

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

Many Thanks:hi: