Note: Do not ignore the DBMS.
ntext/text in MS-SQL (or in MySQL) is equivalent to LongText in Access/ACE.
nvarchar(..) in MSSQL is in Access/ACE varchar(...) or Text(...)
nchar(...)/char(..) is a data field with fixed length (or Null).
Okay - I changed all the DDL strings to "text(x)" and updated the back end from the original version.
I no longer get the odd behavior with spaces padded to the right of the field I added to the text box on the bound form.
However, now when I click the save button to manually save the record, I get this error:
It occurs on this line:
DoCmd.RunCommand acCmdSaveRecord
I commented out the Form_BeforeUpdate and Form_AfterUpdate code, and it runs through those and then gives me the error message.
I have the same code on another form with the same added field, and I don't get the error there, but i haven't added the events to the text box yet on that form (On Click, On Double Click, After Update, On Change, etc.).
Save is behind a button. When the button is clicked, it runs down to the DoCmd.RunCommand acSaveRecord line. Then it runs the Form_BeforeUpdate() code, then it runs the Form_AfterUpdate() code, then it returns to the button code - the button is named UPDATE and the event is UPDATE_Click() <Don't ask, I know ...> and then the error message shows up - so it seems like the save is not working.
There is a different control (a label) on the form where the Click Event of the label also calls acCmdSaveRecord, and it also fails there and Me.Dirty = False also works properly there ...
I didn't step through it with the Me.Dirty = False line swapped for acCmdSaveRecord, but that seems to work fine - like acCmdSaveRecord used to for the past 20 years or so ...
Actually, it seems like the save IS working - i.e. I don't get the prompt to save the record when I go to a new record and the changes are saved, but I get the error message and none of the code after the save line runs.
(I could probably avoid using Me.Dirty = False by trapping for Error 3939 and clearing it and resuming next, but since I don't know what is causing it to begin with ...)
Clarification: I already have code that if the record is "dirty/changed" and the user tries to go to a new record, the database will show a pop-up and say "Do you want to save the record?" I also have a button to manually save.
If I press the Save Button, I get the error message, but I DON'T get the "Do you want to save?" message, so I said the SAVE is working - or more accurately, I guess at least the Form_BeforeUpdate event is firing normally.
@Pat Hartman - I think we are saying the same things ...
I don't call it pushed, but ... I have a global Boolean variable called DontPromptUser.
If the user changes a field, the background of the field is changed and the SAVE button is enabled. If the SAVE button is clicked, DontPromptUser is set to true and the record is saved. FormBeforeUpdate has a step to exit the sub if DontPromptUser is set to true and FormAfterUpdate sets Don't Prompt user back to False.
I do NOT have a Cancel = True line in the Form_BeforeUpdate Event. Not sure if that is the error cause or not (but I don't have it on forms that don't generate the error.
I think I used to have it, but I ran into an error where if I tried to duplicate a record and had that line in there, then I ended up on the initial record instead of the new one.
The validation code is in the Field_AfterUpdate events - we didn't used to have any.
Essentially, Form_BeforeUpdate looks like this:
Code:
If DontPromptUser = True then Exit Sub
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
Me.Undo
End If
Me.btnUndo.Enabled = False
Me.btnSave.Enabled = False
New issue - this is more of a WHY question - it seems to be working the way I intended.
I have a user table and I am using Yes/No fields to show "rights" for each user - i.e. if this box is checked, the user can do certain things. I adapted the UserName Code from the previous replies, so the code looks like this:
Code:
Public Function GetUserName() As String
Static WinUserName As String
If Len(WinUserName) = 0 Then
WinUserName = GetWindowsUserName()
End If
GetUserName = WinUserName
End Function
Private Function GetWindowsUserName() As String
' https://www.devhut.net/vba-recognize-user-get-username/
' https://www.access-programmers.co.uk/forums/threads/audit-update-of-a-field.329588/page-3 - LCase is not technically required, it should work the same way regardless.
GetWindowsUserName = LCase(CreateObject("WScript.Network").UserName)
End Function
Public Function IsAdmin() As String
' Returns True, False, or Unknown - Only updates once, at startup or when variable is reset.
Static VerifyIsAdmin As String
If Len(VerifyIsAdmin) = 0 Then
VerifyIsAdmin = LookupIsAdmin()
End If
IsAdmin = VerifyIsAdmin
End Function
Private Function LookupIsAdmin() As String
' Returns True, False, or Unknown
LookupIsAdmin = Nz(ELookup("[Admin]", "[tblUsers]", "[UserName] = '" & GetUserName & "'"), "Unknown")
End Function
Sub VerifyPrivileges()
' Either = True or = "True" works
' MsgBox IsAdmin
'MsgBox LookupIsAdmin
If IsAdmin = True Then
MsgBox "True"
Else
MsgBox "False"
End If
End Sub
I posted Allen Browne's ELookup function previously.
My question concerns Sub VerifyPrivileges()
Admin is a Yes/No Field in tbl Users.
LookupIsAdmin returns a string function based on this field, with value of True, False, or Unknown (I would have expected "Yes, No, or Unknown."
IsAdmin returns a string function also, and basically returns the value of LookupIsAdmin - so that the table only has to be queried once unless the static variable gets reset.
VerifyPrivileges seems to work properly - i.e. if the Admin box is checked, either "Is Admin = True" or "Is Admin = "True"" is followed, if the Admin box is not checked they aren't.
I'm curious why this works. With a string variable, I would expect it to require:
If IsAdmin = "True" Then
I would expect:
Is IsAdmin = True Then
to either:
My first guess would be a compile error b/c the variable True is not defined (unless I awkwardly had a variable named True elsewhere, in which case it would give unpredictable results).
My second guess would be that it would give a compile error with a type mismatch since True is usually a Boolean result and I am looking for a string.
It SEEMS to be converting True without quotes into the string variable - which is nice and convenient, but I'd like to verify this is what it is actually doing before I start using this in my code ...
You are using the wrong event. Validation code belongs in the BeforeUpdate event where you can Cancel the event if the validation fails. AfterUpdate events cannot be cancelled. The horse has already escaped from the barn. No point in closing the door.
As an example, let's say my field has to have one Underscore in the value ... If the user types in a value without an Underscore, the AfterUpdate() event for the Field shows an Input box with what was entered into the field and says the User should add an Underscore somewhere. Once they do (the Input box is inside a Do While loop), the field is updated.
True and False are boolean variables. If you assume that strings will evaluate the same way, you are taking a risk that I would not take. This is the kind of subtle bug that could come back to haunt you next year if the next MS programmer "fixes" the fix.
Thank you - that is what I was thinking also, but I wanted to be sure my thoughts were correct. I'll change everything to:
If IsAdmin = "True" Then
I was aware of True being Boolean and also thought it could be interpreted as "If the function worked ..." - i.e. IsAdmin would be True if the IsAdmin function returned a valid result - whether that result was "True", "False", or "Unknown". It would be False if the IsAdmin function was unable to return a valid result.
It doesn't appear to be working as I described it above - but that is what I would have THOUGHT would happen and as you stated, it might get "Fixed" in a future update. It's better to be explicit and not have to correct it later. (Which is why I asked.)
Wrong thread, but I'm getting a new error when I delete a record:
Stepping through the code under the delete button, the record is deleted, the code steps through the Form_Current() event, updates the custom navigation buttons, and then returns to the delete button code and displays the error message. The record seems to have been deleted and everything is fine if I click okay.
The Error Number is 3078.
I don't know where it is calling a query from.
I think I could get rid of it by just adding:
If Err.No = 3078 Then
Err.Clear
End If
In the Error trapping routine, but I'd prefer to solve the error rather than ignoring it.
Quite right. However I do have a question, and if the answer is on one of these threads, I will gladly "peel the onion"...
Q: Is it possible to determine WHO made the change with a DM?
I have used the attached to build my DM's and they work great. But what I cannot figure out is how to get the User without writing it to the table that is being audited.
Probably a simple question, but I wasn't sure what search terms to use.
Currently, our database has date fields for (example) "Subprocess A Completed". Currently, any user can update these fields. My supervisor requested two changes:
Restrict the field update to only certain users - probably by user name. I know how to obtain the user name, and I can probably figure out how to implement this, not sure if I would use the beforeUpdate or AfterUpdate event - I'm thinking BeforeUpdate.
Have some way to audit the information - i.e. if the Field has a date of 4-Dec-2023, be able to say "Marshall Brooks updated that field at 12:53 P.M. on 4-Dec-2023" (probably okay to show usernames instead of actual names and probably don't need exact times). I think this is possibly, but I don't know how to accomplish it.
I would use the form's on current event to disable the field for those that cannot edit it.
If you are adding security I would add a group table to handle each type of user. Add users to each group to give them access to that group's objects.
Now that your supervisor as requested security on 1 action, expect more. Add a menu table to list Forms, Reports and Actions, then add those to your groups. Now the function to check access rights can handle anything you add to those tables.
But how does the BE know who made the change in a split db/multi-user environment? No one is really logged on and even if it were to return a user, how would it know WhoDunIt?
It works. Been a while since I set it up, but we are split db/Multiuser and it works fine. Might have issues if you don't have unique usernames and or nobody has to log onto the PC with the FE, but it works otherwise.
It works. Been a while since I set it up, but we are split db/Multiuser and it works fine. Might have issues if you don't have unique usernames and or nobody has to log onto the PC with the FE, but it works otherwise.
Well, as I said, I do not mind being wrong. The thing to remember is to place the Function in the FE and then in the DM, set the field to that Function.
I guess I simply over-thunk it and ASSuMEd that a Function in the FE could not impact a Table/Data Macro on the BE. Quite easy actually... Thanks for the nudge!
