User Level Security Wizard

[DatabaseTash]

No luck. I still get an error with the word Switchboard on that same line.

Switchboard is saved as Switchboard and the Caption in the properties says Switchboard. Is there anywhere else I should be naming it that??

 

[GinaWhipp]

Please post that entire section of code here cause it works for me. Also, did you run Debug > Compile to make sure there were no errors in the code?

 

[DatabaseTash]

This is all of the code in there. When I hit debug and Compile it highlights (Switchboard)

Option Compare Database
Option Explicit
Private Sub cmdContinue_Click()
On Error GoTo ErrHandler

Dim strSQL As String

If Nz(Me.txtFirstName, "") = "" Then
MsgBox ("First Name cannot be empty.")
DoCmd.GoToControl "txtFirstName"
Exit Sub
End If

If Nz(Me.txtLastName, "") = "" Then
MsgBox ("Last Name cannot be empty.")
DoCmd.GoToControl "txtLastName"
Exit Sub
End If

If Nz(Me.txteMail, "") = "" Then
MsgBox ("Email cannot be empty.")
DoCmd.GoToControl "txteMail"
Exit Sub
End If

strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uActive )" & _
" SELECT GetUserID(), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, True"
CurrentDb.Execute strSQL, dbFailOnError

Forms(Switchboard).Tag = "Continue" 'frmMainMenu change to the name of your Main Menu/Switchboard/Navigation Form
DoCmd.Close acForm, Me.Name

Complete:
Exit Sub

ErrHandler:
MsgBox ("Error creating user profile: " & Err.Description)
End Sub

Private Sub cmdExit_Click()

End Sub



Private Sub Form_Click()

End Sub

 

[GinaWhipp]

I see I missed on correction, you forgot the quotation marks...

Forms("Switchboard").Tag

 

[DatabaseTash]

It worked!!

I put it my details and hit continue and it went straight to the Switchboard. My details saved to tblusers. When I go into the search form though it has wiped all the data from the tables. I did notice in the tblusers though that if recorded the uSecurityID as 0. Do you think that is way I can't see any data?

 

[GinaWhipp]

Hmm, might have to tweak that query because it should be 9. That said, yes that is why you are having a problem seeing anything. You are going to need an admin area to update that if you plan on distributing this database.

Late here, almost 4:00 am will later about why 0 instead of 9.

 

[GinaWhipp]

I omitted a field in my query replace with the below...

strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _
            " SELECT GetUserID(), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', '" & Me.txteMail & "', Now(), 1, 9, True"

That should fill in the 9.

 

[DatabaseTash]

Thank you GinaWhipp that did the trick. It is now recording the number in the user table. I'm not able to test if it is read only though as there is no data in any of the tables. What would cause it to disappear?

Also, I feel really bad that you are doing this so late. Please don't omit sleep you need to get a good night's sleep. It can wait until daylight hours. No hurry.

 

[GinaWhipp]

Yippee!

You are not the reason I omit sleep. I have a *day* job which often means I work late at night. However, if I start answering hate to just drop off as posters, like you, have no idea what time zone folks like me are in and might feel like you've been left to your own devices.

Not sure what you mean by data disappearing. Nothing in what you're doing make the data delete from the tables.

 

[DatabaseTash]

Thank you

I open the tables and all the tables are empty. Even if I go through the back end file the tables are empty.

 

[GinaWhipp]

Okay well I know the sample I got was empty. Did you empty the master by mistake? (That would be the only way they could go empty.

 

[DatabaseTash]

No, I resaved the copy before stripping it back. I know I didn't accidentally delete from this database because all of the extra forms are there and all the graphics are still on the forms etc.

I have the data that I could reimport, might have to do that.

 

[GinaWhipp]

I would do that and make sure to make a copy but the code provided ONLY looks at tblUsers, tblSecurities, ect. None of the other tables come into play. That said do not use those tables in any other queries for your database.

 

[DatabaseTash]

I'm doing a happy dance!!

The database is working really well!

I would like to start handing out front ends now. When I put it on other workstations the user form doesn't pop up only the switchboard does. Once I enable the database the switchboard disappears. I am only trying to use a .accdb. I tried to change it to a ACCDE file but it won't allow me.

Is this a trust centre issue? They are all on the same network and have permissions to the folder that the database is saved in.

 

[GinaWhipp]

They don't need permissions to the Backend, they need Rights to the Backend. They need the Frontend to be in a Trusted Location.

That said if putting it in a Trusted Location is not working then it sounds like you need to test on a *clean* machine to make sure everything is set up correctly.

 

[DatabaseTash]

Thank you! I got it to work on the workstation by just manually adding them into the table. For some reason their computer didn't like the New User Form. The read only works well on the forms. There are a couple of menu options that result in a query. Is there a way of adding that same script to the query to make them read only too?

 

[GinaWhipp]

Hmm, that is odd, glad you got it solved. Though I would probably still be at that machine to find out its problem.

You can't make queries Read Only and truth be told you NEVER want your Users in queries. Users should ONLY access data via Forms.

 

[DatabaseTash]

Oh okay, thank you. I will get busy creating forms for them then.

Thank you sooooooo much for all your help! I really appreciate it!! You are a CHAMPION!!!! Everyone is so excited to start using the database.

THANK YOU!

 

[GinaWhipp]

Glad we finally got it worked out. You're most welcome!