Update Query - Operation Must Use An Updateable Query

Jan 8, 2007

Hi,

As an Access novice I am trying to do a simple update of a table but keep getting an annoying error:

I have 2 tables - Products and OrderDetails

Products
ProductID
Description
StockQuantity

OrderDetails
OrderID
ProductID
Quantity

Basically I want to update the Products.StockQuantity column to subtract the sum of all orders in the OrderDetails table.

So if Product A started with a StockQuantity of 5, and there are 3 Order_Details record, each with a quantity of 2, the query should update the Products.StockQuantity record to -1.

I have tried a few permutations such as:

UPDATE PRODUCTS
set PRODUCTS.StockQuantity = PRODUCTS.StockQuantity - (
SELECT SUM(OrderDetails.Quantity)
FROM ORDERDETAILS
WHERE ORDERDETAILS.ProductID = PRODUCTS.ProductID);

but I seem to keep getting the following error:
"Operation must use an updateable query"

Help - I'm tearing my hair out here!!

Thanks,

Keith

View Replies

Operation Must Use An Updateable Query?

Mar 7, 2006

Hello,

I was hoping someone on this forum might be able to help me.

I am trying to update a table with info in another table, using a nested query.

strSQL = "UPDATE tblTemp SET AdminNum = '" & cboAdmin.Text & "', " & _
"Size = (SELECT Size FROM tblEquipList WHERE AdminNum = '" & cboAdmin.Text & "')"

DoCmd.RunSQL strSQL

cboAdmin is a combo box on my form that is populated by tblEquipList.AdminNum.

When run this gives me:
Runtime Error '3073':
Operation must use an updateable query.

The SELECT statement works by itself, and the first part of the UPDATE (without the nested select) works by itself, but not when I combine them. I also need to update other things in the same manner in which Size is being updated, but when I can get this to work it shouldn't be a problem just added the updates for the other fields in there.

Thanks in advance,
Jeremiah

Edit:
Also, I figured I should add that when I remove the SELECT statment and put in a number (IE: Size = '400'), the query runs just fine. So I know the problem lies in the nesting, but am unsure how.

View 7 Replies View Related

Operation Must Use An Updateable Query.

Mar 21, 2006

I have tried to create a form page that when filled in by a user updates a database.

However i get his error messge

Code:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query./tpvsite/RegisterUser.asp, line 62

In my page it aligns to this line objRS.Update. Which i think means there is a problem with my database as anyone any ideas how to fix this. (all my tables and fields are correct)

Cheers

View 5 Replies View Related

Operation Must Be Updateable Query Error?

Mar 9, 2006

Can someone tell me why this query is causing this error? I don't understand it:

UPDATE 1_2_06 SET ROLE = (SELECT ROLE FROM UserRoleData WHERE USERNAME=ASSIGNED_TO)
WHERE EXISTS (SELECT ROLE FROM UserRoleData WHERE USERNAME=ASSIGNED_TO);

thank you

View 1 Replies View Related

Operation Must Use An Updateable Query - MS Access.

Nov 16, 2004

I am trying to update rows in an access database by retrieving data from dynamic text boxes created through a loop. I keep getting the error message: System.Data.OleDb.OleDbException: Operation must use an updateable query. My code is as follows:

Dim myInsertQuery AsString

myInsertQuery = "INSERT INTO tblTestQues (Answer) Values ("txt" & loop1);"

Dim myCommand2 AsNew OleDbCommand(myInsertQuery)

myCommand2.Connection = MyConnection

MyConnection.Open()

myCommand2.ExecuteNonQuery()

myCommand2.Connection.Close()

Please help or contact me if you need more information. Thanks in advanced.

View 3 Replies View Related

ERROR: Operation Must Use An Updateable Query.

Mar 25, 2007

asp code:
Code:<%Set Con = Server.CreateObject("ADODB.Connection") Con.Open "db"dim username, password, mailusername = Request.form("username")password = Request.form("password")mail = Request.form("mail")sql= "INSERT INTO users(username) values('" & username & "')"Con.Execute sqlCon.closeset Con = nothing%>

