You might also try VChar(). Access text fields are ALWAYS variable length whereas SQL Server gives you the option of fixed width or variable width text. I actually created the code in A2003 so it was before the switch to ACE. The rules may be different now.
I used DDL with CHAR(255) and I was using a text box on a User From to display the current value of the field on the bound form and paste the new value. The value I tried to add was about 25 characters long and Access said the text was too large for the field. I tried again and there were about 200 spaces to the right of the data, so it seemed to be padding the input to fit the field - which is what SQL was supposed to do with a CHAR field.
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).
If the field looked empty but wasn't, you have Allow Zero Length strings set to Yes and somehow you pasted in a string that had trailing blanks. Usually this happens when importing fixed width text files.
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 ...)
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.
This statement is confusing. Access automatically saves the record when you move to a new record. But that would have nothing to do with the code behind the button. Are you trying to stop the save from happening automatically? You can do that but you need code in a couple of events to handle it cleanly. The save button has to populate the SavePushed variable. Then the BeforeUpdate event has to interrogate this button and if it wasn't pushed, cancel the save with an error message. Then you need code in the AfterUpdate event to unset the SavePushed variable. And finally, you need code in the on close event to trap the error that happens when the save was caused by the user closing the form but the BeforeUpdate event cancelling it because the button wasn't pushed.
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.
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.
As I've said many times, this type of prompt simply trains your users to ignore your messages and always press OK.
Validation code needs to be IN or run from within the form's BeforeUpdate event. That way the code ALWAYS runs NO MATTER what caused the record to be saved.
If you want to force the user to use your save button, then the proper way to do it is to add a "pushed" variable. The code in the save button sets the variable to "pushed" and then saves the dirty record using doCmd.RunCommand acCmdSaveRecord. That causes the form's BeforeUpdate event to run. The first line of code in the form's BeforeUpdate event should check the "pushed" variable. If the user pressed the save button, the code continues. If he didn't, then and ONLY then would you prompt him regarding saving. If he followed your workflow and pressed save, then you would NEVER prompt him.
The "pushed" variable is reset in the form's BeforeUpdate event after it is checked. That forces the user to always press the save button, even when he needs to go through the save a second time.
You must have error trapping code in the button's click event. The error will probably be 2501 but I think there is another error that is sometimes raised. You can simply ignore this error. All it is telling you is that an event was cancelled. You know that because you cancelled it so it is OK.
@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.
If you exit the BeforeUpdate event without issuing the Cancel = True command, all you are doing is bypassing the validation code. Access is STILL going to save the invalid record so all your validation is for naught.
It is the
Cancel = True that tells Access to NOT save the record, not your error message.
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.
Using the Form's BeforeUpdate event is better since you can put all the validation logic together. You cannot check for presence in the control level events. So, if you want code that ensures data is entered at all, then it needs to go in the Form's BeforeUpdate event.
Regarding your function. Looks like some change to Access to accomodate people who don't know how to code makes this seem to work. I wouldn't trust it.
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.
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.
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.
This is an over complication required because you are misusing the control event. All you need to do is to put the validation code into the correct event - BeforeUpdate - and then use Cancel = True to tell Access to not save the data. Access now manages the loop. The user can't leave the field without undoing his change or fixing it.
You don't have to take any of my advice. I'm trying to explain the "Access" way. Once you understand how Access form events work, you can use them to your advantage to simplify your code. It is really very easy to stop Access from saving bad data. You only have to understand how to use the BeforeUpdate events. And you need to understand why using the Form's BeforeUpdate event is better because it allows you to all tests in one place. If you put validation code in the contro's BeforeUpdate event you STILL need code in the Form's BeforeUpdate event if you need to require data entry in that control. That doesn't mean that you shouldn't use the control's BeforeUpdate event. I use it in only one situation - the data field is required and it needs to be unique. So, code in the control's BeforeUpdate event checks to ensure that the value does not already exist. I do this because I am not going to let the record save if the value is not unique and it is cruel to allow the user to fill out the whole form before telling him he has to figure out why he doesn't have a valid value for the unique field.
Here's a link to a couple of videos and also to a database that you can play with to help you to understand when events fire and so that will help you to understand what they are intended to be used for. Events are not random. They are "hooks" given to us by the MS developers who knew that we would need to add custom code to forms and reports and they knew where the code needed to go in order to be effective for the desired purpose. Remember this one thing - The form's BeforeUpdate event is like the flapper at the bottom of a funnel. It is the last event that fires before a record gets saved and there is no way to bypass it. It fires regardless of what prompted the save. If your validation is here, you get to close the flapper and keep Access from saving the record until it is valid or you can open the funnel and Access saves the record. Very simple.
I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...
www.access-programmers.co.uk
@Marshall Brooks Please start a new thread with a new question. You are already 200 posts into this one. No one is going to read 200 posts to try to help you.
