Link to home
Start Free TrialLog in
Avatar of Jack Lugo
Jack LugoFlag for United States of America

asked on

Add New Record Based on Form Field Data

I have 'User Permissions' set up on my DB that allow certain 'User Groups' different enabled functions to edit or view-only data, based on a hidden field [txtUserName] that gets populated automatically when someone opens the DB.

I use three tables to make these permissions work.

1. [TBL_C210_UserT ].[UserID] = UserName from an "ENVIRON query"

2. [TBL_C210_GroupT].[ID] = User Group

3. [TBL_C210_GroupXUserT ].[UserID] and [TBL_C210_GroupXUserT ].[GroupID] ties the 'User' to the 'Group Permissions Level'. 

Pretty straightforward stuff, works great.

User generated image

 

When the DB opens it defaults to frmMain_Menu on load, with Forms!frmMain_Menu!txtUserName.Visible = False.


Now, I've been directed to start sharing our DB with other workgroups, and instead of having to administratively add Users and give them 'Read Only' permissions, I'd like the DB to do it for me automatically.


I would like to add some SQL to the frmMain_Menu on load event which does the following sequences:

1. DCount [TBL_C210_UserT].[UserName], if UserName doesn't exist then add a new record to TBL_C210_UserT

2. Then insert [TBL_C210_UserT].[ID] into [TBL_C210_GroupXUserT].[UserID] as a new record. [TBL_C210_GroupXUserT].[GroupID] is set to default value.


Can someone please help me with this VBA coding?

Private Sub Form_Load()
    txtUserName = Environ("USERNAME")         'Verify login credentials     Dim X As Long     X = Nz(DLookup("UserID", "TBL_C210_UserT", "Username=""" & txtUserName & """"))     If X > 0 Then         Forms!frmMain_Menu!txtUserID = X                  'Logs User Activity "Log ON"         DoCmd.SetWarnings False         DoCmd.RunSQL "INSERT INTO TBL_C210_UserLog ( UserID, Activity, Notes )" & _             "SELECT [Forms]![frmMain_Menu]![txtUserID] AS UserID, 'Log ON' AS Activity, [Forms]![frmMain_Menu]![txtUserName] AS UserName;"         DoCmd.SetWarnings True              End If         If IsUserInGroup(1) Then             Forms!frmMain_Menu!btnAdmin.Enabled = True             Forms!frmMain_Menu!btnTimeLog.Enabled = True         End If         If IsUserInGroup(2) Then             Forms!frmMain_Menu!btnAdmin.Enabled = False             Forms!frmMain_Menu!btnTimeLog.Enabled = True         End If          DoCmd.OpenForm "frmMain_Menu" End Sub

Open in new window

Any help would be greatly appreciated. Thank you.

Avatar of Maria Barnes
Maria Barnes
Flag of United States of America image

