Over 1 million tech questions and answers.

Field Validation based on another tables data

Q: Field Validation based on another tables data

So far i have read you can't do this. So i'm wondering how you achieve this the correct way. I work for a gas field company and the database i am redesigning has a lot of fields that need validation, like well site names, company names etc. The person who built the original database had no validation at all, and you could find the word Production spelled 12 different way sometimes. Made it very hard to keep data readable.

So the part im working on now is the well sites list. I have a table called Well Sites. I have a main work orders table where they must choose a well site which is a lookup of the well sites table. This is fine and dandy, however. If they choose not to use a name in the drop down list, they can simply type anything they want, which is what i want to avoid. I want them to ONLY be able to type names that exist in the well sites table.

I was hoping it would be as easy as setting up a validation rule but from what google is telling me that is not even possible. So how do i control what user's enter. I do NOT want to type in a list of 600 well site names using OR for a validation rule. That is unmanagable, but i need to restrict what they can type so we don't get mis-spellings and mis-labeled wells.

Now i understand some things but some things i've just never done before prior to now. If i setup a one to many relationship between two tables then it seems to work like i want. Only values that exist in the other table can be entered into the main table. Problem is the well sites are 3 different fields in the same table, so when i link well sites to 3 different fields it will not allow me to create a 1 to many relationship. So i am just really confused on how to achieve the same effect as a 1 to many relationship since it wont let me create one.

Preferred Solution: Field Validation based on another tables data

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Field Validation based on another tables data

Read other 6 answers

Is it possible to have a user choose a value from a dropdown box outside a pivot table (which matches an existing pivot table field) which places that column into the pivot table rows area?

I have a matrix table with 4 main column headings (ABCD) that i can place into my pivot table rows area - that works well.

However, I have 40 other headings across the top of my data table (EFGHIJK.....) which the Pivot Table shows each as its own field in the field list box - they are all the same type of heading (role positions for example, manager, director, admin etc) underneath each heading in the matrix it displays a Yes or No value in rows that relate to that row topic/name.

I need to return all rows where under a given selected column (position) there is a Yes present in the cell.

My table that I am running a pivot table from basically has both row headings and column headings and I cant figure out how to generate a simple dropdown box outside the pivot table (at the top for example) that would put that field into the pivot table.

Note: a user could just scroll down the long field list window and tick their position name and then filter for Yes - but I'm trying to make it simpler.

Any advice is appreciated, can what I'm asking actually be done?

Read other answers

Hi there
sincerely hoping & wondering whether someone out here could help me by offering me your solution or suggestion ?

My situation is as follows:
In my worksheet there is about 600 cells scattered all over the sheet that are to represent different monetary value figures, that I have used NAME MANAGER to define as MONEDATA1, MONEDATA2, and MONEDATA3 (each holding around 200 cells, which is the approx.limit allowed by NAME MANAGER for Excel 2010). Thing is, my work revolves around dealing with 12 different countries of currencies, and so all these monetary cells, in CURRENCY format, thus need to be 1 out of 12 different currencies with respective currency signs, at any one time I do some calculation. So, they must convert into 1 of those 12 currencies, each time determined by a selection from a single dropdown list of 12 currencies located at the top of the worksheet in one particular cell I data validated, which is $C$6.

Now, I just need the worksheet to convert all the 3 groups MONEDATA1, MONEDATA2 and 3 simultaneously, instantly when 1 of the 12 currencies is chosen in $C$6, by using a VBA code. Is there a best way to resolve this need ?

Could you help me with the above problem ? Thanks in anticipation and advance ! Greatly appreciated any help.
cheers & have a nice day there,

A:Excel data validation and executing a code based on selected value


Read other 1 answers

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?



A:Solved: Excel 2007 - Dynamic formula based on Data Validation List

Read other 11 answers

Good afternoon all!

