Query not combining and filtering values based on a form

[mayestom]

On the Log On form I just have the fields uFirstName, uLastName and ueMail? Can it just use uFirstName and uLastName?

 

[GinaWhipp]

Yes, the eMail is not required to get the Form to work

 

[mayestom]

On my Log On form (frmLogOn) I have first name, last name. Don't I also need to include the uPassword field as well?

So I have the two tables, the Log On form, I added the six hidden fields to my switchboard, and I added all the code in step 4 to the On_Load event of the switchboard and created the module (modUtilities) and I don't need the Temporary Access button so I'm omitting that part. Good so far?
I'm a little confused as to what I need to do next?
Do I add the Delete button code or the Form On_Current events code or the Lock Useres out code. What are those for?

 

[GinaWhipp]

Well, do you want them to Log On every time? Or do you just want to capture there information to assign permissions?

Which two tables? I'm also thinking you won't need all six hidden fields but when you answer the above I'll know better.

 

[mayestom]

Well, do you want them to Log On every time?

I suppose to log on every time. Because if not, can the program remember a user in order for them not to have to log in everytime (I'm thinking like a cookie or something like that)?

Or do you just want to capture there information to assign permissions?

I'm not sure what you mean by this?

Which two tables?

I thought I had read that I needed a tblUsers (with 13 fields uUserID, uFirstName, uLastName, ueMail, uLastLogon,...) and a tblSecurity (with 2 fields sSecurityID and sSecurity)

 

[GinaWhipp]

The program can remember because it is going to store their Network ID in the background, so really up to you.

You answered the questions about the tables, I forgot about tblSecurity.

Okay, now post the code you have in the On_Load event so I can modify.

 

[mayestom]

I'm not sure about the log on part. If it can remember a user in order for them not to have to log on every time then that would be fine. But let me find out.
Code for the On_Load event:

Private Sub Form_Load()
On Error Resume Next

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim myQuery As String

myQuery = "SELECT * FROM tblUsers WHERE uNetworkID = '" & Environ("UserName") & "'"

Set db = CurrentDb()
Set rst = db.OpenRecordset(myQuery, dbOpenDynaset, dbSeeChanges)

     If Not rst.BOF And Not rst.EOF Then
       rst.Edit
       rst.Fields("uLogonCount") = rst.Fields("uLogonCount") + 1
       rst.Fields("uLastLogon") = Now()
       rst.Update
        Me.txtSecurityID = rst.Fields("uSecurityID")
        Me.txtOverride = rst.Fields("uSpecialPermissions")
        Me.txtUserID = rst.Fields("uUserID")
        Me.txtDelete = rst.Fields("uDelete")
        Me.txtPassword = rst.Fields("uPassword")
       DoEvents
     Else
       DoCmd.OpenForm "frmNewUser", acNormal, , , , acWindowNormal
       Me.Dirty = False
       Me.Visible = False

       Do Until Me.Tag = "Continue"
         DoEvents
       Loop
     End If

Set rst = Nothing
db.Close
Set db = Nothing

          If IsDeveloper Then
             ChangeProperty "AllowBypassKey", dbBoolean, True
          Else
             ChangeProperty "AllowBypassKey", dbBoolean, False
          End If

Form_Load_Exit:
Exit Sub
End Sub

 

[GinaWhipp]

Slight delay, getting car serviced...

 

[mayestom]

I just got my car back from shop. Timing belt and front brake rotors replaced. Don't even ask how much that cost! Hope yours is not that much.

 

[GinaWhipp]

Nope, didn't cost me anything but a Front Wheel Alignment... just got care serviced in May so this was on them.

In any event, day got crowded after that and I did not get back to this thread. Will be here tomorrow with adjusted code!

 

[mayestom]

That would be great. Thank you Gina.

 

[GinaWhipp]

Sorry for delay... my *day* job has been keeping me busy of late. Here you go...

(1) You can remove the below hidden fields from the Main Menu...

4. txtDelete
5. txtDepartmentID *Optional, not used in this example!

...as you will not be using them.

Private Sub Form_Load()
On Error Resume Next

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim myQuery As String

myQuery = "SELECT * FROM tblUsers WHERE uNetworkID = '" & Environ("UserName") & "'"

Set db = CurrentDb()
Set rst = db.OpenRecordset(myQuery, dbOpenDynaset, dbSeeChanges)

     If Not rst.BOF And Not rst.EOF Then
       rst.Edit
       rst.Fields("uLogonCount") = rst.Fields("uLogonCount") + 1
       rst.Fields("uLastLogon") = Now()
       rst.Update
        Me.txtSecurityID = rst.Fields("uSecurityID")
        Me.txtOverride = rst.Fields("uSpecialPermissions")
        Me.txtUserID = rst.Fields("uUserID")
        'Not in use Me.txtDelete = rst.Fields("uDelete")
        Me.txtPassword = rst.Fields("uPassword")
       DoEvents
     Else
       DoCmd.OpenForm "frmNewUser", acNormal, , , , acWindowNormal
       Me.Dirty = False
       Me.Visible = False

       Do Until Me.Tag = "Continue"
         DoEvents
       Loop
     End If

Set rst = Nothing
db.Close
Set db = Nothing

Form_Load_Exit:
Exit Sub
End Sub

I have to go out Monday morning just for a bit but will address your next question when I return!

 

[mayestom]

OK added the modified code to the On_Load event of my switchboard and deleted the two hidden fields (delete and departmentID)
After step 5 there are a couple other pieces of code "a *delete* button, form *On_Current* events and Lock users out of a Menu/Switchboard button" do I need to add any of those?
On the new user form I am up to step 5 but not sure how to proceed? Do I copy/paste that code under the On_Click event of cmdContinue button? (also I didn't add a email address field to the new user form)

 

[GinaWhipp]

You don't need any of those but you do need to copy that code and alter it to remove the eMail Address do you know how to do that?

 

[mayestom]

This is the code that I attempted. I removed the email references and changed the switchboard name to reflect mine but it's not compiling properly.

 Private Sub cmdContinue_Click()
On Error GoTo ErrHandler

     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

     
     DoCmd.SetWarnings False
     strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, ueMail, uLastLogon, uLogonCount, uSecurityID, uActive )" & _
     " SELECT Environ("UserName"), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', Now(), 1, 9, True"
     DoCmd.RunSQL strSQL
     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()
     Application.Quit
End Sub

It gives me a syntax error and highlights:

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

Also, "Option Explicit" wasn't automatically added in the code when I opened VB editor so I added that in after "Option Compare Database".

 

[GinaWhipp]

You forgot to remove the email from the top portion, try...

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

 

[mayestom]

Applied new code but it still gives me a "Compile error: Syntax error" and highlights same snippet of code. It also gives "Complie error: Expected:end of statement" and hightlights UserName in same snippet of code.

This is what I have:

Private Sub cmdContinue_Click()
On Error GoTo ErrHandler
     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
 
 
     DoCmd.SetWarnings False
     strSQL = "INSERT INTO tblUsers ( uNetworkID, uFirstName, uLastName, uLastLogon, uLogonCount, uSecurityID, uActive )" & _
" SELECT Environ("UserName"), '" & Me.txtFirstName & "', '" & Me.txtLastName & "', Now(), 1, 9, True"
     DoCmd.RunSQL strSQL
     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()
     Application.Quit
End Sub

 

[mayestom]

Oh and I read at the bottom of the page where in Access 2007 it was giving that same error of "Compile Error, Expected: End of Statement" and highlights UserName but I'm using 2010

 

[GinaWhipp]

Can you upload the file so I can have a look?

 

[mayestom]

I open the "Switchboard" in design view because if not it gives an error message. And the form name is "frmNewUser". Try not to be mad at me for the lack of any sort of naming convention (or lack there of).