Before the End If on line 17 add
Else
        DoCmd.SetWarnings False         
        DoCmd.RunSQL "INSERT TBL_C210_UserT  (Username) VALUES ('" &  txtUserName & "')" 
        X = Nz(DLookup("UserID", "TBL_C210_UserT", "Username=""" & txtUserName & """")) 
        DoCmd.RunSQL "INSERT TBL_C210_GroupXUserT (UserID ) VALUES (" &  X& ")" 
        DoCmd.SetWarnings True 
             

Maria,

Still using SetWarnings and RunSQL?
Why aren't you using the Execute method, which allows you to trap for errors, instead of just ignoring them, which is what happens with this syntax?

Dale
Dale - just following his current coding style...
@Jack,
re: my comment to Maria
Syntax like:
docmd.Setwarnings false
docmd.RunSQL "Update my Table Set [SomeField] = 'X' WHERE [OtherField] = 3"
docmd.SetWarnings True

Open in new window

is old hat.  There are several issues which this code raise
1.  There is no good way to know that the update was actually successful
2.  I've seen too many instances where the # of lines between the SetWarnings False/True were quite a few, and there is a potential for errors to occur.  If you have error handling in place when one of these errors occurs, you can step outside the code and never get back to the SetWarnings True portion of the code.  If this occurs, it can set you up for all sorts of problems as you wont see error messages, won't see the "You are about to delete 8000 records" warning, ...
Instead of using that syntax, I strongly encourage use of the Execute method.  This can be used directly with the database to execute an action query, like above:
Dim db as dao.database
Set db = currentdb
db.Execute "Update my Table Set [SomeField] = 'X' WHERE [OtherField] = 3", dbfailonerror

Open in new window

, or can also be used with a saved query definition.
dim qdf as dao.querydef
set qdf = currentdb.querydefs("YourSavedQuery")
qdf.execute dbfailonerror

Open in new window

There are several advantages to this technique.  
1.  When you add the "dbFailOnError" as one of the "Options" arguments, Access will raise an error if anything in your SQL is incorrect and causes an error.  You can trap for these errors using an error handler.
2.  You can determine how many records were affected by the action query using either:
intRecordsAffected = db.recordsaffected 
or
intRecordsAffected = qdf.recordsaffected

Open in new window

HTH
Dale
Avatar of Jack Lugo

ASKER

@Dale & Maria
Thank you so very much for your input and taking the time to contribute. Full disclaimer, I'm not at a developer level with Access, SQL, or VBA. I know just enough to be dangerous. I didn't create this DB I'm working on, the developer that did has since moved on, I just inherited it, and have been learning along the way to make improvements to it step by step.
Dale, your input is something new to me, so I will research that 'Execute Method' more and learn how to start using it. For now, I'm just trying to make something a little simpler.

I looked at Maria's solution, and it is exactly what I needed to get started. I realized my code has to determine whether or not the User exists, so I put her code a little higher in the sequence of the form load event.

I ran into a Run-time error '3134':
Syntax error in INSERT INTO statement.
User generated image
I can't find the syntax problem.
Private Sub Form_Load()

    txtUserName = Environ("USERNAME")
   
    'Verify login credentials
    Dim X As Long
    X = Nz(DLookup("UserID", "TBL_C210_UserT", "Username=""" & txtUserName & """"))
    If X = 0 Then
   
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT TBL_C210_UserT  (Username) VALUES ('" & txtUserName & "')"
        X = Nz(DLookup("UserID", "TBL_C210_UserT", "Username=""" & txtUserName & """"))
        DoCmd.RunSQL "INSERT TBL_C210_GroupXUserT (UserID ) VALUES (" & X & ")"
    Else
    
        Forms!frmMain_Menu!txtUserID = X
        
        'Logs User Activity "Log ON"
        DoCmd.RunSQL "INSERT INTO TBL_C210_UserLog ( UserID, Activity, Notes )" & _
            "SELECT [Forms]![frmMain_Menu]![txtUserID] AS UserID, 'Log ON' AS Activity, [Forms]![frmMain_Menu]![txtUserName] AS UserName;"
        DoCmd.SetWarnings True
        
    End If
    
        If IsUserInGroup(1) Then
            Forms!frmMain_Menu!btnAdmin.Enabled = True
            Forms!frmMain_Menu!btnTimeLog.Enabled = True
        End If
        If IsUserInGroup(2) Then
            Forms!frmMain_Menu!btnAdmin.Enabled = False
            Forms!frmMain_Menu!btnTimeLog.Enabled = True
        End If
    
    DoCmd.OpenForm "frmMain_Menu"

End Sub

Open in new window

Does it make sense what I'm trying to do so far?


Try this:

Dim strSQL as string
strSQL = "INSERT TBL_C210_UserT  (Username) VALUES ('" & txtUserName & "')" 
debug.print strSQL
DoCmd.RunSQL strsql

Open in new window

This gives you the ability to evaluate the string that you are actually about to execute.  Put a breakpoint on the RunSQL line so that you can see the result of strSQL in the immediate window, and possibly fix it before attempting the insert operation.

Dale
@Dale thank you.

This procedure is new to me.

Where would I insert this into the event?
Private Sub Form_Load()

    txtUserName = Environ("USERNAME")
   
    'Verify login credentials
    Dim X As Long
    X = Nz(DLookup("UserID", "TBL_C210_UserT", "Username=""" & txtUserName & """"))
    If X = 0 Then
   
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT TBL_C210_UserT  (Username) VALUES ('" & txtUserName & "')"
        X = Nz(DLookup("UserID", "TBL_C210_UserT", "Username=""" & txtUserName & """"))
        DoCmd.RunSQL "INSERT TBL_C210_GroupXUserT (UserID ) VALUES (" & X & ")"
    Else
            Forms!frmMain_Menu!txtUserID = X
                'Logs User Activity "Log ON"
        DoCmd.RunSQL "INSERT INTO TBL_C210_UserLog ( UserID, Activity, Notes )" & _
            "SELECT [Forms]![frmMain_Menu]![txtUserID] AS UserID, 'Log ON' AS Activity, [Forms]![frmMain_Menu]![txtUserName] AS UserName;"
        DoCmd.SetWarnings True
            End If
            If IsUserInGroup(1) Then
            Forms!frmMain_Menu!btnAdmin.Enabled = True
            Forms!frmMain_Menu!btnTimeLog.Enabled = True
        End If
        If IsUserInGroup(2) Then
            Forms!frmMain_Menu!btnAdmin.Enabled = False
            Forms!frmMain_Menu!btnTimeLog.Enabled = True
        End If
        DoCmd.OpenForm "frmMain_Menu"

End Sub

Open in new window


Sorry, where line 11 is.
@Dale thank you.
Still getting the syntax error, but look at the Immediate window, it seems to want to do the right thing.

'Victor.Lugo' is my ENVIRON user name.

User generated image
It's still not getting added as a new record.

User generated image
I still don't see the syntax error.
The Immediate Window in VBA shows good data, does it not?

User generated image

Judging by the screenshot I reckon that you probably seeing the "lookup of users" ...you should have a table e.g. Users that has


UserID    User
1             Jack.Lugo
2            Dewei.Luo
...           ....................
x             Victor.Lugo  ' Lets assume that Victor.Lugo has an id : 123
and so the Username is constructed on the fly :
UserName : LastName & "." & FirstName

Open in new window

So your SQL would be
INSERT TBL_C210_UserT (UserID) VALUES ('123') 

Open in new window

so try this and report back
@John, thank you very much for your contribution.

I should point out, I don't need any other fields of a new record except for [TBL_C210_UserT].[UserName] first, then [TBL_C210_UserT].[UserID], in order for the DB permissions to work. The other fields like FirstName, LastName, and Email can be added manually later. 

I'm still getting the syntax error though.
User generated imageNot trying to confuse, but when I work on the DB from home my ENVIRON USERNAME is 'Victor J Lugo', and at work it's 'Victor.Lugo'

Your contribution as I tried to add it to the form's load event in VBA.
Private Sub Form_Load()

    txtUserName = Environ("USERNAME")
   
    'Verify login credentials
    Dim X As Long
    X = Nz(DLookup("UserID", "TBL_C210_UserT", "Username=""" & txtUserName & """"))
    If X = 0 Then
   
        DoCmd.SetWarnings False
        Dim strSQL As String
        strSQL = "INSERT TBL_C210_UserT  (Username) VALUES ('" & txtUserName & "')"
        Debug.Print strSQL
        DoCmd.RunSQL strSQL
        X = Nz(DLookup("UserID", "TBL_C210_UserT", "Username=""" & txtUserName & """"))
        DoCmd.RunSQL "INSERT TBL_C210_GroupXUserT (UserID ) VALUES ('123')"
    Else
    
        Forms!frmMain_Menu!txtUserID = X
        
        'Logs User Activity "Log ON"
        DoCmd.RunSQL "INSERT INTO TBL_C210_UserLog ( UserID, Activity, Notes )" & _
            "SELECT [Forms]![frmMain_Menu]![txtUserID] AS UserID, 'Log ON' AS Activity, [Forms]![frmMain_Menu]![txtUserName] AS UserName;"
        DoCmd.SetWarnings True
        
    End If
    
        If IsUserInGroup(1) Then
            Forms!frmMain_Menu!btnAdmin.Enabled = True
            Forms!frmMain_Menu!btnTimeLog.Enabled = True
        End If
        If IsUserInGroup(2) Then
            Forms!frmMain_Menu!btnAdmin.Enabled = False
            Forms!frmMain_Menu!btnTimeLog.Enabled = True
        End If
    
    DoCmd.OpenForm "frmMain_Menu"

End Sub

Open in new window

I only changed the code on Line 16, is that what you were suggesting?

It still won't add a new record into the table, if I can get past that part first, then work on matching [TBL_C210_UserT].[ID] with [TBL_C210_UserT].[UserID].

User generated image

Very much appreciate any further assistance. 

Can you make a test to
"INSERT TBL_C210_UserT  (UserID) VALUES ('" & txtUserID & "')"

Open in new window

just to see if this works...
Preferably make a copy of this database and on THE COPY
delete e.g
"Olen.Loftis"
and use this line
"INSERT TBL_C210_UserT  (UserID) VALUES ('7')"

Open in new window

Also do check the table relationships..

A sample should be most helpful
@John, I don't understand what you're asking me to do.
 What you're proposing doesn't make logical sense to me.

The relationships are shown in the first picture of my original post.

I think I might be going on a tangent trying to solve the syntax error instead of accomplishing my initial goal.

The goal is to add a new record to TBL_C210_UserT based on the string value returned from the ENVIRON USERNAME return on form load if that UserName does not exist.

I think there is an issue with the strtSQL statement.

Could someone try to correct my on-load event VBA using recordsets and rs.addnew?
OK, I wanted to delete this question and start over, but I decided to give it another shot and present an alternative using Recordsets.

I researched how to add new records but I'm missing one way to create a new value.

User generated image
If someone could help perhaps grab the last [TBL_C210_UserT].[UserID] + 1, I believe that would solve my problem.

ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you John Tsioumpris!!!!

Wow! Now I've got what I wanted.

User generated image

Happy you got it..