I am working on writing a Security Incident Report program, and I am running into a problem. I have a Date/Time field, which I am attempting to auto-update from another Date/Time field, based on the result of a Yes/No field input. Is this even possible? To be specific, the user enters the date of the report. On the next form page, it asks the user if the report date is the same as the incident date. If the user answers Yes, I would like it to enter the data from the report date field and grey out the option to enter the new date. If the report date is different from the incident date, the user would then be required to enter the date of the incident. This takes place three different times on the same form, based on the incident date, date responders cleared from the incident, and the date the incident was placed under control. I am running Access 2007. Thank you!

A:Creating Access 2007 Auto-populate field based on different table Yes/No data

GrahamTechnology, welcome to the Forum.
Yes it is using VBA.

Read other 3 answers

I have a table with a field called "current fund balance" which is intended to be a data entry field. I have a query with multiple calculated fields. Both the table and query share a common field. I'd like to have a datasheet form to where data from the query and table are combined and have that field "current fund balance" be a data entry field. I'm having the hardest time figuring this out.

I've read a form/subform would work but I need to be able to see all the records at once. And so, datasheet with all fields would be best. Anyone have any thoughts?

A:Solved: Having a form field in Access to enter data based on a query with calculated

Read other 16 answers


I'm a beginner when it comes to VB Script and so I am struggling to work this out. I basically have a worksheet that contains information about different staff members. The sheet is set up so that column A (from row 8 on) is their name and columns B to L (also from row 8 on) are other staff attributes. Due to several reasons, there is more than one listing for each staff member (up to 40). The database is supposed to be there so that any staff member can come in and look up their information (or that of any other staff member). However, with so many listings and so many staff members, it becomes an unmanageable database. I could just create filters so that staff members could simply filter out other employees; however, this would create issues. Instead, I have created a cell above the table (say C2) where staff members can enter their name via means of a data validation list (which is linked to a list of staff members on another sheet). I would then like all rows in the table to hide except for those where the name in column A = C2.

I have searched this on the net and I keep seeing similar responses that all say it's hard when there is data validation / formulas involved. Is there a way this can be done?


Read other answers

I attached my database what i would like to do is create a form when an end user will enter a corresponding user ID from that I wish to populate textbox fields with Agent Name, Supervisor, Location, Dept *all seperate text boxes of course* I have attached the database if someone could help me do this it would be great. Let me know if you need any further details to help get me through this.

A:update field A in form based on entry in field b

Welcome to the forum, before answering you question can I suggest some improvements to your database.
Currently you do not have any "Key", "Indexed" fields in your tables and there are no relationships set up between the tables.
Also do you really need the Archive Table?
The data looks like it has been exported in from a spreadsheet, relational databases work quite differently to spreadsheets, so to make the most of their advantages you need to correctly relate your Tables.
Your UserID Roster also has some disconnect between the Field Names and the actual data in them, particularly the Name and Type feilds.
In the Supervisor Table the Birthdate does not seem to have Translated correctly.

Read other 2 answers

trying to access fields of the table but not able to this....tried these codes..

Sub FieldNames()
Dim Rst As Recordset
Dim f As Field

Set Rst = CurrentDb.OpenRecordset("TableName")

For Each f In Rst.Fields
MsgBox (f.name)
End Sub

Sub FieldNames1()
Dim fld As Field
For Each fld In db.TableDefs(tbllist.text).fields
fldlist.AddItem fld.Name
End Sub0

thanx in advance

A:Getting Field Names from Tables in MS Access

What was wrong with the first function?

Read other 1 answers

Of all of the years I've worked with Access, I've never needed to do this; well here I am being asked to get a list of field names from a table. I'm aware of a few ways (set a list box to be field list, and using the db analyzer). I also found some code that doesn't work to read the database fields and dump them into a file (I'll paste to this note - apparantly I'm not running the correct version of ADO to use it).

Any help would be greatly appreciated. It dies on a "User-Defined type not defined" error inside the funtion at "Dim MyDB As New ADOX.Catalog"

I found the code on a developer article, but not sure I'm implementing it correctly. I feel like I'm out of my league on this one, but willing to give it another shot.

Thanks in Advance

