Unable to set field value in access form with new record
When I open my ActivityTracker access form to a new record, I want it to automatically populate the * start_time * field with the current time ().
Private Sub Form_Open(Cancel As Integer)
If IsNull(Form_ActivityEntry.Start_time) And IsNull(Form_ActivityEntry.id) Then
Form_ActivityEntry.Start_time = Now()
End If
End Sub
This raises the error "You cannot assign a value to this object" and terminates execution.
I can fix the error by explicitly navigating to a new entry
Private Sub Form_Open(Cancel As Integer)
If IsNull(Form_ActivityEntry.Start_time) And IsNull(Form_ActivityEntry.id) Then
DoCmd.RunCommand acCmdRecordsGoToNew
Form_ActivityEntry.Start_time = Now()
End If
End Sub
or
Private Sub Form_Open(Cancel As Integer)
If IsNull(Form_ActivityEntry.Start_time) And IsNull(Form_ActivityEntry.id) Then
Me.Recordset.AddNew
Form_ActivityEntry.Start_time = Now()
End If
End Sub
but any of them raise a pop-up warning: "You cannot navigate to the specified entry."
I tried to suppress the warning with this
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.SetWarnings True
or by customizing error handling,
On Error GoTo Err_SomeName
but I am still getting the warning.
AllowAdditions is set to True. Recordset type is Dynaset.
Otherwise everything works fine with this form.
What am I doing wrong? Is there an event called "new record opening" and not "open form"?
Thank you for your help!
source to share
I suggest you set the default of the control to Now () or Date (). The default only applies to new records, and as soon as you fill in any other field, the value will be assigned.
In this particular case, there is even an argument to set a default value for a field in the table.
Please don't use Set Warnings: What is the difference between DoCmd.SetWarnings and CurrentDB.Execute
source to share
The problem is that until now no one has answered correctly why the code has not been posted.
CAUSE Why are you using the on-open event.
Unlike .net, and most systems access is BIG design where you have two events when you open a form event (which can be canceled) and an on-load event.
This nifty design of Access means that code to check conditions and prevent the form from loading can be placed on the on-open event. If you look at CLOSE, you can see that there is even a cancellation on the open. If you set cancel = true then FORM WILL NOT LOAD AND WILL NOT BE DISPLAYED.
So you can check for missing data or user rights or whatever, and huts cancel form loading. If you can cancel the loading of the form, then it would be pointless to allow the value of bound controls to change - since all bound controls are READ ONLY.
You DO NOT ALLOW changing the values of the BOUND controls in the on-open event. It's TOO soon for both design and product intent.
So, testing the conditions to prevent the form from loading is open.
Thus, this means that setting variables, setting controls, setting values for controls and the launch code of the base form belongs to the ON-LOAD event. It's too early in the opened mode.
And from Couse, if your code is going to cancel the form loading, then it makes sense that all your initiation and setup code forms MUST NOT and should not be triggered.
Finally
Open event:
You can set cancel = true The code goes here to check if you want to prevent the form from loading and being viewed by the user.
ON-LOAD event:
Your startup code, setting variables, setting controls, etc. are currently recommended as well.
And this simple design in Access ALSO means that as a developer, you now know where to look for the code that will prevent and cancel the form loading. Of course, without this basic understanding of the difference and WHY two events exist in Access, then the confusion of the poster and the answers becomes obvious.
source to share
I'm not a fan of moving to a new form entry. There are too many data holes to be created and you run into problems like yours.
I recommend a blank unlinked form with a textbox, calender control, number up ... etc. for each of the fields you want to add. This way, you can type-check each of the fields, or perform other checks against whatever you want. Then, when the user is happy, add an insert request record.
However, to the question you asked. It looks like you are trying to assign a value to a related field. Try assigning a value to the object the field is bound to.
source to share