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
I want to have a query that will display on the left hand column a bunh of time slots then the columns on the right should be for each day of the week and the classes which fall under those slots should be placed in the correct place - is this easy to achieve in a query?
I need to create a timetable for my sports center database, the center is hired out monday - sunday in half hour slots, i would in effect need a calander with places to input each booking every 30 minutes.
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?
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.
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
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.
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...
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.
I 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.
Hello, 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 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.
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!
I'm a BA so i'm converting my excel issues list into a database with a front end where i can create new issue records this uses a form that sits on top of the issues_table.
Howver, i have created a table called Projects and a table called Test_Plans
Each issue is logged against a project and a test plan. Lets say ProjectA has TestPlan1 and ProjectB has Testplan2.
On my issues form, i can select ProjectA in the project field.
In the Testplan field i can select EITHER TestPlan1 or Testplan2
Testplan1 is the only valid entry.
How do i
a) Once I have selected ProjectA in the Project field only display Testplan1 in the Testplan field?
or
b) If the user tries to enter testplan2 for it to error?
I've tried creating a query and linking the controlsource field to it but it doesn't like that!
I have little bit problem in access here, if i want to create two textbox... first textbox for time and second textbox for unit. When I type (time) in first textbox, second textbox will appear automatically the unit number...
Example
1textbox-----------2textbox
1<time<=60 ------- 1 unit 60<time<=120 ------ 2 unit 120 < time <= 180 ------- 3 unit
I have a tabbed form built in Access 2007. It's a series of about 32 different questions, with some being fill-in, some drop down boxes to select from, some radio buttons to choose a rating from 1 to 5. The reason I used a tabbed form was just to keep things consolidated on one screen where no scrolling was involved. All of the entries on each of these tabs are deposited into the same single table. I have 7 tabs, and all of the fields on the 7 tabs compose a single record in the table.
I have the actual tabs hidden, and instead use a button to switch to the next tab (Continue button at the bottom) once all the fields on that tab have been completed. I have all of the necessary fields set to be required, but Access does not validate the entries until the very end of the survey when they click a 'submit' button that actually saves the responses and returns to the opening splash page to start the survey again.
What I want to happen is for the fields on the current tab to be validated before it lets the user move to the next tab with the continue button. If they click continue and have left any fields blank they should get a message that all required fields must be completed or something similar to that.
I have found a thread here entitled "Form Validation before Moving to another Tab" which sounds similar to what I'm doing, but using the button method to navigate to the next tab, there is no BeforeUpdated event to assign that code to.
Hello Everyone, I am in the final stages of creating a database for school coursework however I am having a few problems with a few features; any help would be much appreciated. The database itself is meant to manage / keep are record of utility bills, it consists of two tables: Invoice and Company.
Problem 1. In the Invoice table there are two fields called Date Due and Date Received the data type is Date/Time, how do I create a validation rule so that Date Due must be after 'Date Received' and vies versa?
Problem 2 I want each record in the database to store an image of a scanned invoice, and the main form called Transaction to show a preview image of the invoice. Much of the code I used is borrowed from the Access Northwind sample database, after some initial success I had to redesign the 'Transaction' form where the picture was shown and now I get the error message: ‘Run-time error 2135, this property is read-only and cannot be set’ I think I know what is causing the error but I have had no luck fixing it, if someone could help me that would be excellent.
Problem 3 Once I get the Transaction form to properly store and display a preview image of an invoice, I want to add a button that opens the image in an external window(ie MSPaint). Currently my only success has been to add a button on the form that launches MSPaint (you have to manually open the image in MSPaint.) Again, if someone could help me sort this out it would be excellent.
Sorry if my problems aren’t clear or well explained.
I have spent many hours looking for answers to my problems in forums, Access help files etc… My computing tutor has little (no) experience with Access so he has been unable to help, hopefully the people on this forum have the expertise.
I am happy to email you my database if you need it to help me with problems 2&3.
as the form opens with the access application window hidden, and I found that the .ldb file didn't close if I simply used the X.
I have a series of fields with validation rules which activate on exit. One of these validation rules is in the box which gets focus when the form opens. This means that I get the validation text error message boxes when I close the form with the X.
If I have started to complete the form, I get a whole host of error messages and validation messages when I try to shut it with the X.
Is there a way of closing the form and cancelling or ignoring all error message boxes. I want the user to be able to close the form if they have decided not to fill it in after all, or have got part way through and want to cancel.
I would really appreciate any help, I am sure this is a very basic question and I should probably be able to find the answer, but I can't and I am very fed up!
Hello Everyone, I am in the final stages of creating a database for school coursework however I am having a few problems with a few features; any help would be much appreciated. The database itself is meant to manage / keep are record of utility bills, it consists of two tables: Invoice and Company.
Problem 1. In the Invoice table there are two fields called Date Due and Date Received the data type is Date/Time, how do I create a validation rule so that Date Due must be after 'Date Received' and vies versa?
Problem 2 I want each record in the database to store an image of a scanned invoice, and the main form called Transaction to show a preview image of the invoice. Much of the code I used is borrowed from the Access Northwind sample database, after some initial success I had to redesign the 'Transaction' form where the picture was shown and now I get the error message: ‘Run-time error 2135, this property is read-only and cannot be set’ I think I know what is causing the error but I have had no luck fixing it, if someone could help me that would be excellent.
Problem 3 Once I get the Transaction form to properly store and display a preview image of an invoice, I want to add a button that opens the image in an external window(ie MSPaint). Currently my only success has been to add a button on the form that launches MSPaint (you have to manually open the image in MSPaint.) Again, if someone could help me sort this out it would be excellent.
Sorry if my problems aren’t clear or well explained.
I have spent many hours looking for answers to my problems in forums, Access help files etc… My computing tutor has little (no) experience with Access so he has been unable to help, hopefully the people on this forum have the expertise.
I am happy to email you my database if you need it to help me with problems 2&3.
Is there a way to add a validation rule in an access form on an unbound text box. This box is currently doing a calculation of others. If the figure is not between 0 And 0.3125 then I do not want the user to be able to move on. For some reason if I enter "Between 0 And 0.3125" in the validation rule it doesn't seem to do anything. I can only assume its because its an unbound text box. Data attached please see Frm_Input and text box total standard hours.
I've two tables, QA (Quality Assurance) and Instruments. I'm using form to add data to QA.
There's a field in QA, named InstrumentUsed. The criteria is that InstrmentUsed should only accept value when 'status' field in Instruments table shows 'working'. If status is 'faulty' or 'need repair', it should pop up the related error message and cursor stays on the same field.
I've not starting learning VBA yet, so I'm trying to make use of Macro's.
How to use data from another table as a validation criteria for a field in form?
Pops up a MessageBox and stays on the same field, on the form, unless error is resolved?
