I have code in a BeforeUpdate subroutine of a text box to validate the date. The user can choose to keep or discard the value but the warning only appears if the date is in the future or at least a month old. This is my code:
Private Sub txtDate_BeforeUpdate(Cancel As Integer)
Dim dtCurrent As Date
Dim objResult As VbMsgBoxResult
If (Trim(txtDate) = "" Or IsNull(txtDate)) Then
Exit Sub
End If
objResult = vbYes
dtCurrent = CDate(txtDate)
If (dtCurrent > DateTime.Date) Then
objResult = MsgBox("This date is in the future. Are you sure you wish to leave this value?", vbYesNo, "Warning")
ElseIf (DateDiff("m", dtCurrent, DateTime.Date) > 1) Then
objResult = MsgBox("This date is at least one month old. Are you sure you wish to leave this value?", vbYesNo, "Warning")
End If
If (objResult = vbNo) Then
Cancel = True
End If
End Sub
It works fine when I answer "Yes" but if I hit "No" I get this message: The value in the field or record violates the validation rule for the record or field.
This control doesn't have a validation rule. In fact, there are no validation rules in any of the controls on this form nor on any of the attributes in the control source (table). The date is a required attribute of the table though. I can't hide this extra error message by turning off warnings because it happens after I exit the subroutine, so I wouldn't be able to turn the warnings back on. I also can't trap this error because it occurs after the subroutine ends.
If possible I would want the text box to be cleared and be focused if the user selects "No" to discard the date.
My table was fine and I went in to add a field. I did so and got this error when saving:
"Compile error. in table-level validation expression."
I went thru all my fields and I have no validation rules established. I tried removing the field I added and still have the same error. I used the Compress & Repair function and still have the error. After I go thru the dialogs and exit the table design, I re-enter and the new field is there.
I have a VBA code that runs an append query to update a table. Due to relationship integrity a validation error shows (a type does not exist in the connected table): What I want to do is trap the error and write something that the user can understand. Help !
The Code: Code:Private Sub Report_Deactivate()Dim Msg, Style, Title, Response, MyStringMsg = "Click OK to Import Verified Data to the Invoice Table"Style = vbYesNo + vbMsgSetForegroundTitle = "Verify Import"Response = MsgBox(Msg, Style, Title)If Response = vbYes Then 'This is the problem line DoCmd.OpenQuery "qry_Step_5_appending_invoices", acViewNormal, acEdit DoCmd.OpenQuery "Qry_Step_6_update_fob", acViewNormal, acEditElse MyString = "No"End IfEnd Sub
I am using following routine to lift data from Excel files into Access tables. Whole thing works, well, most of the time. The only issue I have is the spreadsheets are received from warehouses and even though they have been given strict instruction to stick to the template, I have had to adjust the spreadsheets. Amongst errors I get are:
Field 'F16' does not exist in table 'SA1'. (In this case I simply delete the last most empty column to fix this).
Or there are column name spellings and in such cases, I get no error and the simply code hangs.
Is there any routine that I could incorporate in the code that clearly states what issues are being experienced. This way I can pass the db to the user to run it themselves.
'Dim dbs As Database, tdf As TableDef Set dbs = CurrentDb On Error GoTo Macro1_Err DoCmd.SetWarnings False ' RunSQL executes a SQL string
In of my tables called Employee Payroll, I have several earnings fields, and in a previous post I mentioned that I have implemented a validation rule for them which is £2.50 or £2.75 or £3.10. I have also used an error message in Validation Text to help the user if they enter an invalid value. How can I change this message to reflect what the user types in? For example, if they enter an erroneous value like £9.34, the error message would say, "You have entered an erroneous value" or if they type an extreme value like £3.40 or £0.01, the error message would say, "You have entered an extreme value". I understand that I would need to state when the data becomes erroneous and when it becomes extreme- so I thought, anything above, £4.00 should be erroneous and any negative values too. Extreme data would be anything between £0.00 and £3.10. Would I need to use some kind of code in VBA? Thanks
I use before insert code procedure to insert default value for one, or more fields in row. It is normal to have these fields are disabled. This event occurs when you try to enter a value in any another field in a row. However , when I finish entering the field going to the event before update regardless and trigger a validation of all fields in a row. I am not leaving from row , I want to go further input in other fields.How to insert default (calculated) values in new row.
Basically i have a booking system and on certain days i need to allow an "extension", to do this i am using a yes/no and a check box. I need to creat a validation rule so that the box is only tickable if my Event_date field is a friday or saturday.
Do i need to use something like Weekday(Date()) = ?
I am a doctor working in Sri lanka wanting to use access to make our clinic work faster. Only fields required are clinic number (Unique), patient name and last clinic date. What i want is
1. In the form when i enter the clinic number the name should come up where i need to confirm to proceed.
2. Once a patient has come to the clinic that clinic number cannot be entered till 4 weeks are past!
I tried and tried to write macros or vb...(i'm a novice in these) but failed. This looks simple though. Can anyone help? any suggestion welcome.
ps:- this is a charity work. i'm not earning money from making this database!
I am looking at the public function routine, that validates the password entry. I want to know how i can make a message pop up with the specific error the user has made on entry.
Public Function ValidatePwd(varPassword As Variant) As Boolean Dim blnValid As Boolean Dim blnValidCriteria As Boolean Dim intChar As Integer
I have a date field in a table which is Short Date with an input mask of 99/00/000;0;_
I do not want (for reasons too complicated to go into here) to put this as a required field even though it is a required field (makes sense from my end!).
If I set Required Field to "No" what format can I put in the validation rule field so a message (from my validation text field) comes up if the field is left blank??
I have a access db with linked tables from an oracle9i db. When I create a query it never works whenever I select a date field, instead I get an 'ODBC call failed' message. When I remove the date field the query works fine. Anyone know how to write the query so I can get dates to. Thanks for the help Eric
I've got an access database (access 97) running on windows 98, windows xp and one windows 2000 machine.
How come on the windows xp and windows 2000 machines where i have the formula =date(), i get the error #name. on the windows 98 machines it works perfectly.
When opening a certain Access database a user is recieving this error: Function isn't available in expressions in query expression '(((PRODUCTION.DATECREATE)=Date())'.
This only happens on a specific computer. I've tried uninstalling and reinstalling Office, Access, I've even tried just installing Office 97. I also went through and reinstalled all the ActiveX controls that have to do with the date (MSCAL.ocx gives us trouble sometimes).
I'm not that familiar with the programming for the database, it was done quite some time before I got here, but I know enough to understand what is happening. I think the problem lies with the Date() function (this still happens as admin on her machine so it isn't a permissions error) but I have no idea how to fix it.
I remember reading something not long ago about a similar issue that had to do with differing versions of library files for VBA. I was going to try and replace the library file containing that function on her machine with one from mine, until I found that it apparently isn't stored in a file.
Any help would be greatly appreciated as I'm at my wits' end with this thing.
I don't get it!!!! I use it all the time. <=Date() should get all rows with no future dates...but I get this error on some of my queries...I've never got this error before... :mad: :mad: :mad: :mad:
I got this to work easily on another form but on this particular form it doesnt work. I enter a command button and name it d1, then I enter this code for it to populate the current date in the field next to the box:
Private Sub d1_Click() Date_Entered.Value = Date End Sub
Can anyone see why it doesnt work :eek:
ERROR IS: Run-time error '2465':
Database cant find the field 'Date' referred to in your expression.
When I enter a date field it enders whats in that field (blank) into the field I told it (Date_Entered) on my form.
I'm using this date format in my report footer. =Format(Date(),"Long Date") & " " & Format(Time(),"Medium Time"). It gives the current date and time on my computer, but causes an error on the other computers in the office. Any ideas why?
