Field Lookup On Forms
Apr 17, 2005
I am a new Access (2003) user.
I am using one table (Table A) as a data source for a form, using the form to update and edit records. However, I wish to include a field on the form that uses an ID field in Table A to perform a lookup on Table B.
To explain further, I have a staff ID in Table A, and I want to look up and display, but not edit, the corresponding surname from Table B (just for display and convenience - we all recognise surnames rather than ID's).
Many thanks in advance
View Replies
ADVERTISEMENT
Nov 23, 2012
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
View 5 Replies
View Related
Jun 6, 2013
I am using a combobox to lookup contact names.
The rowsource is taken from a field in a contact table that contains the contact's First name and then Last name.
You could use the dropdown to pick the name or type the First name into the combobox.
The problem is that the combobox matches only the beginning part of the field as you type. So if you start typing the person's last name, the combobox would not display anything.
Is there an easy way to allow the combobox to lookup any part of the field that matches the text being typed?
View 13 Replies
View Related
Jan 2, 2014
We have a form, which has a field linked to a "Recipients" table through a lookup. The field is bound to the recipient's name, but the lookup displays additional columns for user to identify the correct recipient in the case of duplicate names.
The "Payment" table's Recipient field only displays the recipient's name. So where duplicates occur, it's impossible to know which recipient is the correct one associated to a payment.
I would like to add an additional field to the "Payment" table called "RecipientIDNo". I need a way to auto-populate this field, based on the already existing Recipient field. Is this possible?
Why I just don't modify the recipient field on our payment form to be bound to the user ID number, there are two reasons.
(1) The field is linked to too many other reports and queries at this point. I believe it could cause an enormous amount of work to modify.
(2) the form likes to display whatever it is bound to. There may be a workaround for this, but I'm not aware of it and don't want the form displaying a number instead of a recipient name.
View 7 Replies
View Related
Mar 5, 2014
My table fields are as follows:
1. Issued? (This is a lookup field with the following lists "Yes"; "No"; "NA")
2. IssueDate
I created a subform bound to the table.I wish to accomplish the following on the form:
1. If "No" is selected on the [Issued?] field, I want the [IssueDate] to gray out.
2. If "Yes" is selected, I want [IssueDate] to be required.
I set Conditional Formatting as follows:
Expression is ([Issued?]="No"), condition is disabled.
When I tested it, [IssueDate] grayed out when "No" was selected. However, when I select "Yes", it still remained grayed out.
View 5 Replies
View Related
Jun 21, 2015
I'm creating a database which contains information for keeping dog grooming clients. It has 3 tables, table 1 is customer, table 2 is dog, and table 3 is booking.I want to be able to choose the customer table and within that you can see the pet linked to that customer and within the dog table you can see any bookings with that dog.What I have done is linked the pet and booking table to the customer details via mobile no. being the primary key.
I want a form called appointments which contains fields *ID, date, mobile, dog name, grooming stlye and other. What i want to happen is this, once the mobile number is entered, i then when it to lookup the mobile number in the dog table and pull the pet name from that, then in the dog name box it will then provide a drop down of dogs related to that mobile number.
I understand a query needs to be made in the dog name field, so in that field i created a query to look up the mobile no. in the appointment table, and to then lookup the pet name in the pets table. This kind of works. The two problems are this, the drop down in the dog name field shows all of the dogs in the database not just the ones assigned to the mobile no. in the field before. If i choose one of the dogs not related to that mobile it changes the mobile number in the field before to the relevant one (but i don't want to be able to see the dogs which arent related to the mobile no. in the field before.The other issue is that on one of the customers there are two dogs, now on the drop down you can see both dogs, but it will only allow you to select the dog that was entered first into the database. If i select the other dog it simply chooses the data for the one first entered.
I have read about doing Requeries on the afterupdate of the field and attempted this by choosing the dog name field and in the afterupdate telling it to requery pet name (I've also tried mobile), this hasn't worked. I have a feeling I'm getting a bit confused by which fields i'm supposed to use etc.
View 1 Replies
View Related
Aug 7, 2013
I want to use VBA to hide columns in a subform based on what is checked in a multi value look up field.
I am creating this DB for use with sharepoint as a web database, which is why I am using the lookup field to begin with. There will be a client DB to use with some VBA code
So what I have is a lookup field with tests "Test 1, Test 2, etc" on form sample.
There is a subform called results, and I want to hide certain columns based on what tests are performed.
I tried using an If Then statement (code is being run on subform load)
If Me.Parent.fieldTest = "Test 1" Then
Me.Test1Col.ColumnHidden = False
Else Me.Test1Col.ColumnHidden = True
That is basically the code I was trying to use. I am getting an error 13, which I assume is because fieldTest can not = something since it is a multi value look up field.
View 3 Replies
View Related
Jul 6, 2014
I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.
Something like:
Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function
View 5 Replies
View Related
Dec 30, 2012
Is it possible to look up 2 field to auto fill another field?
Field 1 is "Exposure" this autofill with "Probability" is user input.
I need to lookup Exposure and Probability and autofill a score in to "Risk Rating". This to stop incorrect data being inputted.
I have a table with all the results combination in it.
Would it be possible to use a Dlookup to look at Exposure and Probability to give me the score
I tried a Combox with autofill. But the power to be would like it done without user input.
View 11 Replies
View Related
Jan 11, 2008
Hi,
my only other issue!!! i have a lookup field on a table and when i view it in datasheet, i can see the actual lookup value i need however i have tried the following options:
create a form from the table
added a combo box to an existing form and gone through the wizard
both options show the id number and NOT the value i need to show. what can i do to show my actual information?
many thanks,
NS
View 8 Replies
View Related
May 3, 2005
Let's say that I have two tables:
Table Numbers:
ID VALUE
-- ------
1 123
2 456
3 789
Table Letters
ID VALUE NUM_LOOKUP
-- ------ -------------
1 ABC 1
2 DEF 3
Now, I'd like to use the lookup feature so that NUM_LOOKUP is a combo that displays the data in VALUE based on the relationship between ID in the two tables.
So, when you open the Letters table, you see the above. If you click in the NUM_LOOKUP field, you get a box that displays "123" for the first record and "789" for the second record.
I'm very close -- Here's what I have:
I tried the following:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [ID],[VALUE] FROM [Numbers] AS [NUM_LOOKUP];
Bound Column: 1
Column Count: 2
Problem: This selects ALL values of ID from [Numbers].
I tried adding the following to my row source:
WHERE [Letters].[NUM_LOOKUP]=[Numbers].[ID]
This just gives me the "enter parameter" dialog box for [NUM_LOOKUP] and [ID].
Any help?
Thanks!
View 2 Replies
View Related
Jul 31, 2006
Alright, I have a field called metrics that looks up the text in another table caled Metric Name. I want to delete the table that is being used to supply the values, but I still want a listbox with all those names. Also I want to be able to add names to that listbox. I am not sure how to do this.
View 3 Replies
View Related
Dec 11, 2006
Hi all,
It is along since i have used access for anything and I am having problmes getting it to do what i want it to do.
I have 2 tables one which hold the data needed and the other is being used to hold a list of products with prices.
In my main talbe in which the data will be entered into I have a drop down so the product can be selected, once it is selcted i want the next field filled in automaticaly with the price. The look up looks at my products table which hold the price too. I tried with setting the look up based on a query but it get errors about using the same table
Anyone help me???
Thankyou for looking
View 11 Replies
View Related
Sep 25, 2006
I would like to create a lookup for a field - I want to display 2 columns, but store only one in the table.
Id City
1 Sheboygan
2 Sheboygan Falls
3 Plymouth
Show both fields in the lookup, but only store the Id in the table.
I can't seem to get the second column to display.
View 5 Replies
View Related
Mar 13, 2007
I have two columns in the look up field. When I select the lookup value in the form I need value from one column to be displayed in one box and the value from the second column displayed in another box. Is there a simple way to do it?
Thanks
View 3 Replies
View Related
Apr 30, 2013
I am working on a database where i have to show the user last modification date of the files they are getting the data from. I have already completed that process. Used GetDatetime to find the files last modification date and time. And now i am moving on to the next step and Here is the scenario.
User will click the IMPORT button and first, it will check the last modified date in the form of those files(which is an unbound textbox) and compare that date with the Date Column that exist in the tbl_Import. If the Date matches it will show them a message Saying "Data cannot be imported since it already exist. If it doesnt match it will run the macro i have created to run the data import process
View 1 Replies
View Related
Mar 9, 2007
Hi gurus,
I have two fields in the main record table with values lookup from two other tables. These two table has 1-many mapping (like country - States). Can I make change within the record table. When I change the value in country field for a record, the States field will only show the states of that country?
Now I have lookup query for these fields from country and states seperately, so no matter what I change in the country field, the states field show all states for all countries.
Thanks for your advice!
View 4 Replies
View Related
Mar 15, 2006
Hi, I'm pretty new to access but almost have a database working the way I want it to. I just need help with one thing. I am using PPC Creations on a PDA to fill out a service docket. All the fields export to the DB ok, the problem I have is displaying the info on a report. I have one field to display one of 8 manufacturers and then 8 fields (one for each manufacturer) that display the model. A bit like this...
IntruderPanelType (results are code eg. 1=ADE 2=ADEMCO)
ADE (results are text eg. Optima)
ADMECO
DABOSCH
GARDTEC
MENVIER
SCANTRONIC
TEXECOM
OTHER
OtherIntruderPanelType
So, on my report I want a field (maybe needs to be new) that will display the contents of the relevant manufacturer field, depening on what is in the "IntruderPanelType" field. On the report if "IntruderPanelType" is "1" and "ADE" is "Optima", I want it to display.......
Control Panel Type: ADE Optima
I'm sorry if I'm not making myself clear but if you can understand my ramblings I'd really appreciate a simple solution to this. I've attached a copy of my database so far, in case it helps any! TIA for any help you can give.
Regards,
Brian
View 3 Replies
View Related
Mar 18, 2005
Hi Everyone,
I have a question about lookup fields. I have the tables below. The main table is [vid] and i reference other tables like [manufacturer], [model].. etc. to fill in [vid]. I have pull down lookups for all of [vid] but it is showing all models, makes, etc. I need helping making the lookup row dependant. For example when I am in [vid] and click on the [model] combo box for vid=1, I only want to see the models for that manufacturer(Ford), so it will only show mustang, escape, and navigator; not all of the models. For vid=2, when I click the pull down for model I only want to see Gm Models, corvette and cavalier. So this is what i mean be row dependant. Is this possible?
Thanks so much in advanced,
Michael
[vid]
vid,year,mft, make, model, submodel, ...
1,2005,ford,ford,Mustang,GT
2,2005,GM,Chevrolet,Cavalier, LS
3,2005,dcx,Jeep,Wrangler,base
...
30,2005,ford,Lincoln,Navigator, base
[manufacturer]
mid,mftName
1,ford
2,gm
3,dcx
[Model]
modelID,modelname,modelmft
1, Mustang,Ford
2, Escape, Ford
3, Corvette, GM
4, Cavalier, GM
5, Navigator, Ford
6, Wrangler, DCX
View 2 Replies
View Related
Jan 6, 2006
I am very new to Access and any help would be appreciated. I have been through the entire Access Bible and many forums but can't find the answer to my specific question.
The project:
I'm setting up a database for tutors and students. Tutors will, via the web, enter data about a specific tutoring session (studentName, subject, sessionLength, etc.) Then at the end of the month a report will be generated detailing hours tutored and total compensation.
The problem:
Students pay varying rates based on subject (math or verbal). So in the Session table each record for each tutoring session has an empty field called payRate. This is the rate the student will pay based on subject. So I need this field to "fill in" automatically based on the 'student' and the 'subject' from the same record. Then I can multiply the field by sessionLength and get a totalPay field for the session. Add these at month's end and pay the tutors.
I've set up a lookup table called 'subject' with three fields (student,subject, payRate) and I have the student and subject combined as the primary key. Am I on the right track here? If so, how do I link the tables to get that Session 'payRate' field to fill in automatically. This is where is breaks down for me. I've tried the lookup wizard but all I seem to get when I open the Session table is an empty PayRate field with a drop down box containing the entire subject lookup table.
Sorry for being so long winded but I need help from someone better at his than me. Thanks in advance.
View 2 Replies
View Related
Apr 20, 2006
I have a table (tblStudent) containing a field named fldTeacher1. Looking at the properties of fldTeacher1 I see that it has a lookup:
Bound Column: 1
Column Count: 2
Column Widths: 0";1" (id number; teacher's Name)
I am trying to run a DoCmd.RunSQL command in code and My question is how do I write a sql statement in code that updates that field with a new value?
I have tried:
DoCmd.RunSQL ("UPDATE tblStudent SET fldTeacher1 = " & Val(txtID) & " WHERE fldID = " & gintStudentID & ";")
All it does is place the id # in the field and not display the teacher's name in fldTeacher1 after the command was run.
View 2 Replies
View Related
Feb 15, 2006
Ok I'll premiss this by saying it has been many years since I have done much with access so I have forgotten alot and alot of things have changed. However I have been using Dataease alot but switched companies so am back to access.In dataease if I had a form called projects and that form had three fields (employee ID, employee first name, employee last initial) that pulled data from another table other than the projects table I would relate them via the employee ID. projects form employee ID = employee form employee IDthen for the two other fields I would have them lookup their data by using the employee id.projects form employee first name = if (projects employee ID = employee employee ID) then lookup employee employee first name. And the same for the last initial.The question is how do I setup for fields for first name and last initial to do the lookup in access.Employee ID TableEmployee ID #Employee First NameEmployee Last InitialProject TableEmployee ID (linked to employee id table)project idproject nameproject discriptionproject start dateproject end dateproject formproject idproject nameproject discriptionproject start dateproject end dateemployee ID (must match employee table employee id)employee first name (read only field that looks its data up from the employee table based on employee id)employee last initial (read only field that looks its data up from the employee table based on employee id)
View 1 Replies
View Related
Apr 14, 2006
Hi Guys.
I have a look-up field in a table that looks-up a range of values from another table. How do I set a default value for the field? I have tried entering in numbers as default values (IE the ID numbers for the records that are being looked up) but that doesnt work.
View 1 Replies
View Related
Aug 14, 2006
I have a lookup field in my table - when I try to create my report, it lists all of the data in the lookup list - I just want to display the one that is stored in the table.
How do I do this?
View 3 Replies
View Related
Jan 12, 2008
hi, plz i need a fast help
i have 3 tables in access:
Country Table: fields:country Id, country name
City Table: fields: City ID,Country Name(lookup field to Country Table), City Name
Street Table: fields: Street Id, country name(lookup field to Country Table), City Name(must be lookup to City Table where City.Country name = Street. Country name), street name
my question is: how can i make the lookup of the City Name in the Street Table, in a way that only cities of the specific country of the record active are displayed ???
example:
Country table:
1 Lebanon
2 KSA
3 Kuwait
City Table:
1 KSA jeddah
2 KSA Riyadh
3 Lebanon Beirut
4 Lebanon Tripoli
STreet Table:
1 KSA (???) Sary (the ?? must be a combo Box that show only the cities of KSA: jeddah, Riyadh..which are in the City Table)
2 KSA (???) sondos (same as above)
3 Lebanon (???) hamra (the ??? must be a combo box that show only the cities of lebanon: Beirut and tripoli...which are in the city Table)
i have created all the fields in these tables...and when i put the field City Name as lookup, i am failing to mak eit read correctly from the city table according the the country of my active record..
plz anybody knows how to do this query???
View 2 Replies
View Related
Feb 25, 2006
Haven't touched Access in quite some time and now I can do a simple task.
Here's what I have:
2 Tables
Table 1 includes 2 fields COURSE CODE and COURSE NAME
Table 2 will be a list of students and the courses they have taken
What I'm trying to do is as I'm entering a new student in the database I want the course name to automatically fill in when I type in the course name.
I did lookup fields for both the course code and the course name to ensure the correct codes are entered. However since the data in table 2 does not autofill there is the posibilty of having a row with a course name that does not match the course code.
How can I force table to 2 match the course name and code relationship from table 1?:confused:
View 2 Replies
View Related
