Update A Lookup Field
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 Replies
ADVERTISEMENT
Sep 1, 2012
Is it possible to run an update query to increment the value in a lookup field on all records, if the values are not numerical?
For example if the look up field contains Unit A, Unit B etc? can a query be executed so that anything with Unit A will be set to Unit B, and Unit B records will be set to Unit C?
View 5 Replies
View Related
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
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 1 Replies
View Related
Feb 24, 2007
i want to update data fields (time fields) in a table based on a number input by user (so it will search for record in table using the number and then update the relevant fields)....How can i do this am quiete confused there PLEASE DO HELP
View 1 Replies
View Related
Jun 15, 2007
Hi, I have a very simple database with 2 tables:
table: animals
1. Cat NA
2. Dog NA
3. Rat NA
4. Bat NA
5. Garfield NA
table: felines
1. Cat
2. Garfield
3. Simba
what I want to do is:
UPDATE animals.field3 (which starts off as NA's) to "meow" if animals.field2 matches any item in felines.field2.
it is a recursive operation and I'm not sure how to do it
here is what the animals table should look like if the UPDATE query runs as it should
1. Cat Meow
2. Dog NA
3. Rat NA
4. Bat NA
5. Garfield Meow
Any help would be great! I prefer SQL view of the UPDATE query if anyone knows how to do this. Note: I like cats and dogs equally, this is just to learn how to do such an update.
Sasi
View 2 Replies
View Related
May 30, 2005
Hi, not sure if this is possible or the most economical approach but here goes:
I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.
I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.
Can this be done?!
Thanks in advance
View 2 Replies
View Related
Apr 7, 2014
I have several lookup tables in an Access database. These tables are used to populate fields in the main table and act as filters for viewing record subsets.I want to create a form that does the following:
1.) List the lookup table via a combobox.I was able to accomplish this with the following code:
Code:
SELECT MSysObjects.Name AS [Table Name]
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "tblJob*") AND ((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;
2.) When a table is selected from the combobox, display the table in a subform for updating.
View 7 Replies
View Related
Oct 28, 2014
I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.
View 1 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 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 3 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
