Hi guys
i had developed report which, pops up form to pass on info to generate report.
in that pop up box i have StartDate and EndDate.
The start date should not be Null
The end date should not be Null and greater than start date.
otherwise through message.
Can anyone help me
Thank you
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 want to verify that the [#Closed Date#] is not less than the [#Initial Date#] in a Form.
=IIf([#Close Date# <#Initial Date#],"Error in Date",[#Close Date#]) This does not work. What do I need to do to prevent this kind of input error?
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'm sure this is on the forum somewhere but have searched and searched... and had no luck.
I want my date feild to be set to enter a date no more than 2months in advance but a minium of a week in advance.
I have done this lol
week(1)<"">month(2)
I don;t actually have a clue, do you use "" to represent the value? also how do i do this date verify thing :P.
Thanks in advance
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.
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 table with 2 field:
1. StartDate (format : Short Date)
2. EndDate (format : Short Date)
How can I validate for field EndDate must occur after StartDate using Validation Rule?
I need to restrict data input to the following: "09:00" Or "11:00" Or "13:30" Or "15:30"
This rule works when the data type is set to text, but not on Date/Time with short date set as format
How to get a Date/Time type to accept only one from these four values.
Basically, i have a field where the expiry date of a credit card is input, but i want to set an input mask so that it loks something like this:
MM/YY but i cant find a way of doing this!
I then have validation text which makes sure that this value is either equal to or more than todays date! this looks like this:
>=int(now())
So the input mask has to accept this validation...
If you could help, i would be very greatful!
Cheers
I need to restrict data input to the following: "09:00" Or "11:00" Or "13:30" Or "15:30"
This rule works when the data type is set to text, but not on Date/Time with short date set as format
get a Date/Time type to accept only one from these four values.
If a new record is created, insert date only if customerID is not null
Private Sub Form_Current()
If "CustomerID", "TblDietPlan" = <> 0 Then
If Me.NewRecord Then Me.MealDate = DMax("MealDate", "TblDietPlan")
End Sub
Hi I would like to ensure all fields on the form are filled before a user exits the db. I have a quit button on my form and it is there for people to exit entirely from access. I need to validate the form and display a message if not all fields are filled. This way i will safeguard against any bad records.
how do i look through all the records to display a message and wait til they follow a procedure before exiting?
rob
I am trying to validate fields in a form, before an email is sent saying the form is OK. this is my code :
Private Sub Save_Click()
If Me.Team = "" Then
MsgBox "You must enter your Team name", "Data Validation"
Me.Team.SetFocus
ElseIf Me.Customer = "" Then
MsgBox "You must enter the Customer Name", "Data Validation"
Me.Team.SetFocus
ElseIf Me.Address = "" Then
MsgBox "Your must enter the 1st line of the address", "Data Validation"
Me.Address.SetFocus
ElseIf Me.Address1 = "" Then
MsgBox "You must enter the 2nd line of the address", "Data Validation"
Me.Address1.SetFocus
ElseIf Me.QueryContact = "" Then
MsgBox "You must enter a Contact name", "Data Validation"
Me.QueryContact.SetFocus
ElseIf Me.QueryPhone = "" Then
MsgBox "You must enter a Contact telephone number", "Data Validation"
Me.QueryPhone.SetFocus
End If
DoCmd.Save
DoCmd.SendObject , , , Me.ReqBy, , , "Invoice Requisition " & Me.HeaderID, "You have submiited an Invoice Requistion to Finance. The requistion was too " & Me.Customer & " and totalled " & Me.InvTotal & ". You will receive a further email confirming the Invoice Number, once Finance have issued the invoice.", no
DoCmd.SendObject , , , "lee.mason", , , "Invoice Requistion " & Me.HeaderID, "An invoice requisition from " & Me.ReqBy & " is waiting to be authorised.", no
End Sub
Can someone please explain why the email sends, even though some of the fields are not completed.
Thanks,
Lee.
All -
I am experiencing an issue with the following code. The code works fine, I'm just not sure where to place it on a subform in a Tab Control to ensure that all validation is done before the user can move to another tab.
Any ideas what event I need place this on? I tried the BeforeUpdate and it popped up first error box, but then proceeded onto the next tab that I clicked.
Thanks!
'Enforce required fields on a form
Dim ctl As Control
For Each ctl In Me
If ctl.Tag = "Required" Then
If IsNull(ctl) Or ctl = "" Then
MsgBox "You must complete all required fields to continue", vbCritical, "Required Field"
ctl.SetFocus
Exit Sub
End If
End If
Next
'All fields are validated, now set ctl to essentially being unbound
Set ctl = Nothing
When user submits record on form, by pushing the arrow to move forward to new form, or back to previous ones, I would like to ensure that they have met a specific criteria in some fields.
How can I catch this without using VB Access? I can use VB Access, but would like to know how "normal" users would force form validation.
Much appreciation for lesson.
-Mike
Hi All,
I have a problem regarding how to validate data that the users of my database must enter. I have two tables that relate to this. The first it a booking table.
This table named tblBooking contains:
Booking ID
Client
Date
Label (Name of the Timetable slot)
Event Name
Paid?
The second table contains all the information on the timetable slots. These slots are 10:30am to 12pm(Morning), 1pm to 5pm(Afternoon), 6pm to 9pm(Night), and 6pm to 11pm(Late Night).
The table is called tbltimetable and contains:
Label
Start Time
End Time
Cost
The problem is that a user can enter the database, open the booking form, and enter conflicting data. E.G two clients can have the same 6pm to 9pm booking, on the same date.
Another problem is that the final two slots of the time table are also conflicting, e.g, one client can have a 6pm to 9pm booking, and another can have a 6pm to 11pm booking.
Obviously, neither of these situations are possible, so I wish to add some validation that will prompt the user to choose another date when they pick a date/timetable slot combination that has already been taken.
I assume that I will have to impliment this using the forms, as it is not possible to add this amount of validaition in the table design. The thing is, I dont have any ideas on how to do this, and I have only just started to learn VB if that is what i have to do. If anyone can give me any feedback and help me to learn how to solve this problem, either through the booking form, or a better way, then I would be very grateful.
On another note, i would have posted the database, but it is 41mb, and only has about 10 records in each table. Does anyone know why this could be? I have already compacted and repaired the database, as it used to be 39mb.
If you have any questions please do not hesitate to contact me.
Thanks for taking the time to read my post.
Best Regards
Chris Grant
Hello there,
This is probably an easy question for most of you out there, but it has me stumped as a novice in this field.
I have a combo box on a form that has a row source defined to come from a query. I have the "limit to list" option set to "YES" in the properties of the combo box.
My issue is this, when a end user enters a value into the combo box manually, instead of using the drop down data, and that value is not in the related query, can I define what is said in the text box that appears, in place of the access default error message?
Second question is related to that, the system will not let me continue until I enter a value in the combo box that is on my query. Can I, from the text box that I have come up, jump to another form where I can update the records that the query is pulled from?
Here are the specifics for my database.
Combo box is on a form dealing with my "tote tracking" table. The box specificlly deals with the "serial #" field on that table.
I do not want the to be able to 'move' a tote unless that tote serial number has been enterd on the "tote setup" table, which the combo box is limited to search from.
If you try to enter a tote number that has not been setup, the "limit to list" feature will not let it happen. I would like my own error message to come up, and woud like to hit a control on that message to go to a form that enters records in the "tote setup" table. Then you will have the chance to 'set up a new tote' and create a record on that table from which the origional form would be able to pull.
Any help you could lend on this would be appreciated, I am new to access and trying to learn on my own...
Thanks
I have a form that requires people to enter a number of values. The sum of these values cannot exceed 100. The values are stored in a table for later use. I also want to catch the error here with a message.
View 3 Replies View RelatedI have a simple form with a save button created directly from a table. I would like the on click event to check 4 fields to be sure that they weren't left blank and have a msgbox prompting to fill in the missing data, obviously not saving until all fields are complete. Fields are FirstName LastName Age Position. If all data is filled I would like the button to save the record and begin new record.
View 3 Replies View RelatedHello, I'm having difficulty with a validation rule in a form. I have a numeric field I want to limit users to entering a number between 1 and a variable second number. The second number is the number of records in the table. So if the table has 5 record, the user can enter a number between 1 and 5. When the number of records increases to say 15, then the user should be able to enter a number between 1 and 15.
What I have done so far is as follows:
Function MyValRul()
MyValRul = "Between 1 And " & DCount("*", "tblRegion1")
End Function
and in the validation rule option for the field on the form I enter
=MyValRul()
This should returns a value like
Between 1 and 15
But it does not work. I do not get any programming errors but when I attempt to enter a number in the field I get the validation error message even if I enter a number that IS valid.
Any assistance will be greatly appreciated. And please be gentle (i.e. keep it simple and include detailed step by step instructions) I am new to Access and vba.
I would like to write some code to validate that when a user enters data into a text field, the user must enter 4 digits.
View 3 Replies View RelatedI want to include a validation on a form. I have an auto number field called ID and a field called Nature Of Incident. I only want to have the auto number generated if the Nature of Incident is NotNull.
View 5 Replies View RelatedI am trying to validate data held in two controls on a form.
The first control records membership status and is a combobox with 5 values allowed.
1 is 'member'
2 is 'under consideration'
3 is 'left' etc.
The combobox displays the description, the underlying table the number 1-5.
The second control records 'reason for leaving' and again is a combobox with 7 entries just as above.
I need to ensure that if a value is entered into the second control the value of the first control is '1' or 'member'.
Whatever I do I cannot get any code to work.
So, what event should I link to the code attached to the second combobox? On lost focus, before update or what?
What value do I check for in the comboboxes, the text or the number?
How do I set focus back to the first combobox in the event of an error?
What I have:
Table named: clients table
Form named: clients data form
The form has the client information. The client sends me a document. I review the document. If the document has the correct info needed, then I have a field named "Incomplete" that is a Yes/No field in the clients table.If the field is clicked, then I want the next date field to appear and then I can fill in the date that it was sent back to them.If the field "incomplete" field is not checked, then I don't want that date field to appear nor do I want to fill it out.
I have a form that acts as a filter/search engine -- it inputs criteria into a query, then opens a report based on the criteria/query. One of the criteria options is "Supplier Number", which is to be entered into an unbound text box.
All of our suppliers have 10 digits, usually in this format: A123400000 (i.e. First character is either a Letter or digit, followed by four or five digits, then followed by four or five zeros)
Our system works with ten digit numbers, while the people in the dept are used to just typing in "A1234" assuming the zeros are irrelevant.
How do I create a validation rule or input mask where a user can simply type "A1234", then it will return zeros for the remaining of the ten required characters?
I realize the usual response to these type of simple question is "Search the Threads", but I have done so already and still don't have an answer!