Option Base 1
Public Sub Enumerate_Table()
Dim aryFields()
Dim lngCount As Long
lngCount = 0
strInput = InputBox("Please enter the name of the table for which" & vbCrLf & _
"you wish to list FieldNames & Descriptions." & vbCrLf & vbCrLf & _
"Output will be placed in tab-delimited text file.", "Table Name Input", "MainTabl")
If StrPtr(strInput) = 0 Or Len(strInput) = 0 Then
Exit Sub
strSQL = "SELECT * FROM " & strInput
Dim Adofl As ADODB.Field
Dim rs As Ne... Read more

A:Solved: Getting Field Names from Tables in MS Access

Read other 6 answers

First off I am new to this forum and somewhat new to access2010. We are trying to do a query basedon two tables. The tables are joined bya field on both sides named “jobno”. In tableone we are using a field “totalcost”. Intable two we are using a field named “totalcost”. Totalcost in table one has 6 records, totalcost in table two has 9 records. When werun the query we get 54 records. We havetried all possible join selection with the same results. Thanks in advance for any help.

A:query based on 2 tables

paulbol, welcome to the Forum.

There are also 2 other properties that you can try, they are in the Query's properties (click below the Criteria rows to see them)
One is "Unique Values" and the other is "Unique Records".

Read other 1 answers

Hoping someone can help. I have Count of one amount and Sum of another. I am trying to do a calculated field to determine the % of Count / Sum. For the entire Pivot table I get #DIV/0! - even though I have true to life numbers in these fields. Any suggestions?

A:Solved: Calculated Field Issues / Pivot Tables

figured it out... you can't calculate when pulling a count or sum from text.

Read other 1 answers

Now in the form, only Names is updated in table tblOrders. I want to update BizUnit in tblOrders too, what should I do?

A:[ACCESS 2010]2 tables with same 2 fields but only one field updated.

Why do you want to add the BizUnit to the Orders table?
I notice that you also have emails in there as well, they also should not be in there.
Only data relating to the Order should be in the orders table.
The data is then brought together using a Query for Output purposes like Reports & Invoices etc.

Read other 1 answers

I have a spread sheet where I would like a message to pop up after I enter a value in cell G2 based on what the value of I2 is. like the following description:
In cell I2 I have "=(E2*F2*G2)/144" after I enter a value in cell G2 I want a message to come up if the value of I2/g2 is less than three and not equal to zero" Is this possible? If so could someone help me out with this?


A:validation based on another cell

This is the same as the challenge file but I added the code for this question

Read other 1 answers

I'm trying to make a simple form but one of the fields needs more complex validation than it allows me to do. Basically, the user chooses a material, enters the number of units and their combined weights. Another field calculates their average weight (total weight/units). I have a table specifying the range that each material's average unit should weight. I'm trying to make access validate the calculated average weight field (or something equivalent) according to the material chosen by the range specified in the other table.

I'd rather not forgo the table with the ranges by hard coding the ranges in VBA so that users could change the ranges.

I've put a relationship for materials from each table and display the range in the form but now I can't enter in a new entry.

I need help.

A:Access 2007 Complex form field validation

Read other 16 answers

I want to add a validation rule to a date/time field in Access 2010. This worked in 2003 and I'd like to know why it won't work now.

The rule is

Is Null Or <= Date()

I have entered this through the expression builder as well as typing it to avoid typos.

The error message is:

Unknown function 'Date' in validation expression or default value on 'Artists.Date edited'.

I am doing this at work: we are running Windows 8, Server 2012, Access 2010 via virtual desktop.

Any help would be gratefully received!

Thank you

A:Solved: Access 2010 date field validation problem

Alison, welcome to the Forum.
I think you will find that this is an Access problem of Access 2010.
You may be able to overcome it by using Now() instead of Date().
If not I can provide you with some simple VBA code that will do the same thing that may work.
I have also seen advice to open a new 2010 blank database and import everything in to it to solve this problem.

Read other 2 answers

Maybe it is just late in the day and the brain has quit. But I need help. I have a form that I have to base on two tables. So I wrote a query to pull the fields needed from each table, since visually trying to use sub-forms could get very messy. Now the problem is how to I save any of the data the user enters/changes in the form. In other words, I want to be able to update the appropriate table, but can't remember how to do that.

Thanks in advance for any help.

