VBA To have a button add Date to table field

[NewAccessUser12345]

I currently have a button that does some functions. With in it i want to add a date to a table.

Current code of the button

Private Sub butChoseOrderReport_Click()On Error GoTo butChoseOrderReport_Click_Err
   DoCmd.OpenForm "frmOrderReportButtons", acNormal, "", "", , acNormal[DateOrdered].Value = Now()butChoseOrderReport_Click_Exit:   Exit Sub
butChoseOrderReport_Click_Err:   MsgBox Error$   Resume butChoseOrderReport_Click_Exit
End Sub

I want it to add a date to the Order Table Called Order and the field DateOrdered in the table. Not good with VBA and can't figure out the code to make it work.

[moke123]

Need more info.

Apparently your opening a form. Is it a data entry form? Are you trying to get the default value of a field on your form to equal Now()?

[June7]

What happened to carriage returns in your code?

Why not just set Default Value property of textbox or field in table?

[NewAccessUser12345]

Need more info.

Apparently your opening a form. Is it a data entry form? Are you trying to get the default value of a field on your form to equal Now()?

The form does not have a dateordered its just in the table. I would prefer not to have it on the form and just have it add the date in the table to those unique order number..

Yes it opens a form.

[NewAccessUser12345]

What happened to carriage returns in your code?

Why not just set Default Value property of textbox or field in table?

Hi not sure what a carriage return is? The dateordered is only in the table and not on the form.. I would like when i hit the button the date automatically populates in the table for that order number with out having to have it on the form.

[Welshgasman]

This is more like what your code should look like.

Private Sub butChoseOrderReport_Click()
On Error GoTo butChoseOrderReport_Click_Err
   DoCmd.OpenForm "frmOrderReportButtons", acNormal, "", "", , acNormal
   [DateOrdered].Value = Now()
   butChoseOrderReport_Click_Exit:
   Exit Sub
butChoseOrderReport_Click_Err:
   MsgBox Error$   
   Resume butChoseOrderReport_Click_Exit
End Sub

How is anyone meant to make sense of what you posted?


Field does not have to have a control on the form, so you can just set it to date. Now() includes Date & Time. ​So unless you meant that use Date, as if you later compare to dates only, the time element is going to throw you off, unless you are aware of it.

I used to use code like this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.CreatedDate = Now()
    Me.CreatedBy = Environ("username")
Else
    Me.AmendedDate = Now()
    Me.AmendedBy = Environ("username")
End If
End Sub

[NewAccessUser12345]

Thanks I added that in on the button and get this error

Private Sub butChoseOrderReport_Click()
On Error GoTo butChoseOrderReport_Click_Err
   DoCmd.OpenForm "frmOrderReportButtons", acNormal, "", "", , acNormal
   [DateOrdered].Value = Now()
butChoseOrderReport_Click_Exit:
   Exit Sub
butChoseOrderReport_Click_Err:
   MsgBox Error$
   Resume butChoseOrderReport_Click_Exit
End Sub

This is more like what your code should look like.

Private Sub butChoseOrderReport_Click()
On Error GoTo butChoseOrderReport_Click_Err
   DoCmd.OpenForm "frmOrderReportButtons", acNormal, "", "", , acNormal
   [DateOrdered].Value = Now()
   butChoseOrderReport_Click_Exit:
   Exit Sub
butChoseOrderReport_Click_Err:
   MsgBox Error$   
   Resume butChoseOrderReport_Click_Exit
End Sub

How is anyone meant to make sense of what you posted?


Field does not have to have a control on the form, so you can just set it to date. Now() includes Date & Time. ​So unless you meant that use Date, as if you later compare to dates only, the time element is going to throw you off, unless you are aware of it.

I used to use code like this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.CreatedDate = Now()
    Me.CreatedBy = Environ("username")
Else
    Me.AmendedDate = Now()
    Me.AmendedBy = Environ("username")
End If
End Sub

[Welshgasman]

On what line does that occur?
Note your last acNormal is incorrect for the OpenForm.
You need to use Intellisense, that is what it is there for.

Is the field in the source of the form?

[NewAccessUser12345]

Hello no there is no field in the form.. IDK what intellisense is.. Newbie here.

On what line does that occur?
Note your last acNormal is incorrect for the OpenForm.
You need to use Intellisense, that is what it is there for.

Is the field in the source of the form?

[davegri]

[DateOrdered] must be a field within the form's recordsource.

Edit: I see now that WGM as already alluded to this.

[Welshgasman]

Hello no there is no field in the form.. IDK what intellisense is.. Newbie here.

Might want to start watching some YouTube videos then.
As I mentioned, the field/control does not have to be on the form, but it does have to exist in the source.

[moke123]

your open form code

DoCmd.OpenForm "frmOrderReportButtons", acNormal, "", "", , acNormal

has an incorrect window mode argument. it should be acWindowNormal

DoCmd.OpenForm "frmOrderReportButtons", acNormal, "", "", , acWindowNormal

Since you dont have any filter or where criteria and you are using the defaults for all the optional arguments you only need

DoCmd.OpenForm "frmOrderReportButtons"

No need to use "" as a placeholder for optional arguments.

edit: Just noticed Gasman mentioned acWindowNormal. oops.