error:
Code:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80004005)[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query./site/content/register_act.asp, line 13

It's says it has something to do with permissions...

I set writing & reading permissions for the db and the directory that contains it through
contorl panel->Administrative Tools->Internet Information Services

I have no "security tab" when I rightclick->properties the db file or it's directory even though the file system is NTFS

in spite all these when I rightclick->properties the directory that contains the db I see a "read only" square marked in the general tab-attributes, when I try to unmark it seems to be unmarked but when I rightclick->properties again it's marked again...

what should I do?

View 1 Replies View Related

Query Not Updateable?!?

Sep 29, 2005

I have a form that is based on a query that has all of the sudden become non-updateable. After searching this site for a while - I found someone who had a similar problem, it was reccomended to check the query the form is based on to see if it could be updated. I did that with mine and the query COULD NOT be updated either.

To the best of my knowledge - nothing has changes with the tables the query is based off of or the query itself.

Any ideas what else to look for?

View 3 Replies View Related

Query Not Updateable

Oct 18, 2007

Having a problem that's driving me nuts

I am trying to ship some inventory from a sales order line (query 2qryInvShip works fine) but when I join the query with another (to find the oldest stock) Shipped Qty, Shipped Location & Shipped checkbox cannot be updated

Any thoughts as to where I am going wrong with this much appreciated, I have have tried seemingly endless combinations of join types etc but to no avail

db attached

View 7 Replies View Related

Predicate For Non-updateable Query...

May 17, 2005

Okay, it may seem like a silly question for one who's been using this as long as I but I was just curious if anyone had a concise answer as to the predicate for a non-updateable query?

Today I think I decided to stop using a workaround by creating a temporary table with duplicate information found elsewhere in the system.

~Chad

View 1 Replies View Related

Problem With Updateable Query

Jun 1, 2007

Hi everyone,

I have four tables (all linked by a unique identifier) which I populate via data entry forms. I would like to give the user an option of making changes to the data by entering the unique identifier and displaying the specific record (from all four tables).

Is there any way to write an updateable query to accomplish this task?

I started with a query of two tables and this was updateable.

I then added a third table and now it don't work.

Here's the SQL statement
-----------------------------------------------------------------
SELECT tblPerson.*, tblPermaddr.*, tblTempaddr.*
FROM (tblPerson INNER JOIN tblPermaddr ON tblPerson.FILENO = tblPermaddr.FILENO) INNER JOIN tblTempaddr ON tblPerson.FILENO = tblTempaddr.FILENO
WHERE (((tblPerson.FILENO)=[enter fileno]));
-----------------------------------------------------------------

Any suggestions??

Many thanks in advance.

Will

View 1 Replies View Related

Union Query Datasheet Not Updateable

Mar 19, 2008

Hello All,
I have a database with multiple tables, each table with a subset of common fields I need to work with. The way the database was set up was a series of append queries that created a common table to combine the subset of fields. The problem with this is that I had to work with the common table but update the data in the parent tables and regenerate common table to keep everything up to date. To get around this I have created a union query to replace the common table but the data is not updatable. I have had a quick scan of the net and it seems union queries are not updateable by default. I understand the problem I have is a result of the database not being structured correctly however due to circumstances beyond my control, I am stuck with it. I have come across a couple of INSTEAD OF trigger examples but not sure quite how they work and if they can help me. Below is the SQL code for my union query so any help in making it updatable would be greatly appreciated.

Regards

Daz

SELECT [Belt Filter Press].Table, [Belt Filter Press].Plant, [Belt Filter Press].[New Tag], [Belt Filter Press].[New Comment],

[Belt Filter Press].[P&ID number], [Belt Filter Press].Purpose, [Belt Filter Press].[Motor Size], [Belt Filter Press].Starting, [Belt

Filter Press].New, [Belt Filter Press].Comment, [Belt Filter Press].Switchboard, [Belt Filter Press].PLC, [Belt Filter

Press].[PLC Interface], [Belt Filter Press].ACH, [Belt Filter Press].IL, [Belt Filter Press].ZSO, [Belt Filter Press].WIO, [Belt

Filter Press].ZSP, [Belt Filter Press].PSL, [Belt Filter Press].FSL, [Belt Filter Press].TSH, [Belt Filter Press].MSX, [Belt Filter

Press].SSL
FROM [Belt Filter Press];

UNION SELECT Bins.Table, Bins.Plant, Bins.NewTag, Bins.[New Comment], Bins.[P&ID number], Bins.Purpose, Bins.[Motor

Size], Bins.Starting, Bins.New, Bins.Comment, Bins.Switchboard, Bins.PLC, Bins.[PLC Interface], Bins.ACH, Bins.IL,

Bins.ZSO, Bins.WIO, Bins.ZSP, Bins.PSL, Bins.FSL, Bins.TSH, Bins.MSX, Bins.SSL
FROM Bins;

UNION SELECT [Chemical Dosing].Table, [Chemical Dosing].Plant, [Chemical Dosing].[New Tag], [Chemical Dosing].[New

Comment], [Chemical Dosing].[P&ID number], [Chemical Dosing].Purpose, [Chemical Dosing].[Motor Size], [Chemical

Dosing].Starting, [Chemical Dosing].New, [Chemical Dosing].Comment, [Chemical Dosing].Switchboard, [Chemical

Dosing].PLC, [Chemical Dosing].[PLC Interface], [Chemical Dosing].ACH, [Chemical Dosing].IL, [Chemical Dosing].ZSO,

[Chemical Dosing].WIO, [Chemical Dosing].ZSP, [Chemical Dosing].PSL, [Chemical Dosing].FSL, [Chemical Dosing].TSH,

[Chemical Dosing].MSX, [Chemical Dosing].SSL
FROM [Chemical Dosing];

UNION SELECT Clarifiers.Table, Clarifiers.Plant, Clarifiers.[New Tag], Clarifiers.[New Comment], Clarifiers.[P&ID number],

Clarifiers.Purpose, Clarifiers.[Motor Size], Clarifiers.Starting, Clarifiers.New, Clarifiers.Comment, Clarifiers.Switchboard,

Clarifiers.PLC, Clarifiers.[PLC Interface], Clarifiers.ACH, Clarifiers.IL, Clarifiers.ZSO, Clarifiers.WIO, Clarifiers.ZSP,

Clarifiers.PSL, Clarifiers.FSL, Clarifiers.TSH, Clarifiers.MSX, Clarifiers.SSL
FROM Clarifiers;

UNION SELECT Filters.Table, Filters.Plant, Filters.[New Tag], Filters.[New Comment], Filters.[P&ID number], Filters.Purpose,

Filters.[Motor Size], Filters.Starting, Filters.New, Filters.Comment, Filters.Switchboard, Filters.PLC, Filters.[PLC Interface],

Filters.ACH, Filters.IL, Filters.ZSO, Filters.WIO, Filters.ZSP, Filters.PSL, Filters.FSL, Filters.TSH, Filters.MSX, Filters.SSL
FROM Filters;

UNION SELECT [Gravity Drainage Deck].Table, [Gravity Drainage Deck].Plant, [Gravity Drainage Deck].[New Tag], [Gravity

Drainage Deck].[New Comment], [Gravity Drainage Deck].[P&ID number], [Gravity Drainage Deck].Purpose, [Gravity Drainage

Deck].[Motor Size], [Gravity Drainage Deck].Starting, [Gravity Drainage Deck].New, [Gravity Drainage Deck].Comment,

[Gravity Drainage Deck].Switchboard, [Gravity Drainage Deck].PLC, [Gravity Drainage Deck].[PLC Interface], [Gravity Drainage

Deck].ACH, [Gravity Drainage Deck].IL, [Gravity Drainage Deck].ZSO, [Gravity Drainage Deck].WIO, [Gravity Drainage

Deck].ZSP, [Gravity Drainage Deck].PSL, [Gravity Drainage Deck].FSL, [Gravity Drainage Deck].TSH, [Gravity Drainage

Deck].MSX, [Gravity Drainage Deck].SSL
FROM [Gravity Drainage Deck];

UNION SELECT [Grit Classifiers].Table, [Grit Classifiers].Plant, [Grit Classifiers].[New Tag], [Grit Classifiers].[New Comment],

[Grit Classifiers].[P&ID number], [Grit Classifiers].Purpose, [Grit Classifiers].[Motor Size], [Grit Classifiers].Starting, [Grit

Classifiers].New, [Grit Classifiers].Comment, [Grit Classifiers].Switchboard, [Grit Classifiers].PLC, [Grit Classifiers].[PLC

Interface], [Grit Classifiers].ACH, [Grit Classifiers].IL, [Grit Classifiers].ZSO, [Grit Classifiers].WIO, [Grit Classifiers].ZSP, [Grit

Classifiers].PSL, [Grit Classifiers].FSL, [Grit Classifiers].TSH, [Grit Classifiers].MSX, [Grit Classifiers].SSL
FROM [Grit Classifiers];

UNION SELECT Instruments.Table, Instruments.Plant, Instruments.[New Tag], Instruments.[New Comment],

Instruments.[P&ID Number], Instruments.Purpose, Instruments.[Motor Size], Instruments.Starting, Instruments.New,

Instruments.Comment, Instruments.Switchboard, Instruments.PLC, Instruments.[PLC Interface], Instruments.ACH,

Instruments.IL, Instruments.ZSO, Instruments.WIO, Instruments.ZSP, Instruments.PSL, Instruments.FSL, Instruments.TSH,

Instruments.MSX, Instruments.SSL
FROM Instruments;

UNION SELECT Mixer.Table, Mixer.Plant, Mixer.[New Tag], Mixer.[New Comment], Mixer.[P&ID number], Mixer.Purpose,

Mixer.[Motor Size], Mixer.Starting, Mixer.New, Mixer.Comment, Mixer.Switchboard, Mixer.PLC, Mixer.[PLC Interface],

Mixer.ACH, Mixer.IL, Mixer.ZSO, Mixer.WIO, Mixer.ZSP, Mixer.PSL, Mixer.FSL, Mixer.TSH, Mixer.MSX, Mixer.SSL
FROM Mixer;

UNION SELECT [Odour Control].Table, [Odour Control].Plant, [Odour Control].[New Tag], [Odour Control].[New Comment],

[Odour Control].[P&ID number], [Odour Control].Purpose, [Odour Control].[Motor Size], [Odour Control].Starting, [Odour

Control].New, [Odour Control].Comment, [Odour Control].Switchboard, [Odour Control].PLC, [Odour Control].[PLC Interface],

[Odour Control].ACH, [Odour Control].IL, [Odour Control].ZSO, [Odour Control].WIO, [Odour Control].ZSP, [Odour

Control].PSL, [Odour Control].FSL, [Odour Control].TSH, [Odour Control].MSX, [Odour Control].SSL
FROM [Odour Control];

UNION SELECT [Pump List].Table, [Pump List].Plant, [Pump List].[New Tag], [Pump List].[New Comment], [Pump List].[P&ID

number], [Pump List].Purpose, [Pump List].[Motor Size], [Pump List].Starting, [Pump List].New, [Pump List].Comment,

[Pump List].Switchboard, [Pump List].PLC, [Pump List].[PLC Interface], [Pump List].ACH, [Pump List].IL, [Pump List].ZSO,

[Pump List].WIO, [Pump List].ZSP, [Pump List].PSL, [Pump List].FSL, [Pump List].TSH, [Pump List].MSX, [Pump List].SSL
FROM [Pump List];

UNION SELECT [Safety Shower].Table, [Safety Shower].Plant, [Safety Shower].[New Tag], [Safety Shower].[New Comment],

[Safety Shower].[P&ID number], [Safety Shower].Purpose, [Safety Shower].[Motor Size], [Safety Shower].Starting, [Safety

Shower].New, [Safety Shower].Comment, [Safety Shower].Switchboard, [Safety Shower].PLC, [Safety Shower].[PLC

Interface], [Safety Shower].ACH, [Safety Shower].IL, [Safety Shower].ZSO, [Safety Shower].WIO, [Safety Shower].ZSP,

[Safety Shower].PSL, [Safety Shower].FSL, [Safety Shower].TSH, [Safety Shower].MSX, [Safety Shower].SSL
FROM [Safety Shower];

UNION SELECT [Screenings Equipment].Table, [Screenings Equipment].Plant, [Screenings Equipment].[New Tag],

[Screenings Equipment].[New Comment], [Screenings Equipment].[P&ID number], [Screenings Equipment].Purpose,

[Screenings Equipment].[Motor Size], [Screenings Equipment].Starting, [Screenings Equipment].New, [Screenings

Equipment].Comment, [Screenings Equipment].Switchboard, [Screenings Equipment].PLC, [Screenings Equipment].[PLC

Interface], [Screenings Equipment].ACH, [Screenings Equipment].IL, [Screenings Equipment].ZSO, [Screenings

Equipment].WIO, [Screenings Equipment].ZSP, [Screenings Equipment].PSL, [Screenings Equipment].FSL, [Screenings

Equipment].TSH, [Screenings Equipment].MSX, [Screenings Equipment].SSL
FROM [Screenings Equipment];

UNION SELECT Screens.Table, Screens.Plant, Screens.[New Tag], Screens.[New Comment], Screens.[P&ID number],

Screens.Purpose, Screens.[Motor SIze], Screens.Starting, Screens.New, Screens.Comment, Screens.Switchboard,

Screens.PLC, Screens.[PLC Interface], Screens.ACH, Screens.IL, Screens.ZSO, Screens.WIO, Screens.ZSP, Screens.PSL,

Screens.FSL, Screens.TSH, Screens.MSX, Screens.SSL
FROM Screens;

UNION SELECT [Surface Aerator].Table, [Surface Aerator].Plant, [Surface Aerator].[New Tag], [Surface Aerator].[New

Comment], [Surface Aerator].[P&ID number], [Surface Aerator].Purpose, [Surface Aerator].[Motor Size], [Surface

Aerator].Starting, [Surface Aerator].New, [Surface Aerator].Comment, [Surface Aerator].Switchboard, [Surface Aerator].PLC,

[Surface Aerator].[PLC Interface], [Surface Aerator].ACH, [Surface Aerator].IL, [Surface Aerator].ZSO, [Surface Aerator].WIO,

[Surface Aerator].ZSP, [Surface Aerator].PSL, [Surface Aerator].FSL, [Surface Aerator].TSH, [Surface Aerator].MSX, [Surface

Aerator].SSL
FROM [Surface Aerator];

UNION SELECT Tanks.Table, Tanks.Plant, Tanks.[New Tag], Tanks.[New Comment], Tanks.[P&ID number], Tanks.Purpose,

Tanks.[Motor Size], Tanks.Starting, Tanks.New, Tanks.Comment, Tanks.Switchboard, Tanks.PLC, Tanks.[PLC Interface],

Tanks.ACH, Tanks.IL, Tanks.ZSO, Tanks.WIO, Tanks.ZSP, Tanks.PSL, Tanks.FSL, Tanks.TSH, Tanks.MSX, Tanks.SSL
FROM Tanks;

UNION SELECT [UV System].Table, [UV System].Plant, [UV System].[New Tag], [UV System].[New Comment], [UV

System].[P&ID number], [UV System].Purpose, [UV System].[Motor Size], [UV System].Starting, [UV System].New, [UV

System].Comment, [UV System].Switchboard, [UV System].PLC, [UV System].[PLC Interface], [UV System].ACH, [UV

System].IL, [UV System].ZSO, [UV System].WIO, [UV System].ZSP, [UV System].PSL, [UV System].FSL, [UV

System].TSH, [UV System].MSX, [UV System].SSL
FROM [UV System];

UNION SELECT Valves.Table, Valves.Plant, Valves.[New Tag], Valves.[New Comment], Valves.[P&ID number],

Valves.Purpose, Valves.[Motor Size], Valves.Starting, Valves.New, Valves.Comment, Valves.Switchboard, Valves.PLC,

Valves.[PLC Interface], Valves.ACH, Valves.IL, Valves.ZSO, Valves.WIO, Valves.ZSP, Valves.PSL, Valves.FSL, Valves.TSH,

Valves.MSX, Valves.SSL
FROM Valves;

View 1 Replies View Related

Help Needed On A Query Operation

Mar 27, 2007

I am asked to display this, "List the Client Numbers, Name, and Balance fields in the design grid. Sort the records in desending order by the Balance Field."
AND THIS: "Display the top 25%"

I need to know how to show the top "25%" when I press run, in the datasheet without manually having to arrange them.

I would love detailed instructions ASAP!

View 3 Replies View Related

Query In Subform - You Cancelled The Previous Operation

Jun 18, 2007

I have subform where access lists orderitems(the main form contains the order info). The subform uses a query to lookup items thats in the orderlist. Everything works fine execpt when I change something in a dropdowncombo list in the orderlist sheet(like in sample northwind db) the I get "You cancelled the previous operation". The post IS changed and everything seems to work nice but why do I get this error?

The combo uses "SELECT DISTINCT Items.IDItem, Items.Item FROM Items;" as rowsource and also "ListedItems.IDItem" as controlsource.

View 1 Replies View Related

"Operation Must Use An Updatable Query." Error

Oct 3, 2007

I tried to update a column in a table (tblReservedPart) from a query (qryF4101) using Update Query. Here is the code:

UPDATE tblReservedPart INNER JOIN qryF4101 ON [tblReservedPart].[PN]=[qryF4101].[PartCode] SET tblReservedPart.strProductLine = [qryF4101].[IMGLPT]
WHERE [tblReservedPart].[PN]=[qryF4101].[PartCode];

I received an error "Operation must use an updatable query." I am not updating the query. What is the error complaining?

Thanks.
DanYeung

View 1 Replies View Related

Queries :: Update A Query Based On Results From Another Query Using Count Function

Apr 2, 2013

I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).

