Record source is not not updating

[ajaeger]

I have a database that is used only in the fall for a month or so. It was working last fall without issue, but in the last year, an upgrade to Access 2007 seems to have adjusted some functionality.

Users log in using a UserID such as "Administration", "Conference", "Programs" etc. In the data entry form, the record source should (used to last year) change based on the userID. So, if someone is logged in as Adminstration, the record source will be qryChartOfAccounts-Adminstration. If they are logged in as Conference, the record source will be qryChartOfAccounts-Conference.

What I'm seeing now is that the record source never changes from qryChartOfAccounts-Adminstration. When the Conference user is logged in, he cannot open the data entry form because the record source is qryChartOfAccounts-Administration and he doesn't have permission for that query. "Record(s) cannot be read; no permission on qryChartOfAccounts-Administration."

Here's what I have on open for the data entry form.

Private Sub Form_Open(Cancel As Integer)

Dim strRecordSource As String

strRecordSource = "qryChartOfAccounts-" & CurrentUser()
MsgBox "The record source is " & strRecordSource
Form_frmBudgetEntry.RecordSource = strRecordSource

Me.Requery

End Sub

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[dhookom]

Can we assume you are implementing user security and the file format is still an MDB? The only suggestion I have is to change the code to:

Private Sub Form_Open(Cancel As Integer)

  Dim strRecordSource As String

  strRecordSource = "[qryChartOfAccounts-" & CurrentUser() & "]"
  MsgBox "The record source is " & strRecordSource
  Form_frmBudgetEntry.RecordSource = strRecordSource

  Me.Requery

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I haven't spent in real time with Access 2007 but I think the real answer is that they completely revamped security in 2007.

In theory at least it should still support last version files so did you upgrade your database to 2007? If so, do you have the older version?

I SUSPECT that you should be able to give 2007 a command line switch with an earlier version workgroup file and open an earlier version database. I'd be curious of these results.

An alternative would be to learn 2007 securiy methods and see where the compatible items are and how to come up with a new security scheme.

The other alternate method would be to make some sort of application log on and use it instead of currentuser... This may be part of a true solution using 2007 security as my google search seemed to say that user level security went away but alas I only gave it a cursory look.

I'm hoping someone else can give you more specific advice.
I'm curious either way.

 

[lameid]

By Duane's cross post I can see you must be able to use user level security with older versions of files... I'd be curious as to how the workgroup file is supported (command line only?)

Good point with the square brackets.