Automatically finds the open record and insert todays date (1 Viewer)

[nathanmav]

Help.. I just want to ask how to add code in a form that will automatically finds the open record and insert today's date? Thanks hope someone can help me..

 

[apr pillai]

If I am not mistaken you are trying to insert Today's date into a field of a new record on the form, right?

If so, add the following Code into the Form's module:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me![DateFieldName] = Date()
End Sub

Open record on a Form is the current record on a Form. You can address the current record's datafield from another form-module or from the standard module, like the sample statement given below:

Forms![Employees]![HireDate] = Date()

When you execute this code the Employees form must be in open state.

 

[nathanmav]

thanks.. i try the code you gave me its working but is possible to add new record after the automatic insert of today's date.

what im trying to do is i have a previous record with salary start and salary end.. and i want to add new record but insert first the todays date in the previous record of salary end the set focus will go to salary start of a new record.. hope you understand what im trying to do.. i attached the sample picture of my database.. thanks

 

[khodr]

If I under stood you correctly you need then to add the following line to the previous code code:

DoCmd.GoToRecord , , acNewRec

Then it should work
Good luck
Join my group and let's learn group name is ( The New To Access Group )

 

[mahenkj2]

what im trying to do is i have a previous record with salary start and salary end.. and i want to add new record but insert first the todays date in the previous record of salary end the set focus will go to salary start of a new record..

In such case, you need an unique identifier which may be employee code or a primary key of that table, you would like to insert Salary End date but only to that employee of which you are entering a new record, correct? So this should become a criteria and this criteria will be used in a update query.

So, when you press a button, first an update query will run which will search employeeID of active form in table, insert todays date. Then you would ask to go for new record.

I think by working on these lines you may achieve your solution. Please revert if you need further help.

best regards.

 

[nathanmav]

In such case, you need an unique identifier which may be employee code or a primary key of that table, you would like to insert Salary End date but only to that employee of which you are entering a new record, correct? So this should become a criteria and this criteria will be used in a update query.

So, when you press a button, first an update query will run which will search employeeID of active form in table, insert todays date. Then you would ask to go for new record.
.

thank you this is exactly what im trying to do... how can i make an update query.. i attach the sample db that im currently working... thanks again

 

[apr pillai]

Copy and paste the following Code into the Salaries Form Module and try it out:

Private Sub cmdNew_Click()
Dim rst As Recordset, j As Integer

Set rst = Me.RecordsetClone
rst.MoveFirst
For j = 1 To rst.RecordCount
If IsNull(rst!SalaryEnd) Then
   rst.Edit
   rst![SalaryEnd] = Date
   rst.Update
   rst.AddNew
   rst![SalaryStart] = Date
   rst.Update
   Me.Bookmark = rst.Bookmark
   Exit For
End If
Next
Set rst = Nothing
End Sub

I have made copies of both the Employee and Salaries forms with the names Employee2 and Salaries2 respectively. You may open the Employee2 form to try out the program. The modified database is attached.

In am using Access2007 and no idea how it will behave in Access2010.

 

[nathanmav]

thank you very must i got it... its a big help for me.. thanks again!