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.
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
Any help would be greatly appreciated. Thank you.
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
re: my comment to Maria
Syntax like:
docmd.Setwarnings false
docmd.RunSQL "Update my Table Set [SomeField] = 'X' WHERE [OtherField] = 3"
docmd.SetWarnings True
is old hat. There are several issues which this code raise1. 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
, or can also be used with a saved query definition.dim qdf as dao.querydef
set qdf = currentdb.querydefs("YourSavedQuery")
qdf.execute dbfailonerror
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
HTHDale
ASKER
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.
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
Does it make sense what I'm trying to do so far?Dim strSQL as string
strSQL = "INSERT TBL_C210_UserT (Username) VALUES ('" & txtUserName & "')"
debug.print strSQL
DoCmd.RunSQL strsql
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
ASKER
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
ASKER
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
So your SQL would beINSERT TBL_C210_UserT (UserID) VALUES ('123')
so try this and report back
ASKER
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.
Not 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
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].
Very much appreciate any further assistance.
"INSERT TBL_C210_UserT (UserID) VALUES ('" & txtUserID & "')"
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')"
Also do check the table relationships..A sample should be most helpful
ASKER
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?
ASKER
I researched how to add new records but I'm missing one way to create a new value.
If someone could help perhaps grab the last [TBL_C210_UserT].[UserID] + 1, I believe that would solve my problem.
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