I have 3 queries which count how many patients come in 5, 4 and 3 times/week.

In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".

I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.

(I'm not using SQL view, I'm using the query design view)

In the "update to:" row, I use the Build function and locate the count I'm looking for.

Problem: when I run the query I get the error: Operation must use an updateable query.

View 3 Replies View Related

Forms :: Update Form / Query Without Updating Underlying Tables To Query

Jul 23, 2015

I have a form which will be used as the basis to print a label.

It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.

What I want to do is to update the form/query without updating the underlying tables to the query.

View 14 Replies View Related

Added A Table To My Query, Now My Query Will Not Let Me Update It.

May 2, 2007

Okay I have two tables in a query.tblMain and tblStatestblStates has a control number and a state name.tblMain has the same control number as well as a bunch of customer info.The query is simply tblMain.* and then the state name from tblStates.It pulls the information correctly when I link the control number. However, I cannot update the information.If it just has the tblMain table in the query then I can update everything. As soon as I add the tblStates I can't update anything, even if I don't link the two together. Can someone help me figure out what is going on?The reason I have to have a seperate table is that this is for pulling a contracted state which can be different than the customers state, and the only way it is listed in our system is by number so it has to use this number as a reference and then pull up the state name.

View 3 Replies View Related

Queries :: Update Query Using Previous Query

