Queries :: SQL Code - Recordset Is Not Updateable?
Oct 4, 2013
I'm fairly new to SQL and I'm trying to input data into this form I've created but it tells me that "this Recordset is not updateable". Here's my SQL code, I'm not certain this is the problem though.
Code:
SELECT [Annual Instructor Report County Summary].*, [Camps, SS Events, Events, Total Members].*,
[Archery Members].[numArchClubs]+[Archery Members].[numArchNon4H]+[Archery Members].[numArchCamp]+
[Archery Members].[numArchSSEvent]+[Archery Members].[numArchEvent] AS totArchMem, [Rifle Members].
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.
I am trying to check a “Check Box” on my query, but Access won’t allow me to do so as “This recordset is not updateable” - Could anyone please offer an idea of how I can get around this problem…..
I buy lots of products from a supplier, for each product ordered I assign an order ID, and have a check box called “Delivered” for each product to show whether it has been delivered or not. When the delivery arrives I print out our order onto paper and tick each product that has arrived, then I go onto the Access database and tick all of the boxes for the products that have arrived.
This way, any products that haven’t arrived and have not been checked show up on a query to show all products that have the “Delivered” field unchecked.
9 times out of 10 all of the products arrive, so I want to perform a group query on the order ID, and then check the box for the whole order to show that all the products arrived – rather than having to tick off each product (sometimes there can be up to 50 products, and I’ve already gone through the process once on paper so if they are all there I don’t want to have to do it all over again).
For example, the following shows the printed report for the order “1234”:
[Order ID] [Product ID] [Supplier ID] [Delivered] 1234 1 Supplier A NO 1234 2 Supplier A NO 1234 3 Supplier A NO 1234 4 Supplier A NO 1234 5 Supplier A NO 1234 6 Supplier A NO 1234 7 Supplier A NO 1234 8 Supplier A NO 1234 9 Supplier A NO
I’ve been through the delivery from Supplier A and all of the products are there. I perform a group query on the “Order ID”, “Supplier ID” and “Delivered” fields to return the following
[Order ID] [Supplier ID] [Delivered] 1234 Supplier A NO
I want to be able to tick the “Delivered” check box and it automatically check each “Delivered” box for the 9 products mentioned above.
Does anyone know if this is possible? I’ve spent ages going through the properties of the query, changing the Recordset Types and trying to use forms, and going through help files but I can’t find any options. I did come across the possibility of using an update query – however I don’t know how to perform a “Group By” total on the “Order ID”.
Any help or advice would be very much appreciated.
I'm a having a problem with a Yes/No datatype. Fundamentally, my code looks like this:
strSQL = "SELECT * FROM tblAddresses " & _ " WHERE YPID = " & Me.Parent.IDNUMBER & " AND CurrentAddress = yes"
Set rs = db.OpenRecordset(strSQL)
Now this statement returns 3 records even though only 1 Current Address exists.
false also returns 3 records true also returns 3 records no also returns 3 records -1 also returns 3 records 0 also returns 3 records 1 RETURNS 0 records!!
If I look at the table in Access, I only have 1 CurrentAddress record for my IDNumber.
i have a combo box ,and when i should select "all",this button after update code should show me all the column of table test ,but looks like for no reason the record set shows nothing and its not working.
Code:
Private Sub cboTaskListName_AfterUpdate() 'On Error GoTo cboTaskListName_AfterUpdate_Err Me.Refresh Dim db As DAO.Database Dim SQL As String Dim rs As DAO.Recordset If Me.cboTaskListName = "111111" Then Set db = CurrentDb() SQL = "SELECT no1 from test" Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset) End If End Sub
I have a form (frmAssign) based on a query which when opened directly from the database window allows me to update any of the fields.
BUT when I open the same form from a coded button on a different form (frmLastAssignment), my efforts to update any of the fields are thwarted and "Recordset is not updateable" appears in the status bar.
Process That Will Let Me Update Open frmAssign directly from database window.
Process That Won't Let Me Update Open frmLastAssignment, click on "Continue" button to open frmAssign.
Any suggestions of what is happening and how to fix it would be greatly appreciated!
I have a scheduling database that I am only using one table "DateLog"
It contains these fields: JobIDInt-PK LotIDTxt-PK TaskIDInt-PK - List from TaskList Table TripIDInt-PK TaskDateDate ForemanIDInt - List from Foreman Table TaskQtyInt TaskMisc1Txt TaskMisc2Txt NotesMemo
Every time a trip is taken to a job site, a record is created. I would like to have a datasheet view of this table that will join all of the tasks for a particular JobID and LotID together on the same record for easy viewing. The list of tasks are static and will not change from job to job. The only fields that needs to be seen are the JobID and LotID at the row header. The rest of the data is the TaskDate for each task.
What I have attempted so far is to create a separate query for each TaskID and then to join them by the JobID and LotID. I have gotten to the point that I can put values into the datasheet, but I get an error that the primary key cannot be null.
Am I approaching this the correct way? Is there a way I can point the hidden key fields to the fields they are joined to? Access does not seem to be smart enough to figure this out itsself and I am not sure how to tell it to create the new record using the fields that it is related to.
This is very difficult to explain, any help would be greatly appreciated. I will be glad to post further clarification if I did a bad job explaining what I am trying to do.
I am trying to populate a recordset in Access97 using the following code, but cannot seem to get it to work.
The table name is "EventData" that has Type and date fields. startdate and enddate are text boxes on a Form, for wich the user enters the date range, and then clicks a command button to build an Excel spreadsheet on the fly that will be populated with the results of the recordseet.
here is the code:
Dim db As DAO.Database Dim rst As DAO.Recordset
strSql = " SELECT EventData.EventType, Count([EventData].[EventType]) AS [Num Occurrences] FROM EventData WHERE ((EventData.Date) >= (Me!startdate)) GROUP BY EventData.EventType"
The error message that I am getting is "run time error 3061", too few paramaters. ALSO not sure if I need a semicolon right before the closing quote of strSql.
Hy,i have some ADO recordset and i want to bind it to blank report that I made in reports.How to do this. If i create report in reports section and after that i want to see it in my code as that same object and set his record or data source property how to do this?? I know that you can set the recordsource property directly in report but it includes some values from textbox in my form that doesn't show corectly(it's an array of numbers,and i don't know why it doesn't accept it). Thanks
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?
Why I getting an error when trying to run the below code ? If I take out
Me.ClientNameList.Column(1) = rs.Fields("[Tracking Date]") Then . . .
add "And" to
If Me.ClientNameList = rs.Fields("[Client Name]") . . .
I do get a record, but with the wrong date. I need to match the client's name and tracking date, then move the related fields to a MS Access form.
The code follows:
Private Sub ClientNameList_Click() Dim db As Database Dim rs As Recordset Dim i As Integer Set db = CurrentDb Set rs = db.OpenRecordset("Progress Tracking")
I am not sure if my coding is efficient or not but it takes so long to read lets say tbCOMPANY 40k records and find if values of two columns exists in tbRESULT which holds 30k records. Then if it doesn't find any records in the tbRESULT it will just insert the row otherwise an update will be executed.
What I did was:
1. Read tbCOMPANY each row using for loop 2. tbCOMPANY.FindFirst to lookup if values exists in the tbRESULT 3. If tbCOMPANY.noMatch, it will insert into tbRESULT 4. else it will update the matched row in the tbRESULT
Its been taking like over 30 minutes to process this and still ongoing and it will still keep on running. Now Access is showing as Not Responding in the task manager.., Any efficient way to do this?
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.
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
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.
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.
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])); -----------------------------------------------------------------
I have a form that has normal text boxes for data input and a couple drop down boxes that look at another table for the selection choices. Everything is great. What I want to do is give the user a drop down list but also give them the ability to add to that look up table. Where if the choice is not in the drop down list they can type in a new value and it will update the Look up table so it will show up the next time.
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)
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode: ? me.fsubsrNavSHLBHL("NavSH_QQ") NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line: fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window: ? me.fsubsrNavSHLBHL.Form.RecordSource Select * from vsrNavigatorSHLBHL where Well_ID =91229
I have a query (that gets it's data from several other queries) with a column called "max." The data in the column is correct, but when I call on the query in VBA, it shows me incorrect data.Here is where I call the query:
Code:
Dim db As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset Dim strReport As String Set db = CurrentDb() Set qdf = db.QueryDefs("7-ErrorsReport") qdf.Parameters(0) = Forms!frmmain!dt1.Value qdf.Parameters(1) = Forms!frmmain!dt2.Value qdf.Parameters(2) = Forms!frmmain!d2.Value
[code]....
The query looks at a table of employees and finds out if they have been issues a warning letter before, then prints out a corresponding report based on the "max" warning level they are at.The problem arises when an employee graduates from a 6 month probation period - all letters in that period should be ignored. As i said, they are ignored correctly when i run and view my query ("7-ErrorsReport") because they are filtered out at that point, but for some reason when this code runs, it somehow sees the previously issued letters which are stored in a table and likely in some of the other queries.
I am not sure if there is some issue with the query tree I have set up which is necessary to get the results I need, or if something is wacked with the was I am using it as a recordsource.
how to grab the recordset after a user has filtered a datasheet and export it to EXCEL with VBA from a Button on a form.
I've figured out how to export a pre-defined query to excel with all the fields I need. I was hoping to be able to grab the filters from the datasheet form and pass them to the query.
I have a main form with a bound Datasheet subform. The export button is on the main form and I need to grab the filtered data from the datahsheet subform.
Hey all.. Been out of the Access loop for quite some time now.. Last project I did was in Acc97, about 5 years ago! Been working on SQL Server and web programming lately...
Anyway, I have a project that needs to be done in Access XP. It is a basic resource allocation DB. Listing of employees, projects, and time associated with those projects per Employee. I want to keep this normalized, and so I have this basic table layout..
tblEmployee -- Has EmployeeID, name, etc tblProject -- Has ProjectID, name, etc tblEmployeeProject -- Has EmployeeID, ProjectID, year, month, hours tblWorkingHours -- Has an autoNumber ID, Year, Month, Working Hours, Holidays, business days
Basically, the tblWorkingHours has 12 entries per year, Jan-Dec. The tblEmployeeProject is where I have the meat of my data.. If an employee(1) is assigned to a project(123) for 20 hours a month for 3 months(Jan-Mar 2008), then there will be 3 rows in there. EmployeeID - ProjectID - Year - Month - Hours 1 - 123 - 2008 - 1 - 20 1 - 123 - 2008 - 2 - 20 1 - 123 - 2008 - 3 - 20
This seems to make sense to me, and seems to be normalized. However, I cannot seem to create an updatable query on this layout... I would like to pick a project, and then a few employees assigned to that project, and then enter in the hours for each employee, for any of the 12 months I need. The problem I'm having is that I cannot seem to get an updatable query out of this layout. The only way for me to get a listing of all employees attached to a project, and then all months for the year, regardless of an entry in tblEmployeeProject, is to use a Cartesian Product type join on tblEmployee and tblWorkingHours. And then do an outside join on tblEmployeeProject. However this is obviously not updatable.
The only other option I can think of, is to prefill the tblWorkingHours table with 12 entries per year, however this seems to add unnecessary data in the database. At that point, I might as well create a table that has 12 columns, one for each month.
Sorry for the long-winded post, but wanted to try to get all my points across. Any help would be greatly appreciated! Thanks all.
