Write data to table without closing form

[Big Pat]

Hi,

I'm still a novice at VBA so forgive me is this is laughably easy!

I need to log the date a certain form is first accessed by the user, so I can display a message that further information will be required by a 14-day deadline. This date will also trigger certain actions later, so I need to store it.

The form's record source is a table which contains (among other things) a field called ActivationDate. I have put this on the form but kept it invisible. I'm trying to use code to log the date the user first opens this form. So far I've got

If Me.ActivationDate.Value = Null Then   'If no date is yet stored, then
    Me.ActivationDate.Value = Date       'store the current date.
    DoCmd.Save           '[COLOR="Red"]<--  what should I put here to physically write the date into the table?[/COLOR]
End If
:
:
...(rest of code for this form)...
:

I've tried DoCmd.Save and I've tried Me.Update, but so far nothing works.

How should I go about this?

Thanks,

 

[Khalid_Afridi]

Hi Big_Pat

If the form's record source is the table where the field 'ActivationDate' comes from, then it will save the current date automatically to the underlying table in the 'ActivationDate' field when you close the form or goto another record either by record navigation buttons or some other means.

Docmd.Save method is used to Save form,table,module and macro etc.

You can use:

DoCmd.RunCommand (acCmdSaveRecord)

to save the record.

 

[Jeanette Cunningham]

Writing to a table is easy and automatic if your form is bound to the table with the field for activation date. If the form is bound it it has a record source based on a table or a query.
Perhaps your form is ‘unbound’ – there is not a record source?
For an unbound form you can put code in its open or load event to write the date to a table.

Private Sub SaveActivationDate
Dim strSQL As String
If Nz(DLookup("[ActivationDate]", [TableName]),0) =0 Then
strSQL = "UPDATE [TableName] SET [TableName].[ActivationDate] = Now"
CurrentDB.Execute strSQL
End If
End Sub

Note: replace [TableName] with your table.

If your form is bound to that table, you can use
Me.Refresh to save the data to disk.
To show the changes on the form, you can use
Me.Requery

 

[Big Pat]

Thank you both for those suggestions. I've tried to make them work but I failed. Maybe I'm putting the code in the wrong place, or maybe I'm missing something else which has not yet been mentioned.

Two things to clarify:
The form is definitely bound, although I have now also tried an unbound version.
I'm aware the data will save if I close the form or go to a new record, but I need to stay on the current record. In fact I have also tried

DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

but that doesn't seem to do anything either. I need a value to be written to the table immediately when the form is opened. Is that possible?

I've recreated your suggestions in a sample db which I have attached. Maybe this will show you where I've gone wrong.

Thank you.

 

[DCrake]

Theoretically you should not be saving the date to the record as soon as the record is displayed on screen. Why?, well suppose this is not the record that the user was looking for, perhaps they are scrolling through records, what you should be doing is saving the date in conjunction with any other changes made to the record. This ensures that the date is saved in the correct instance.

 

[Big Pat]

Hi,

Yes I can see that I suppose. However, the plan is that there will only ever be one record in this table and navigation controls will be removed so that the user can't create another.

My structure is as follows:
tblActivation -> ActivationDate field only. One record only
tblResearch -> the top-level data. Several hundred records.
tblParticipants -> sub-records. Several thousand records.

The way I was going to do this was to have frmActivation as my opening form, which would log the date it was first accessed (there is a reason for this, but not worth going into here).

This form would also have a subform (based on a query) which would act as a kind of dashboard for the rest of the database.

I've attached a jpeg showing what I mean. Does this make more sense now? The rest of the db is working OK, so really the only thing remaining is to log the date frmActivation is first accessed. This would need to be done even if there is no other data in any of the tables and even if the user makes no attempt to enter any at this "visit". The crucial thing for this db is to log the date the user first accessed it.