Require Field Entry To Be One Of Entries In A Table? Validation?)
Oct 29, 2004
I am using Access 2002 database with ASP.NET(VB). I usually try to get something working in the access 2002 GUI 1st and I am having trouble with creating the code to require that a field entry be forced to be another exisiting entry in another table. For Example:
UNIT_TABLE has fields UNIT_ID(key), and UNIT_NAME having values of:
1 Tigers
2 Bears
3 Sparrows
BATTLES_TABLE has fields BATTLES_ID(key), ATTACKER_MAIN, and DEFENDER_MAIN
I want the ATTACKER_MAIN and DEFENDER_MAIN choices to ONLY be members from UNIT_TABLE.UNIT_NAME
I set up a combobox and lookup in BATTLES_TABLE as follows:
SELECT UNIT_TABLE.UNIT_NAME FROM UNIT_TABLE ORDER BY UNIT_TABLE.UNIT_NAME;
this OFFERS the CHOICE of any of the 3 clans (ie Tigers, Bears, Sparrows)
but I cannot not FORCE the USE of these....because the end user can option to enter in anything else.(which is not wanted).
How do I add SQL to the LOOKUP such that... the user can ONLY choose to enter text that EXACTLY matches an Already existing entry in the UNIT_NAME field?
I tried to enter a validation rule:
[ATTACKER_MAIN] In ([UNIT_TABLE.UNIT_NAME])
I get an error that the field 'UNIT_TABLE.UNIT_NAME' does not exist.
Thanks for any help with this validation problem.
HKP_MACK
I haven't been able to figure out the code to require entry (for a new record) in a field on a subform - anyone can help with this?
The situation is a contacts database, and each new contact should have a (or multiple) Person Roles entered as well, which is their relationship to the organization.
I have a subform where records can be added or edited. There are two fields: firstname and lastname. I want a message box to appear when a first or last name is edited. I do not want that message box to appear when a new name is being added. I use this code:
[CODE]....
Now I want to also prevent the user from leaving a record before both fields have data (first and last name are required). My efforts to do this seem to conflict with my message box issues.
Can anyone see what I'm doing wrong or how to do this?
There are 4 unbound dropdown list box that will have a default value of empty string. All 4 must have values entered (data is required).Once all 4 are required - the bottom 5 text box will become enabled (optional data).Forcing the user to click on a Validation Button
Some users type then use the mouse to select the next.Other users type to select then tab.The trick is: After filling in the 4th listbox and validating all 4 have values, the event must trigger code to enable the 5 optional text boxes below.This provides a seamless data entry environment.
I have been doing this in other places, but the transisition after the last required field is complete gets ugly.If Trim(cmbBoxname1 & "") = vbNullString ' check all 4..If it is the 4th one, the event seems not to happen quick enough for the bottom 5 text boxes to be enabled so the user can continue with data entry.
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
I'm new to access so I thought this would be easier than it is. I have a list of cities in a simple table . I want use this list (or a query of it) to create a validation rule for a field in a second table "City". How, either with VBA or some other method do I use the Cities database to validate entry. These tables are both in the same project.
I am (kind of) new to Microsoft Access and have a question about making validation rules more complex.
I am making a quality control database with 3 tables. One containing the product and their unique codes, the 2nd with their unique lot numbers for each different production run, and the 3rd with all the required quality control data for each product.
Each different product has different required specifications. For example, Product AB-544 needs a value between 11-20 while product RY-233 needs a value between 21-30.
My question is: Is there a method of defining different validation rules for different product codes? I want the QC tech entering the data to only be able to enter in quality data that is within the required specification defined by the product code. If the current product is AB-544 he should only be able to enter in 11-20 but if he is entering in data for RY-233 he should only be able to enter 21-30.
Sorry for creating yet another thread on table design/relationships for surveys, I did search just needed a bit of clarification.
From what I understand from my search the best way to create a basic db for surveys would be a question table, response options table, and response table, this is similar to a database I have implemented for a simple online surveying system. Now this works with Y/N, or even limited response questions.
However I can't get it to fit with my current project, and I don't think it will, but I thought I'd check to see if I'm being stupid.
I have a survey, 20ish questions expecting 200-250 responses. The questions consist of a mixture of:
Yes/No answers Select all that apply answers Select one from list Select one from list or select Other and enter it yourself Text/Comments
Because of the wide range of values, some completely unique and the possible number of answers per question per survey it seems the above method (and that implemented in At Your Survey which i've had a look at) would not work.
I only need to store responses (not questions), so I'm proposing a main table with related tables for those questions that require it.
So in essence a flat file response table with each field being a different question, each record being a separate survey response. For those questions with multiple answers a separate table, related to the main response response table.
Data entry will be twofold, a web version of the survey automatically recording responses and manual entry into custom form within access for those paper returns.
Reporting will be faily widespread with a lot of cross referencing.
I know this is not a reusable format, and it is pretty much a one time only project anyway, relatively short lived. But I'd still like to create the most efficient product I can, within the time constraints.
instead of providing validation at the client side or in the server script.... cant i provide validation in the tables fields... I have a field named YearID, datatype text..
But I want to allow only text like this ==> e.g. 'F-00' 'F-01'
Do I have to put any format property... please do help... thank u.
I am trying to customize one of my query table, so that it shows the latest review date of an employee (with multiple entries). I have gotten the SQL statement to work so that it shows the latest employee review date which is greater than the current date. But if the employee has 2 records after the current date and I want it to show the latest entry of the 2. I don't know to put add a sub-Select statement or whether to add another criteria in the Where criteria.
e.g. Current Date = May 25, 2005If Employee A's next review date is set for June 1, 2005 but has already been reviewed (the next review date is May 31, 2006). The query will show the June 1, 2005 and not the latest entry of May 31, 2006.
Because there are 2 review dates that are after the current date. What should I do to make only the latest entry appear?
Below is the my VBA code so far, which produces the above result.
SELECT tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.[current store], tblEmp.position, tblEmpSalaryHistory.salary, tblEmpSalaryHistory.next_review_date FROM (tblEmp INNER JOIN tblEmpSalaryHistory ON tblEmp.ssn=tblEmpSalaryHistory.ssn) LEFT JOIN tblEmpWorkHistory ON tblEmp.ssn=tblEmpWorkHistory.ssn WHERE (((tblEmpSalaryHistory.next_review_date)>Now() And (tblEmpSalaryHistory.next_review_date)<=[Please enter the Next Review Date])) ORDER BY tblEmpSalaryHistory.next_review_date;
My brain is stuck and can't figure it out. I hope any fresh mind could help me out. Thanks in advance.
I have a form (called User_Input)with 2 text boxes, 5 combo boxes and a Submit button, which is bound to a table (called Submssion table) i.e the form submits its entries into the table called Submission.
I want the submit button on the form to work based on this condition:
1. Check current submission of four fields (corresponding to entries from 4 out of the 5 combo boxes on the form) in the table and confirm if the current selection on the form already occurs in the table up to two times. If this is the case, the Message Box should pop up on the form informing user to make another selection as maximum number of entries for that particular selection is already in the Submission table.
2. The checks for the four fields will be done simultaneously as the selection will be done together on the same form.
3. However, if the entries on the Submission table is not up to 2, the entries should be saved to the table and Messagebox should display "Thank you for your Submission"
I need to use Macros for this being a web database which will be published using Sharepoint and VB is incompatible with the web database.
I am trying to validate a time entry in a form, where the time is part of a Date/Time field. What I am trying to ensure is that the time entered is in working hours (in this case 0900 - 1700). I want to raise an error message.
Thus: 20/02/2005 08:30:45 is not OK, while 20/02/2005 09:03:45 is.
I have a form which people fill in (made up of fields from Table1) to record when sick days.
On the form, they have to enter the "week commencing" (which is set at Medium Date format); then they have to enter in a first day (which is set at Short Date format).
The First Day should be no more than 7 days from the date that they enter in the Week commencing field on the form.
I want an error message to pop up if they do this, for example:
Week commencing: 1st April 2007 First Day: 10th April 2007
(because the 10th April 2007 is more than 7 days from the 1st April). But it would be ok if they did this:
Week commencing: 1st April 2007 First Day: 8th April 2007
This is pretty simple but I need to place a validation rule in my table for a field so that users can only enter data that starts with APB. What would the rule be?
I have a form that allows users to search the database for records based on there criteria. The form allows the user to search, through combo boxes, by variables. The first is MAT, the second is Relocation Area. The search works fine when the user selects options form the combo boxes. However sometimes it is neccesary to leave the location field blank and only search by MAT. When the location combo box is left blank no records are found. Is there anyway around this. The SQL code for the query is below:
SELECT [Extract Data].[ID], [Extract Data].AREA, [Extract Data].[MAT], [Extract Data].[Relocation Area 1], [Extract Data].[Relocation Area 2], [Extract Data].[Relocation Area 3], [Extract Data].[Grade Score] FROM [Extract Data] WHERE ((([Extract Data].[MAT])>forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 1])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 2])=Forms![Data Search]!SearchArea)) Or ((([Extract Data].[MAT])>>Forms![Data Search]!SearchMAT) And (([Extract Data].[Relocation Area 3])=Forms![Data Search]!SearchArea));
Is there a method of setting the default value for new entries to the same value as the previous entry?
For example, I have a quality control information table that stores QC data for different lot numbers. It is annoying to enter in the lot number and product code field over and over again if there are dozens of records per lot number. Is there a way for Access to easily display the previous entry as the default value?
I have a database that is tracking attendance for several 100 employees. The Db is mainly used to log any policy occurrences (no call, no show). If an associate doesn't have any occurrences for a month, then they get a point credit. Right now, I set it up so the credit can be manually added. the problem is a supervisor (the user) may not know if their associate should receive this credit unless they review their attendance report. Opening the form to add a policy occurrence, then running a report to only re-open the same attendance form is inefficient. What I'd like to have is a way to have a credit automatically added if they did not receive any points for a particular month.
ps. My office does not allow uploading any data/files so I am not able to upload a copy/sample.
I have a text box whose value is stored in a table. The column which it stores its value is of format "text" and has size "5".
What I want is for the user NOT to enter more than 5 characters!! I don't want a msgbox popping up on the screen, I want is that the user cannot enter more than 5 characters...!!
I am wanting a text field in a table to accept only chars A - Z
In my validation rule I use
Like "a" Or "b" Or "c" Or "d" Or "e" Or "f" Or "g" Or "h" Or "i" Or "j" Or "k" Or "l" Or "m" Or "n" Or "o" Or "p" Or "q" Or "r" Or "s" Or "t" Or "u" Or "v" Or "w" Or "x" Or "y" Or "z"
Which works :D
Then validation text I use
Enter Alphabetical Letters only
Also works :D
Is there any short way to write Like A.....thorugh to Z without the long statement?
Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.
Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:
For APR:
4/1/14 Dep A 12 2 4/1/14 Dep B 10 2
However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?
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'm trying to write a query that will validate a field in an existing table. I want the query to show where any of the 412,000 records fail this strict validation:
AAnnnnnnA
...where A is any letter A-Z and n is a digit 0-9
Clearly I can use the LEN function to check for invalid lengths (anything <>9), but I'm looking for a simple neat expression I can use that will check the format. Does an existing function already exist before I resort to writing my own function in VBA (again)?