A:Access 2000 Form based on 2 Tables

For Access to update changes using information in both tables, there needs to be a relationship between them. Although you have created a query using fields from both tables, unless they are linked Access won't know how to treat the information

Read other 2 answers

i want to be able to link tables in Word 2016 so that if I enter text in a particular cell in one table it automatically populates a specific cell in another? Is this possible or would I need to create thelink using Access or Excel? Not much experience with Access.

Read other answers

I have 2 tables that are linked by a zipcode field. One one table there is a kinda count field. If the zipcode field on the first table is updated, then I want the count on the second table to increase by one.How can I go about doing this? Thanks for all the help.

Additional info left out: The zipcode field on the count table has a list of zipcodes. When a zipcode is entered on the other table, I want it to match the zipcode in the count table and update the count for that zipcode. Thanks.

A:Update one field in a table based on another field in another table.

Not quite sure what you are trying to do, but is linking the zip code fields a good idea? Are you tring to find the number of times that a particular zip code is entered?

Read other 2 answers


All of a sudden my Access 2007's Expression Builder just stopped showing the fields names for reports, queries, tables, etc. This was working one day and just stopped. I wonder if I may have flushed an add-in or something. Help!

A:Access 2007 Expression Builder not showing field names for tables, reports ot anythin

I have had some pretty squirrely issues with the expresion builder also. I like to use it as a general guideline but usually just type in all of my expressions by hand. Are you linking to external database(s)? I had a problem like the one you are experiencing but it was a few years ago and I'm not certain about how I got around it.

Read other 1 answers

i want to be able to link tables in Word 2016 so that if I enter text in a particular cell in one table it automatically populates a specific cell in another? Is this possible or would I need to create thelink using Access or Excel? Not much experience with Access.

Read other answers

I am using Excel 2010

I want to limit a validation list based on the contents of Cell D4
There are 4 lists possible based on the contents of D4:

If cell is 1 = list (on datasheet tab) should be limited to range A42:A71 only

If cell is 2 = list (on datasheet tab) should be limited to range A72:A87 only

If cell is 3 = list (on datasheet tab) should be limited to range A88:A90 only

If cell is 4 = list (on datasheet tab) should be limited to range A92:A93 only

A:Excel 2010: Limit validation list based on another cell

Soooo... is this possible or no?

Read other 3 answers

I was wondering if it's possible configure a Windows 2019 IIS v10 hosted Web Server to perform OCSP checking of client certificates that are used to authenticate?

It is my understanding that typically the Responder URL that the Web Server contacts in order to validate the client cert is extracted from the AIA attribute in the client certificate. But is it possible to override/supplement this with an additional Responder?

For instance, what if I set up an OCSP Responder in the same domain as the Web Server and associated its revocation configuration with the SUB CA binded to the IIS Site. Now if client certs come in for authentication  and have an unrelated OCSP Responder
in their AIA, can I somehow tell the Web Server to check also the aforementioned Responder that has been stood up in the domain?

A:OCSP Based Validation for Client Certificates Using Responder Defined by Web Server

Per a reply from Mark B. Cooper at PKI Solutions this is indeed possible. You must edit the following GPO in order to override the default behaviour of the web server which is to only check the Responder URL specified in the client certificates' AIA extension.
Default Domain Policy > Computer Configuration >  Policies > Windows Settings > Security Settings > expand Public Key Policies
Once a custom Responder is specified in the CA / SUB CA's revocation properties the above GPO will allow it to check that custom Responder URL first, then ocsp as defined in the AIA extension and then CRLs
Thanks Mark!
Will post reference links once MS verifies my account.

Read other 1 answers

I am trying to create a database, to keep track for stores orders for the ship i am on.
I want to auto generate the stores order number (Order Number) in the format
The 0730 is fixed as is the TAK. The yy is 2 digit year and the $$$ is the sequential number.
The yy field i want to take the year from the "Order Date" field in the same form.

IS this possible?
I have Access 2007 but the final database may have to run on 2003.
The database is still in early stages so is still small can be forwarded if it helps

Many Thanks

A:Sequential numbering in access based on another field

Read other 11 answers