Dec 1, 2013

Is it possible to create an update query where the Update To field is set to the results of a previous query? I've been playing around with the Design View but I haven't figured out how to do this yet.

View 3 Replies View Related

Can I Get An Update Query To Not Add Records To Tables Only Update?

Jun 28, 2005

Hi Guys,

I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.

It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.

Any suggestion guys & gals?

Many thanks
Tim

View 9 Replies View Related

Using Criteria To Update Fields In A Update Query

Nov 29, 2006

Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers

View 3 Replies View Related

Update Query/Append Query

Nov 15, 2006

Hi All,

I am using an update query however when clicking on the run button within the query, i keep reciving an error message which i have attached. It basically refering that due to setting a primary Key on the table i cannot add all the records, however i need to set a primary key to the table to stop duplicate entries from being updated. Any help im confused.

The table is set up as:
Line Number (Primary Key)
WorkOrderNumber (Primary Key)
ProdNo (Primary Key)
ProductDescription
Quantity
CylinderSerial Number
Status

I have set the first three fields as primary keys as there cannot be a Workorder number with the same line number and product number as another

for e.g. the follwing results cannot be shown
Workorder number Line Number ProdNo
3333 1 221
3333 1 221

For e.g the follwing results can be shown

Workorder number Line Number ProdNo
3333 1 221
3333 2 221

