How to set up db so one user sees only forms and other user has access to all?

[AccessIdiot]

I'm sure this has been asked a million times but I can't seem to find the answer I need.

I am working in Access 2007. I have a very simple project composed of one main table, several look up tables, one switchboard form, two other forms (one data entry form and one query/search form), and one report.

I need to set this up with two levels of access:

1) people entering data can only see the forms and report, no access to any tables. It would be great if they couldn't even see the tables.

2) one person with access to everything (not me, since i'm shipping this whole thing off to the folks I'm creating it for). This person may need to delete records from the main table or add values to the look up tables, etc.

One idea is to password protect the tables. Another idea is to split (?) the project into front end/back end. I've seen people mention this, and have seen NeoPa's write up (here) but in reality I have no idea how to do either. I'm not a coder, but can hack things together fairly well.

I'm told the project will be on one computer. There is no access to SQL Server or anything fancy/complicated. We're not concerned with people hacking the data, just want to avoid mistakes or people poking at tables they shouldn't.

Ideas? Thoughts? Advice?

[AccessIdiot]

Unfortunately not around the database on the weekend and my 4 yr old keeps me away from the computer mostly (she's asleep right now). I'll be back at it Monday morning PNW time. Have a great weekend!

[NeoPa]

M, All your clever code is completely bypassed if you click on Exit (Your Command Button). Instead of closing serenely using :

Call DoCmd.Close

it quits Access immediately and irreversibly, using :

DoCmd.Quit

Also, assuming you want the database to close (rather than just the form or even the whole application), you should simply use :

Call CloseCurrentDatabase

In short, your Form_Close() subroutine should read :

Private Sub Form_Close()
  Dim strUser As String
 
    strUser = ";" & Environ("UserName") & ";"
    If InStr(1, ";" & conClear & ";", strUser) > 0 Then
 
        If Not DBWindowVisible() Then _
            Call DoCmd.SelectObject(ObjectType:=acForm, _
                                    InDatabaseWindow:=True)
    Else
        Call CloseCurrentDatabase
    End If
End Sub

Everything else I could see worked exactly as expected (I even added in my ID and it switched me back to the Database Window on close, instead of dropping out of the database completely).

PS. Your cmdViewReport_Click() subroutine should say :

Private Sub cmdViewReport_Click()
 DoCmd.OpenReport "rpt_Report", acViewPreview
End Sub

acViewreport is not a valid parameter value (It's undefined). With that fixed the project compiles (which is always very important).

[AccessIdiot]

Thanks A, that cleans it up nicely. My code is very much hacked together as you can tell.

Still having an issue however. When I am logged in to my machine and I launch the db I don't get the navigation pane. If I right click on the switchboard form and select "close" then the form closes but I get the navigation pane.

This doesn't happen when my coworker tries it from his machine (his login), just mine. Any ideas?

[NeoPa]

M:
Any ideas?

Only that this is exactly what I would expect to see. This is the whole idea we've been looking to implement, to have separate results for those in the list, from those not in it. The Database Window (Navigation Pane if you prefer) is designed (in this database) not to show until someone in the list closes out the database. At this point M (or A when I was running it) will get the Database Window left open and accessible, whereas all others will find that the database has been closed entirely, leaving only an open Access window.

What were you expecting that is different from this?

[AccessIdiot]

Got it. I was expecting that the nav panel would display as soon as the db was launched if the appropriate person was logged in. It is fine the way it is now, I just need to explain to the client what they should expect to see and how it works. Thanks A!

[AccessIdiot]

Another question: is there a way to remove the right click option to design forms (layout option too) for those that aren't allowed?

[NeoPa]

M:
Got it. I was expecting that the nav panel would display as soon as the db was launched if the appropriate person was logged in.

That can easily be done that way too, if that would help. I figured it would simply clutter up the screen if the database is running. The current way, it shows either the running database, or the Database Window. Never both at the same time. Let me know if you'd prefer the Database Window to be open at all times for the selected users though.

[NeoPa]

M:
Another question: is there a way to remove the right click option to design forms (layout option too) for those that aren't allowed?

Not that I'm familiar with. Similar code wouldn't do it I don't think. This is taking the solution well beyond the original stated requirement (which was fundamentally to make fiddling unobvious rather than blocked). If stronger protection is required then a whole different approach would be needed. We'd have to throw away and start again from scratch if this feature were important.

[AccessIdiot]

Nope, just thought I'd ask. I think making the tables not apparent is more important at this stage than protecting the design. Thanks again!