Update a field in a table that is hidden from the user

[anski]

Assuming an Access app has been deployed to a user. There is a field in a table that is hidden from the user. How can we modify the value of the hidden field?

 

[CJ_London]

use an update query

 

[Gasman]

You can also update the field on the form. The control does not need to be visible, in fact does not need to exist even, as long as it is in the source, just update with vba.

 

[Gasman]

Never use an update query on a record your form is bound to. I'm also pretty sure that how forms handle bound RecordSources has changed to be more like the way Reports do. In that case, the field you want to update with VBA must actually be bound to a control. You may make it very small and hidden. Then in the BeforeUpdate event of the form, update the hidden control:

Me.ChangedDT = Now()
Me.ChangedBy Environ("UserName")

@Pat Hartman
My response was to advice you gave me a few years back, where Access brings in all the fields of the record source, regardless whether there are controls on the form or not.?

From that I wrote

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

and those controls do not exist on the form?

 

[jdraw]

anski,
Can I ask what is the purpose of this hidden field/table?
Given that the "Access app has been deployed to a user", did you have some forethought for this potential purpose?

 

[Gasman]

That particular form of mine is bound to a tables, as likely are others, as my BE was just an Access DB.
The Form BeforeUpdate event was where you advised me to place the code, and it worked well.

 

[Gasman]

I am still on 2007 , but point noted if I mention it again.

 

[anski]

Thanks for the suggestions. The scenario is the user does not have MS Access so I plan to use runtime. What I am thinking is to create another app that has the Update Query. Send this app to the user. The user will use Import> New Data Source> From Database> Access.

Is there a better (and simple) way to do this? I am anticipating updates or corrections to be done like in case a user enters wrong company information such as the tax identification number. (When a user "finalizes" information, the data can no longer be modified.)

 

[anski]

If the user is using the Runtime, it will NOT be able to update any objects in the db.

If the data is hidden, how did the user update it to begin with? You CAN create another database that has a macro that runs a query to update a table in the BE but you MUST know the exact path to the BE so you can include it in the query. And the macro has to close the database as its last step.

Hidden data - fields such as version number.