Update a field in a table that is hidden from the user (1 Viewer)

[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.

 

[Pat Hartman]

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")

 

[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?

 

[Pat Hartman]

@Gasman At that time, the advice was accurate. For reports, Access "helped" us out by rewriting the recordsource into a query that selected ONLY the fields that were bound to controls. Many people ran into this issue due to sorting anomalies. They thought their sort order in the RecordSource query would prevail but it turns out that it didn't and that was the second side effect of the disappearing unbound fields issue. But until recently, the aberration only affected reports. It did not affect forms. Now it affects forms but not as badly as it affects reports. It rarely bothered people in reports because #1, we don't write much code behind reports and #2, even less of it is calculations that include fields that are in the recordsource but not displayed on the report.

However, recently, I ran into a problem with a form. I almost never bind UpdateBy and UpdateDT fields to the form, usually because there is no room for them but I always log this information in the Form's BeforeUpdate event. The problem is tricky though. I can't quite put my finger on when Access rewrites the query but if you run the code without closing the form it seems to work. I always save and compile so that doesn't cause the problem. It seems to happen either when the form is closed or maybe when it is opened the next time or maybe not until a C&R. I can make the error go away by replacing the RecordSource but of course that isn't a permanent fix.

I think the problem happens only when the form is bound to a table rather than a query - again something I rarely do since most of my apps are connected to SQL Server and using forms/reports bound to naked tables defeats the whole purpose of using SQL Server. It may also happen if it is bound to query but you later change the query outside of the form. The form no longer picks up the external changes to the query.

To make it happen. bind a form to a table. Leave at least one field not bound to a control. In the form's BeforeUpdate event, populate the unbound field. In my case it is almost always UpdateBy or UpdateDT. save the code, compile. Save and close the form. When you reopen the form, the code doesn't work.

 

[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.

 

[Pat Hartman]

It you can't break a form using the method I described above, you are working with a version of Access that hasn't been broken yet. I just confirmed the form's RecordSource to still be "broken" in my version.

 

[Gasman]

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

 

[Pat Hartman]

As I said, it takes a certain set of circumstances for the "error" to pop up. I discovered it last year. I don't have any idea when MS broke the form to make it work like the Report and to "help" us.

 

[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.)

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

If you want to put out a new version, you need to replace the entire FE. The version table is in both the FE and BE. So you replace the FE and create a database that has a macro that updates the BE from versionX to versionY and makes any schema modifications needed for the new version. Make sure your version update knows what the old version should be and only updates the BE if it is updating the correct version. You want to prevent this update database from running twice.