Require Entry In Either Of Two Fields But Not Both
Jun 6, 2014In a table, I'd like to require an entry in either Field A or Field B. But an entry should be in only one of the two fields.
View RepliesIn a table, I'd like to require an entry in either Field A or Field B. But an entry should be in only one of the two fields.
View RepliesI 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?
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
Attached program needs little code correction; but, I do not know how, Any help will be highly appreciated
The code attached works fine with fields Dentist , patient number and name, but when I try to apply the same code at fields productID and Quantity does not work because these fields at sub form , Can any one help me to handle this problem please ?
All what I need to in force user to fill all fields ( all fields require ) dentist, patient number, patient name, product Id and Quantity,
Regards
vbnt
Hi,
a colleague of mine has Office 2000 installed on her work PC in order to carry out Access datadase responsibilities (amongst other things) as part of her job.
I think (though I cannot be sure) that someone has 'locked down' the copy of Access to such a point that shift bypass no longer works, F11 does not display the database window etc. This is strange because our company generally does a default install of Office and leaves most everything configured as standard.
In short, I need to access the Tools menu to set startup screen options and customize some of other options for her. Obviously I can't.
Will this problem require a complete Office re-install or is there a way to undo someone elses changes and set Access back to 'wide-open' ??
Thanks
Sausagefingers
I am trying to edit the following code to be able to require the user to enter a Loan number and keep prompting the user to enter a value as long as the field is blank. Once the field is filled in, then the code should go on to check if the sql condition is met and make the necessary change if met, then finally, I have a save command code that will require the user to save the record. Right now I am getting errors when I added the code to require the user to enter a field. I am new to loop and if statements in vba so I am not exactly sure how to structure it.
Code:
Private Sub Save_Record_Click()
Dim SQL As String
Do
If IsNull(Me![Loan Number]) Then MsgBox "Please Enter Loan Number. This is a required field."
[Code] ....
I'm creating an employee survey database and I'm using option groups for collecting general information such as office, department, etc. My question is can I make it a requirement for them to answer an option group before moving on to other questions? I'm trying to control and track how many employees have taken the survey. Right now I can skip these general information questions and just go into the survey questions so I'm trying to prevent that. Also in the same train of thought is it possible to not save any incomplete surveys? Say I have 50 questions and someone starts the survey but only answers 10 questions and then exits for whatever reason. I would like that record to be deleted so it does not count. If I allow incomplete surveys then I can get skewed numbers.
As always you help is greatly appreciated.
Thanks,
Chester Campbell
I'm trying to create a validation rule that requires an input of 5 numeric characters OR if the entry begins with a letter it can be any length. For example if the record begins with a number the length must be 5. If it starts with a letter any length is acceptable.
Any help is appreciated!
Thank you!
I have a simple table called CUSTOMER with contact details on there...First NameSurnameDate of BirthAddress 1Address 2Address 3Town/CityCountyPost CodeemailetcI have a second table called TOWN_COUNTY_REGION that lists all the towns and cities in the UK. Each Town/City is listed with its corresponding County and Region. This table has 3 fields...Town/CityCountyRegionThe TOWN field in the CUSTOMER table is populated by the user selecting from a list of towns in the TOWN_COUNTY_REGION table. When a user clicks the town that they are in, I would like the appropriate COUNTY and REGION fields to be automatically populated in the CUSTOMER table. So, for example, if the user clicks LIVERPOOL from the list of towns and cities, Merseyside and Northwest should automatically be populated in the other 2 fields.How do I do this ??Many thanksAllan
View 13 Replies View RelatedI have a form that has a 3 column: ID, first name and last name.
And I also have a table that have a same column (ID, first name and last name).
I want that when i write the ID, access fill the first name and last name fields in a right data - the first and last names of ID entry
Example:
if my table like that:
ID
last name
first name
13456
Elen
Miki
When I write in form in ID text box: 123456, in last name field appear Elen and in first name appear Miki.
Firstly hello, I'm new! :)
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.
Any views or help would be much appreciated :)
i have a quick question... I am having a slight problem with a query that i am using in my database... i am trying to calculate miles per gallon. The query i have set up looks at the previous records odometer value and subtracts the current odometer value to get the miles driven since the previous fill... Here is my sql code.
SELECT fuel_use_tbl.unit_ID, fuel_use_tbl.fuel_use_ID, fuel_use_tbl.fuel_date, fuel_use_tbl.gallons, fuel_use_tbl.odometer, (SELECT TOP 1 Dupe.odometer FROM fuel_use_tbl AS Dupe
WHERE Dupe.unit_ID = fuel_use_tbl.unit_ID
AND Dupe.fuel_date < fuel_use_tbl.fuel_date
ORDER BY Dupe.fuel_date DESC, Dupe.fuel_use_ID) AS PriorValue, [odometer]-[PriorValue] AS [Miles Driven], [Miles Driven]/[gallons] AS Expr1
FROM fuel_use_tbl;
The problem with this code is this... lets say i drive a brand new vehicle (its first record and it has no prior odometer value... the PriorValue reading shows up as nothing (NULL) when i then try to take [odometer]-[PriorValue] it shows up as nothing (NULL) while it should just treat it as [odometer]-0... i need help so that it shows an average mpg for every fill... Thanks for your help.
For a better example this is exactly what it looks like
unit_IDfuel_use_IDfuel_dategallonsodometerPriorValueMiles DrivenMPG
11328/11/200700.00
11269/11/200726400.00040015.3846153846154
11279/17/200722700.0040030013.6363636363636
113110/16/2007231,131.0070043118.7391304347826
101289/11/2007220.00
101299/17/200721500.00050023.8095238095238
101309/18/200728800.0050030010.7142857142857
Fuel Use ID is my primary Key and it is autoindexed... ive just been messing around with fake data and have changed the dates... thats why it looks a little strange
Greeny
Hi
I am an amature with databases and a bit rusty too. Can anyone help me with this problem?
basically I want to automatically enter a unique asset code for one table that is based on product and manuafacturer's codes from other tables
e.g. for audio visual eqipment
The first table would be a manufacturers table with the manufacturers code as the primary key e.g. JVC, HTC (hitachi), SHP (sharp) etc
The second table would be a product table with the product code as the primary key e.g. AMP, DVD, LCD (lcd tv) etc.
( i would also have a customer table and an asset repair table)
In my asset table I would like to enter an asset code as the primary key of the type JVC-DVD-001, JVC-DVD-002, HTC-LCD-001 etc by looking up codes from the other tables and then adding the last number.
(Ideally once the manufacturer's code has been selected only the product codes for products made by that manufacturer would be displayed).
(Also it would be ideal if referential integrity could be set up so that the asset code components can only contain valid manufacturer and product keys.)
The asset code cannot be a compound key as I want to have a repair records table in which the asset code is entered as a single field with referential integrity to the asset field in the asset table.
When entering repairs I would like the lookup field to display only the assets belonging to a particular customer.
Hi,
I have created a form with 3 subforms. This form is split in to 3 parts on a 3-page tab control.
I want the user to enter data in the order below. All fields are required. My problem is that Access97 wants all fields of the main form to be completed in the order set by the table. ie. before I fill in a sub form. I have tried setting the form tab order property but it is being overridden.
First page:
a) Complete first 6 fields of main form
b) Complete subform 1
c) Complete subform 2
Second page:
d) Complete 5th field of main form
e) Complete subform 3
f) Complete 6th field of main form
Third page
g) Complete three fields of main form
Any help appreciated,
Seth
On a data entry form bound to a single table, there is a subform displaying all the records in the source table.If the user accidentally adds a record that they didn't mean to add (or if I add a record to test the form's functionality), and then right-click and delete the record, "#Deleted" appears in all the input controls.
View 5 Replies View RelatedI have an address field containing "City", Street Name, and street Address. How do I link these fields together on a form so that when I enter data into the "City" field the rest of the fields making up the address fills in?
View 7 Replies View RelatedI am fairly new to Access and my "changed" position at work requires that I learn much more about the software. My first challenge is to learn how to make an existing form prompt a user to confirm or cancel changes. I don't know anything about coding but I searched online and found some coded that is supposed to make this happen. I went to "form properties'' and typed this (below) in BeforeUpdate:
'If the form data has changed a message is shown asking if
'the changes should be saved. If the answer is no then
'the changes are undone
On Error GoTo BeforeUpdate_Error
[Code] ....
BeforeUpdate_Exit:
Exit Sub
BeforeUpdate_Error:
MsgBox Err.Description
Resume BeforeUpdate_Exit
After saving changes to the design, I tested by changing the record. I received no prompt.
Hi,
I want to only save changes made to the DB row when the user clicks the Save button I created. I have some text boxes which currently are bound to different columns in the given row (ex: name, address).
Right now, changes are updated automatically when the form is closed or when focus moves to a different tab page.
Is there any simple way to do this? Currently all form input objects are bound to their respective columns (ex: name, address). Should they be bound, or should I just perform a query when the form opens to find the specified item then load the different fields into the proper text boxes.
Also, when I TAB through all my input boxes and the TAB focuses on my Tab Conrol Object the text boxes are automatically updated with the next item n the DB (next row). How can I prevent this?
Thanks!
DRT
I'm trying to build a form based on a table where the user can also enter data to update the table but with data fields not originally in the table. Below is a summary of what I have and what I need:
1.) A file that will be uploaded daily into a Table (ie name Denials extract table).
2.) A form bound to table Denials extract
3.) Additional fields that will be updated by users that is not included in this table b/c they are not available in the system we are getting the data extract from.
4.) The user will look up a key field that will allow them to review it in our system. Once they "work" the record, there will be multiple fields that they will update with this information that are not in the bound recordsource (table Denials extract).
Do I create another table with these fields even though they will be blank at first and make them a subform that will have a "primary key field" that will link the main form with the subform? Will they need to "update" a whole new table? Not really sure how that would work since they will be updating fields not in the bound table.
I need to create a form to enter data from a survey, and ideally some sections of the form would only be completed when the answer to a first question is "yes". How to do this enabling/disabling of fields for data entry?
View 3 Replies View RelatedI was using the following code on a field (ItemCode) that was indexed to prevent duplication of records. The intent is that the user will get an error message that a duplicate exists before they enter all the data for the record and get the built in error message that Access 2003 provides when an index violation has occurred.
Code:
Private Sub ItemCode_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[ItemCode]", "tblQuestions", "[ItemCode] = '" & Me.ItemCode & "'")
If Not IsNull(Answer) Then
MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True
Me.ItemCode.Undo
Else:
End If
End Sub
Now, the index for this is based on two fields (ItemCode and Question Group). I would like to display the same message before update but don't know how to include the second field in the syntax.
1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.
How can I prevent the entry of a duplicate combination of these three fields?
2. I want to have a form to fill the three tables separately, depending the values in the other fields.
How can I do this?
I am in the of designing an access 2010 database for data entry. Is it possible to create a button on a form in which a prompt asks a user for which records to export. Then depending on the entry export specific fields (First Name, Last Name, DOB) to a specific excel 2010 workbook (setup) and worksheet (template)?
For example, if the user entry is 1, only record 1: First Name, Last Name, DOB is exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx). However if 2,3,4 are entered then records,2,3,4: First Name, Last Name, DOB are exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx).
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.
My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.
I am trying to create another table that I can capture daily Out Of Stock data for products.
What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.
How can this be done?