Any help, can u see what i am doing worng

View 5 Replies View Related

Update Query Won't Update In Access 97

Feb 10, 2008

I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?

Thanks in advance

Wayne

View 5 Replies View Related

Using Update Query To Update Values

Oct 3, 2007

I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.

View 1 Replies View Related

Why Is This Not Updateable

May 26, 2006

In trying to help another member of these forums, I have this query:SELECT [Table1].[name], [Table1].[age], [Table1].[telephone], [Table1].[address], [Table1].[postcode], [tbl3].[ImageBMP]
FROM Table1, tbl3
WHERE [tbl3].[ImageID]=IIf(IsNull([postcode]),2,1);
Basically, the idea is that the image from tbl3 changes if the postcode field is null.

The WHERE clause seems to make this non-updateable, which I don't understand. Any explanation why, and how can I achieve the same result with an updateable result?

View 7 Replies View Related

This Record Set Is Not Updateable

Jul 5, 2005

this record set is not updateable

Me.TimeSpan = Now() - CDate(Me.ArchiveDate)

View 1 Replies View Related

Recordset Not Updateable

Apr 7, 2006

I'm having a problem updating a form. The query from the form is based on two other queries, one of these queries is based on a table from an external database.

I want to use the Form to populate only data on my own database using a checkbox with:

If IsNull (Me.ReceiptDate) Then
ReceiptDate= ExternalReceiptDate 'where ExternalReceiptDate is the receipt date from the external table
End If

I have checked to make sure that the "unique values" of the queries are all set to No.

As I will only be updating data to tables that are on my database that are updatable. I'm hoping that someone can offer some advice if this is possible and why I'm still getting "recordset not updatable" when trying to enter any data to my data fields in the form.

Thanks

View 3 Replies View Related