I hope this is an "easy" answer.

We use an Access database to track class registrations for recreation programs.

Each program has a limit on class size.
How can I set a limit in Access so that when that number is reached, it displays a message....

I'd like to be able to continue to add records if it's decided that the class size can be adjusted, I really just want the message to appear.

I have a course table which has the course name, course code, start date, end date, and cost

I have a registration table in which all the registrants info is entered and the course is selected from the course table.

Thanks for any help with this.



A:Access 2K-Limit records based on a field

I would do a record count and see if it is equal to or less than the class limit. It is was, then I would do a messagebox or pop up window to give a message. you could to the message box with a yes or no. yes would add it, no would do nothing. the easiest is the message box. just look up msgbox in access help or give some more information on what you are doing.

Read other 2 answers

I have a field that contains say "Mel Gibson", what I need to do is pull Mel out and pull Gibson out and put into two new fields. I cannot for the life of me remember how to do this as it has been at least seven years since did this. Any help would be most appreciated. Thank you!

A:Two words in a field - extract based on space

Read other 8 answers

I have a form that populates fields from lookup box. There are multiple records for one member and not all records for that members has data from table.

memb1 has three records in table
first record has membname, category=2YO, Type is null
second record has membname, category=Pren, Type is AB

I need to show the type on the form but when selecting the memb1 but if type is null and type is AB - null shows but I need to see type AB.

I have an unbound textbox on form and populate with cboSearch.column(1)
This is in the unbound textbox: =forms!frmB!cboSearch.Column(1)

any suggestions on how I can show type ?


Read other answers

Dear Guys,

