Update A Lookup Feild

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

Highlight A Feild

Dec 1, 2004

Is their a way to highlight a feild. I am using the duplicate function but after the record is duplicated their are certain feilds that have to be changed, how can i highlight them somehow. ANy ideas??? The feild is Price???

Thanks Very good forum


Pete

View 4 Replies View Related

Help On How To Create A Feild!

Mar 27, 2008

The situation I am in at the moment is that I have a database for a company who does work at different building sites. I have created a database so that it has the date the work was completed. With this company in particular the last possible dates of payment are 30 or 60 days after the work was completed, depending on the work. I would like to know how I would be able to create a calculated field that would display the first day that the payment is late.

View 1 Replies View Related

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 2 Replies View Related

Lookup And Update Data????HELP

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

Setting Feild Value To Table Value

Nov 28, 2004

I have a form and subform which have the total value of subscriptions made for the month on them.

The subform has the individual values, and the mainform has the total value for that month.

how can i then assign the value of a feild on the mainform, to a value in one of my tables?

whats the best way to go about it?

thanks
BB

View 4 Replies View Related

Allowing Duplicates In One Feild But Not 2

Apr 12, 2006

I have have 2 feilds in my database, there can be the same value in one feild but not both.....for example say NAME and ADDRESS i can have the same NAME for more then one entery and i can have the same ADDRESS for more then one entery but i cant have the same NAME And the same ADDRESS....anyone know how to do this? :confused:

View 2 Replies View Related

Duplicate Feild Issues

Jun 20, 2007

Im having a problem with controling duplicate records in a table

i want to allow duplicates in all feilds individually, but not if 3 feilds are the same in more than one record. (ei. if the entries in a1,b1,c1 are equal to entries in a2,b2,c2; prevent the second record from being entered)

so far, i've writen a macro to identify duplicates in an existing table, but i dont know how to delete records, or access values being entered in a form

any help would be greatly appretiated, thanks

View 2 Replies View Related

Recursive Lookup And Update In Access- HELP!

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

Changeing Feild In Table Annualy.

Oct 27, 2004

I have a feild in my table that I need to increase by 1 each year.

eg.

2004- needs to change to 2005- on 1/1/2005 and keep changeing each year after that.

I am sure this is simple but all my attempts at makeing a counter have failed thus far.

View 5 Replies View Related

Auto Complete Feild In Table

Apr 28, 2008

Hi,
I'm trying to create a table which needs some feilds to be automatically filled up when the control goes to it. Say my table has feilds, "size" and "result". In each row, the result cell should be filled up corresponding to the size value of that row. This needs to be looked up from an existing table.

Would appreciate any solutions!

View 1 Replies View Related

Update Query Based On Lookup Tables Values

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

Forms :: Dynamically Selecting A Lookup Table To Update

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

Database Update Of Multiple Fields Based In Lookup

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

Update Query To Increment Value In Lookup Field On All Records?

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

Adding Feild Values And Storing In Another Field In Same Table

Mar 19, 2005

I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!

View 2 Replies View Related

LookUp Tables For Dynamic Set Of Attributes: Set A Pointer Or Use A Generic LookUp?

Jul 24, 2007

I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.

However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.

I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.

But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.

Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.

For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?

Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?

So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?

View 4 Replies View Related

Filtering Lookup Value Based On Other Lookup Values

Jul 27, 2015

I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".

Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.

View 4 Replies View Related

Lookup Fields Using Lookup Wizard

Mar 2, 2005

Hello,

I wonder if anybody can help me.

I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,

I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.

I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.

Is this possible?

Regards
Nathan

View 1 Replies View Related

Tables :: Lookup Field Using Another Multi Value Lookup Field As Data Source

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

General :: Saving Data - Update Or Cancel Update Without AddNew Or Edit

Apr 13, 2013

My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.

The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.

Browsing the all of the existing records is fine.

View 9 Replies View Related

How To Use Lookup

Oct 26, 2005

I have an Invoice & Address file I want to know how I can for example enter customer number in the Invoice file and get the information form Address file in the Invoice file.

Thanks

View 3 Replies View Related

Lookup Help

Feb 19, 2008

Hi all
Im trying to create a database where one field has a lookup to another field in another table (easy enough done) the the next field in the first table has another lookup, however the items the user can select change dependent on what was selected in the first feild eg:
SelectA -A1
-A2
SelectB -B1
-B2
So the user can only select B1 in the second field if SelectB was picked in the first field

Does anyone know whow this is done

Thanks
Chris

View 1 Replies View Related

Lookup Value

Oct 26, 2006

I wonder if anyone can help.
I have 2 tables.
Table1 (lookup table)has 2 fields: 'rate' and a 'minimum amount'. The rate applies to a range of values eg up to 1000,rate=20, up to 5000, rate = 22 etc.
Table 2 has 3 fields: 'UniqueID', 'current_rate', 'amount'.
The 'amount' field in table 2 increases on a monthly basis and when this reaches the next amount threshold (in Table1), the 'current_rate' needs to be updated in table 2 to the appropriate value.
I have searched this forum and Dlookup seems looks to be the only way to do it. However, I think this only returns one result and some of the suggested solutions use a form to input the 'amount', when I have many entries that need to be updated.
Any suggestions would be appreciated.

View 4 Replies View Related

Lookup

Jun 21, 2007

i'm sure this must be pretty simple but when i search for lookup i keep finding stuff about translating names into numeric id's which is not what i mean

what i have is a table TBL_EnergySavings with these fields

IDFuel, IDProperty, IDMeasure, EnergySaving

its a lookup table so that given the three ID numbers (of a type of energy saving measure like loft insulation, installed in a certain type of property which uses a certain type of fuel) we can say how much energy is typically saved in GWhrs

I have another table of installed measures (jobs) which has property type, fuel type and measure type amongst its fields and what i need to do is to fill in a column in that table with the energy saved by each measure

so how do i look up the relavant record from TBL_EnergySavings?

View 9 Replies View Related

Lookup Help

Sep 9, 2005

Hi, im fairly new to access. im creating my 2nd access project for my 2nd year of A levels and i need a bit of help with lookups on a form im using

heres a screenshot of the form

http://beta9.picturehost.co.uk/cat.jpg

What i want is for when you select an option from the category combo box, for the sub category to have limited options. Example: i choose Drinks from the main category combo, the sub category combo will only have a list of types of drinks available.

This has probably been posted before, but to be honest i didnt know what to search for.

Tell me if you need any more information
thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved