Use VBA to create new record and insert date into focused field?

[Goofpod]

Access created a macro for my button to create a new record (via button wizard), but I'd like to have that new record's first field ("DateBox") auto-populated with the current date. I see no way to use the existing macro function to populate that default-focus field with the date. I'd prefer NOT to have a separate "insert date" button or a text label suggestion to use the keyboard shortcut (CNTRL +. The user will EXPECT a system date to be programatically inserted into the new record, and rightfully so. How do I do this in the simplest way?

Can I do this with VBA (create new blank record and have the current date inserted into the named "DateBox" field?")

TIA!

~ Goof

 

[pbaldy]

The simplest way is to let Access do it. Set the default property of the field in the table to

Date()

 

[Pat Hartman]

It is important for you to NOT dirty a record before the user does. The best way to populate this field is to use the form's BeforeInsert event. This event runs only once and only after the user has started typing into some field and dirtied it. You don't need to change the field layout but you should take the date field out of the tab order so it won't be the field that gets the focus when the form opens. You want the user to type into some other field before your code populates the date field.

 

[Goofpod]

Thanks, good replies. However...

More detail:
I have “locked down” Access to behave as a stand alone application. The user will launch a desktop shortcut that launches a Main Menu modal form that is all they see centered on the screen. No tables, no forms, no navigation etc. only the Main Menu dashboard with multiple buttons.

One primary button launches a new form overtop the Main Menu form (pop up) that provides editing of existing records and an “add new record” button. (This second form is bound to one table.) The new record button blanks the fields (macro via the button wizard) and I’d like the current date to be inserted into my named “DateBox” field, which is bound to a field on the table named “Date.”

Caveat: the Access table behind this form is linked to a SQL Server so I cannot set the default property of the field on the table. Access says I must make changes on the source database. Unfortunately, SQL is locked down so I can’t modify the tables and even if I did, the SQL table wouldn’t write the date to the Access form upon form loading. I need the form date field to write to SQL when it syncs.

Pat: can I use a BeforeInsert event via VBA for a button on an existing form? In other words, the button uses a standard New Record macro event to clear the fields, but doesn’t open a new form. At what point does the new record get inserted into the table- not until a field is dirtied, or right upon the New Record button click event? So, instead of the limited macro behind the button can I use VBA to insert a new record with a field populated with current date value and have that value displayed on the currently open form (and, in turn, written to the SQL table upon synchronization)...

(Composed on an iPhone, please excuse typos.)

 

[JHB]

Does the default value for the control (“DateBox”?) not work?

 

[arnelgp]

You can add the date to your macro. In the next line if the macro:

Setvalue: [DateBox]
Value:: Date() or =date

 

[Goofpod]

arnelgp: this sounds right to me but how do I add code lines to the macro? I'm using the macro UI (form design view --> "Add New Record" button properties --> On Click event --> Embedded macro (ellipsis) opens the Macro UI but I see no way to just enter raw lines of code. I tried adding a RunCode function to the macro from the UI and typed in your suggestion, "Setvalue: [DateBox] Value:: Date()" but that produces an error when the button runs: "The object doesn't contain the Automation object 'Setvalue.' "

Maybe a separate macro for this additional function and have the original macro call the second macro? I'll test that next...

 

[arnelgp]

You shiw all macro command from the ribbon. If it dies not appear just type Setvalue ti the next blank macro line. Runcode macro is another thing. What you put there is a Publuc function.

If you must use runcode, create first a public function in a standard midule in vba:

Public function fnPutDate()
Forms!yourformname!datebox=Date
End function


Then on runcode, tyoe fnPutDate()

 

[Goofpod]

arnelgp: Sweet! Creating the public function and calling it from the RunCode command in the macro works perfectly. Many thanks!

Question: when I click the "Save Changes" button, which I put on the form for when users edit an existing record, my message box pops us correctly, "Record saved successfully," and the record does get saved to the access table and written to SQL fine, but then all the fields in the form display "#Deleted" -- any idea what's going on there? It's obviously not a true record in the table, just some kind of system text?

 

[Pat Hartman]

Is this a bound form? If it is a bound form, it is important that you NOT dirty the record before the user does. If the form is bound, you can use a default value for the date control. That will also populate immediately. The default in the SQL server database works fine but it does NOT populate immediately when the user navigates to a new record. It does however, get applied if the date field is empty when the record is saved. Just make sure to define the date as required.

If the form is unbound, it makes absolutely no difference what you do since you have to do everything via code anyway. The default for the control might also work for an unbound form but since I never use unbound data forms, I can't say for sure.

 

[Goofpod]

Thanks for the reply, Pat. This is a bound form but the suggestion from arnelgp works great, a macro calling a function:
=======================
If you must use runcode, create first a public function in a standard module in vba:

Public function fnPutDate()
Forms!yourformname!datebox=Date
End function

Then on runcode, tyoe fnPutDate()
=======================

This solved my auto-insert date problem but still can't figure out why I'm getting all fields populated with "#delete" after the new record is saved with the "Save Record" button.