I am trying to get MS Access to print labels with names on them times the number in another field (i.e., Joe Smith is the name in the "name" field and a field called "Tags" has the number of car tags he is alotted, let's say 4. I need the database to print his name on 4 labels.)
I don't write code! Is there any way to do this, even in code.
Please e-mail a response to [email protected]

Thank you.



A:Access: Print Label Quantity Based on Value in a Field

Make a query in which you join the table with the person's name to the table with the tags. I assume there's a common field between the two, or how could you count? Join the tables by drawing a line between the fields with your mouse in the query design window.

Add the fields you want to show up on your labels. Base the report on that query. Access will print one label for each instance of the name in the tags table, i.e. 4 labels for 4 tags.

Hope this helps.

Read other 1 answers

I have a form designed to track processing times for various activities. This information is entered from a Production Log that the employee completes throughout the day. The main form has the following fields: Name, ArrivalTime, DepartTime, ProductionDate. After the user enters data into these fields, the cursor moves to the subform where the activity id is entered, the start and end times for that activity, the beginning & ending sequence number, and several other fields.

Since some activities don't require entry into all fields, I would like to disable certain fields based on the value entered into the activity id field. I have 4 activity ID's that I want to trigger the disable event on 3 other fields in the subform. I need to know how to code this, where to code it, and then to check in the form's BeforeUpdate event that the above is correct.

Thank you for your help!

A:Solved: Disable Fields on Subform Based on Value of Another Field

Read other 10 answers

This seems like it would be a common task in Access but I can't find any reference to it.

I have a combo box with three choices, Kamazaoo, Grand Rapids, and Traverse City. The field name is location. The location is entered only once but there is no limit to the amount of records that can be added. When one record is finished Access defaults to the previous location and starts a new record.

I know how to do a make table query but can I do a make table query based on the fields value? For instance if the entry person is finished entering data for Kalamazoo a button could be pushed that would make a Kalamazoo table.

Building the Ultimate site list for PC support.

A:Make table based on form field in Access

Read other 16 answers

Here's a sample

Record 1 - John Smith #23 - want 1
Record 2 -John Smith #29 - want 2
Record 3 -John Smith #27 - want 3
Record 4 -Fred Jones #24 - want 1
Record 5 -Fred Jones #25 - want 2

So what I want is for each row of data to place a count #. So for record 1 it would have 1, record 2 would be 2, records 3 would be 3, record 4 would be 1 and record 5 would be 2.

I need a count of records for each name. I do not want a subtotal. I need it to actually list out 1,2,3, 1,2.

I can do this in Crystal reports - but I really need this is Access.

Read other answers

Hi all,

I am looking for help on VB script to send automated emails based on the date column in excel sheet..

Uploading the excel sheet as well. The email should trigger on dates in column F to corresponding email addressed in column E ..

The email body should say :

Your employee "Column C" is approaching his probation period on "Col D". Kindly confirm if you want to confirm on the date of "Column D" or modify it.

A:Need help to send automated email based on the date field

Have a look at the attached I have written the code necessary however if you have any changes you would like made let me know.

Read other 1 answers

How does one create a mail merge document that will print only certain paragraphs or sentences based on data in each record? For example, a mail merge document where you wanted the first paragraph to be one thing if [fieldx] is "yes" and something else entirely if [fieldx] is "no"?

A:Word: Mailmerge Letter Contents Based on Field Value

Check here, Don:


Read other 3 answers

Here's a sample

Record 1 - John Smith #23 - want 1
Record 2 -John Smith #29 - want 2
Record 3 -John Smith #27 - want 3
Record 4 -Fred Jones #24 - want 1
Record 5 -Fred Jones #25 - want 2

So what I want is for each row of data to place a count #. So for record 1 it would have 1, record 2 would be 2, records 3 would be 3, record 4 would be 1 and record 5 would be 2.

I need a count of records for each name. I do not want a subtotal. I need it to actually list out 1,2,3, 1,2.

I can do this in Crystal reports - but I really need this is Access.

Read other answers

I am an absolute begineer in MS Access. Will try to put my question as simply n clearly as possible.

I have designed a database to keep history of our company's projects. The 'Master Project History Table' contains fields including 'Project Ref', 'Project Title', 'Project Budget' and 'Project Year'. The users are expected to populate the table through 'Master Project HistoryForm'.

The key field is set as 'Project Ref'.

Some of the projects are combined with each other and to display this info, 'Master Project History Form' also has the fields 'Project Ref-1', 'Project Title-1', 'Project Budget-1' and 'Project Year-1'.

I am loking for a code for the ('On Update' event) that will fetch and display data in 'Project Title-1', 'Project Budget-1' and 'Project Year-1' fields when 'Project Ref-1' is updated. Please note that all this info is coming from the same 'Master Project History Table'.

My IT guys told me to use a code with variable for each field that goes something like this (to update Project Title-1 field):

Dim varX As Variant

varX = DLookup("[Project Title]" , "Master Project History Table" , "[Project Ref]=" & Forms![Master Project History Form]![Project Ref-1])

Me![Project Title-1] = varX

And then repeat for each other field by defining new vari... Read more

A:MS Access: Updating Data In One Field Should Update Data In All Relevant Fields

Read other 14 answers

Basically I have a situation where users are creating data tables in Microsoft Word using the creating table functionality, and are then resquesting that the data will be imported into a single Microsoft Database field.

Please could someone show me the best way of doing this!!!!!!

Cheers !!!

A:Importing Table Data Into Multiple Access Field Data?

Do you mean they're creating, say, a 3-row by 3-column table and then they want to import everything in that one table into a single field? a single record?

Out of curiosity, what happens if you copy and paste, perhaps with an intermediate step through Excel?

Also, is there a reason why your users cannot work directly in Access?

More details, please.

Read other 3 answers

Hiya gang,
I need to know where I went wrong and for someone to point me in the right direction for a plausible solution.
Here is my task at hand:
Our database crashed and we lost data. I don't want to loose the data we have already inputted since the crash; however, there is missing data.
I loaded up the last best known back up of our database to recover the lost data.
So far so good.
I have done the following below:
Exported field from one db1_be.mdb file
Imported field to new db1_be.mdb file
Run Query to compare data and fill any missing fields with corresponding data

In essence, I am attempting to replace data from a compare data field query in access 2007.

Listed below are the web links I used to create the process above:
Export a database object to another Access database

Compare two tables and find records w/o matches

Update data from one table to another

Here is my code:
When I run the update query, I receive the error below:
Mircrosoft Office Access didn't update 0 field(s) due to type conversion failure, 0 record(s)... Read more

A:Solved: Update Data From a Compare Data Field Query

Read other 6 answers

I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance

Col | A | B | C | D | E
Row | 1 | Test1 | | | 10
| 2 | Test2 | =A1 | =MAX(E2) }| 15
| 3 | Test3 | =A2 | =MAX(E3) | 20
| 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30

A:Excel macro or formula - change field based on other cell

Read other 7 answers

I have a table that has service information which is categorised as ServiceType, ServiceDescription, ServiceCode, etc. The are up to 200 unique ServiceDescription/ServiceCode enrties per ServiceType.

I am trying to create table for orders that will only allow ServiceType from a listbox. I then need service description to be listed as a listbox based on the selection made from service type. I then need the ServiceCode to show based on previous 2 selections.


ServiceType ServiceDescription ServiceCode
Cleaning Clean Dwelling 001
Skips Empty Skip 002
Skips Replace Skip 003
Skips Remove Skip 004
Scaffold Erect New 005

So if I select Scaffold from drop down, it only shows Erect New in drop down for description and once that is selected, 005 is autofilled for Code

A:Solved: Access - Lookup Based On Previous Field Result?

This is fairly easy to achieve. The easiest is with the queries supplying your 2nd & 3rd Combos.
Asuming that your ServiceDescription Table contains the ServiceType ID
and the ServiceCode Table contains the ServiceDescription ID.
Create a query for your ServiceDescription including the ServiceType ID.
Create a query for your ServiceCode including the ServiceDescription ID.
In the first combo Afterupdate event procedure requery the 2nd Combo.
In the 2nd combo Afterupdate event procedure requery the 3rd Combo.
The key to make it work is to enter in to the 2nd combo's query's Criteria row for the
ServiceType ID - forms![fromname]![combo().column(1)]
where the second column of the 1st combo is the ServiceType ID.
Repeat this for the 3rd combo and the ServiceDescription ID.

Read other 2 answers

Hey there, long time reader first time poster!

(If you know of a great advanced tutorial on Access, please let me know. I know how to set up the basics, now I'd like to know how the program really runs)

I'm currently working on a form that allows users to search for equipment. The category of the equipment is slectable through a combo box, and the searched text is entered in a text field. The results are displayed in a list box.

What've I've tried is using a 'Find' button to enter this sql query into the RowSource of the list box:

Private Sub Equipment_Find_Click()

Me.Equipment_List.RowSource = "SELECT Equipment.Type, Equipment.Manufacturer, Equipment.Model FROM Equipment WHERE (((Equipment." & strQuote & Me.Equipment_Search_Category.Column(0) & strQuote & ")=" & strQuote & Me.Equipment_Find_Text & strQuote & "))ORDER BY Equipment.Type DESC;"

End Sub

The result i get is a pop up window with a text field. If i re-enter the text in that feild the info pops up nicely. What am I doing wrong, and how do i get rid of that popup?

A:Access 2003: Search criteria based on combo box and text field

Read other 12 answers

Firstly, let me apologise, I'm a complete Access noob & am trying to teach myself as I've got no one to help me in person.

I'm trying to build a Time Sheet database that should record daily duties via an entry form:

This form updates an underlying table:

The date field of the "Work_Hours" table is:

However, workers get paid fortnightly and all their entitlements, allowances & overtime are calculated on these fortnightly periods. To be more specific, the workers are paid for a certain amount of overtime for each fortnight upfront & when they exceed that, then they are entitled to all excess time back in TIME OFF. So it's pretty important that each day's duties are assigned to the correct fortnight period so that their overtime & so-forth are calculated correctly.

Each fortnightly period is numbered according to year. For example, each year there are 26 fortnightly periods, so as an example today (3rd October 2013) falls in period 2013-19 (23rd September 2013 to 6th October 2013).

I've created a table called:

Which has the following Fields:
"ID" (Auto-number)
"Period_Start" (date - the first day of each new fortnight period)
"Period_Number" (number - eg: 2013-19)

Back in the "Work_Hours" table, I inserted a new (Number) FIELD called:

and set ... Read more

A:Solved: MS Access - How to get a field auto-update based on date entered in a form

Read other 9 answers

Hi there!
On sheet B, cell C7, I have a dropdown list (using data validation) of names. The things I want to do with vb codes are as under:-
Let suppose, I select the first name from drop and took the print of page1. Now I want to automatically
Select the next name (i.e Mike) from dropdown list and take the print of page1. Then
Select the next name (i.e Steve) from dropdown list and take the print of page1. Then
Select the next name (i.e Jhon) from dropdown list and take the print of page1.
And so on till last name (i.e Blair).

Please note that I have some blank fields in my dropdown list. I just want to ignore them, mean I did not want to take the print for those blank fields. (sample file is attached)
Any help would be highly appreciated.

A:Data Validation with VBA

Read other 7 answers


I never quite got to the bottom of my last query because I changed jobs. Now, new job, new project! Can anyone help please?

I have created a DB with information about clients recorded in seperate tables eg: personal details; health info; financial info. The info is entered neatly from a main form.

The main form deposits info into the personal details table. From the main form, I can open other forms (all linked by ClientID) to deposit info into their corresponding tables. I have split the info up like this because I don't need to see all of it all the time, and also, not all fields will be relevant to all clients (eg not all clients will have relevant financial info).

Now the thing is to import all the information from one spreadsheet or .csv file and have it go to the relevant table. In my original DB, where I had everything in one table, I used the Get External Data option from the File menu to import .csv files from a Word Form that I created. But I can't see this method working where I want info deposited into seperate tables.

So, can I get the info contained in one file into the seperate tables? Where should I start? I can import to one table, then create queries to extract the info I need for each other table. However, I dont know how to then get the info from each query to the corresponding tables. Is this the right way to go anyway?

Thanks in advance for any suggestions.


A:Data into seperate tables

Read other 16 answers

In MS Access how do I have one field's data trigger another data?
I've tried this but no result:

Private Sub rfi_closed_BeforeUpdate()
If rfi_dt_to_contr = "NULL" Then
rfi_closed = "NO"
rfi_closed = "YES"
End If
End Sub

If "rfi_dt_to_contr" is empty I want the field "rfi_closed" to be "NO" and if there is data [i.e. a date] it is to be "YES".

A:the data of one field trigers the data of another

Hmm, it's been a while but as I recall NULL is not the same as EMPTY.


Read other 2 answers

Hi everyone,

I need help in excel, your comments will be highly appreciated.

I have 2 sheets in a workbook, one named: _Inventory_ & the other one _Quotation_

I wrote the inventory in Inventory sheet and then I went to Insert/ Name/ Define and created Inv.Quinc. which refers to =Inventory!$A$1:$A$2000

On sheet Quotation, cell H20, I went to Data/ Validation/ Allow: List/ Source:= Inv.Quinc. This is to create a list that I can choose from in H20
It works, but every time I open the list I have to scroll up, because it always start from the last empty field.

I have attached the file for your reference
Thank you in advance

A:Data Validation in Excel

Read other 13 answers


I have a couple of questions if you could possibly help me.

I have a cell where I want an employee to input the approximate (or actual) age of a person. To make this easier, I have created the following list - baby, child, 16-20, 20's 30's, 40+, Unknown. The problem is that sometimes the employee will know the exact age of the person - and I could do with capturing this information. Is there any way, other than elongating the list (adding on 0,1,2,3,4 etc), that the cell can be validated in such a way that you either input an actual age (say 00-100) or choose from the drop down list above?

The second question is -

I have 2 questions on an excel spreadsheet: -
1 - What is the number of clowns in the circus? Answer to be put in cell B3 (minimum 0, maximum 9)
2 - How many clowns had red noses? Answer to be placed in cell B4 (minimum 0, maximum 9).

Currently, both cells are validated to produce a drop down menu of 10 options - 0-9. What I want is to ensure the inability to place a higher number in cell B4 than B3. But I want to keep the drop down menu's (using list on validation). Can this be done?

Any help would be greatly appreciated.


A:Cell - Data Validation

It might help us to help you if you told us the exact program you are using.

Read other 3 answers