Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recordset not updateable (access 2000)

    I have a query sitting on top of another query and when I try to make a change to any field in the second query it says that the "recordset is not updateable" yet I can make changes on the first query. Any answers?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordset not updateable (access 2000)

    Your description is very general. Can you give us some details of the queries? For example, you could post the SQL of both queries.

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset not updateable (access 2000)

    BELOW IS THE SQL STATEMENTS AND ON THE FIRST ONE IN THE "PAID?" FIELD IT WILL UPDATE BUT IF ONE TRIES TO CHANGE THE "PAID?" FIELD ON THE SECOND QUERY AN ERROR MESSAGE OCCURS WHICH SAYS "RECORDSET IS NOT UPDATEABLE" Thanks in advance

    1st SQL Statement is as follows:
    SELECT [dt-JobTickets].ID, [dt-JobTickets].Mileage, [dt-JobTickets].[Next Call Date], [dt-JobTickets].Technician, [dt-JobTickets].[14pt Oil Change], [dt-JobTickets].[14pt Oil Filter], [dt-JobTickets].[Lubricate Chassis], [dt-JobTickets].TransmissionFluid, [dt-JobTickets].PowerSteerFluid, [dt-JobTickets].RadiatorFluid, [dt-JobTickets].LightsChecked, [dt-JobTickets].FanBeltChecked, [dt-JobTickets].BrakeFluid, [dt-JobTickets].WasherFluid, [dt-JobTickets].BatteryFluid, [dt-JobTickets].WiperBlades, [dt-JobTickets].AirFilter, [dt-JobTickets].AddRadQts, [dt-JobTickets].PriceOil, [dt-JobTickets].FuelFilter, [dt-JobTickets].Comments, [dt-JobTickets].VIN, [dt-JobTickets].[Vehicle ID Number], [dt-JobTickets].AddOilQts, [dt-JobTickets].AirFilterType, [dt-JobTickets].AddTransQts, [dt-JobTickets].RepairTicketNbr, [dt-JobTickets].LaborAmt, [dt-JobTickets].PartsAmt, [dt-JobTickets].Distributor, [dt-JobTickets].DistributorsInvoiceNbr, [dt-JobTickets].DistributorsCreditNbr, [dt-JobTickets].Date, [dt-VIN].Year, [dt-VIN].Make, [dt-VIN].Model, [dt-VIN].[Clients ID], [dt-VIN].CustomerName, [dt-VIN].OilQuartsCapacity, [dt-JobTickets].EnviroFee, [dt-JobTickets].WiperPrice, [dt-JobTickets].AirFilterPrice, [dt-JobTickets].FuelFilterPrice, [dt-JobTickets].TransmissionPrice, [dt-JobTickets].CoolantPrice, [dt-JobTickets].AdditionalRepair1, [dt-JobTickets].AdditionalRepair2, [dt-JobTickets].AdditionalRepairPrice1, [dt-JobTickets].AdditionalRepairPrice2, [dt-JobTickets].PurchaseOrder, [dt-JobTickets].Diesel, [dt-JobTickets].DieselOilChangePrice, [dt-JobTickets].[paid?], [dt-JobTickets].paidChqCCnbr, [dt-JobTickets].PaidNotes
    FROM [dt-JobTickets] RIGHT JOIN [dt-VIN] ON [dt-JobTickets].VIN = [dt-VIN].VIN
    WHERE ((Not ([dt-JobTickets].ID) Is Null) AND (([dt-JobTickets].Date)>=[Forms]![f-Invoices]![AXinvoiceDateFrom] And ([dt-JobTickets].Date)<=[Forms]![f-Invoices]![AXinvoiceDateto]) AND (([dt-VIN].CustomerName)=[Forms]![f-Invoices]![UCinvoiceCustomer]))
    ORDER BY [dt-JobTickets].ID;

    2nd SQL STATEMENT
    SELECT [q-JobTicketInv 10].ID, [q-JobTicketInv 10].Mileage, [q-JobTicketInv 10].[Next Call Date], [q-JobTicketInv 10].Technician, [q-JobTicketInv 10].[14pt Oil Change], [q-JobTicketInv 10].[14pt Oil Filter], [q-JobTicketInv 10].[Lubricate Chassis], [q-JobTicketInv 10].TransmissionFluid, [q-JobTicketInv 10].PowerSteerFluid, [q-JobTicketInv 10].RadiatorFluid, [q-JobTicketInv 10].LightsChecked, [q-JobTicketInv 10].FanBeltChecked, [q-JobTicketInv 10].BrakeFluid, [q-JobTicketInv 10].WasherFluid, [q-JobTicketInv 10].BatteryFluid, [q-JobTicketInv 10].WiperBlades, [q-JobTicketInv 10].AirFilter, [q-JobTicketInv 10].AddRadQts, [q-JobTicketInv 10].PriceOil, [q-JobTicketInv 10].FuelFilter, [q-JobTicketInv 10].AddOilQts, [PriceOil]*[AddOilQts] AS [Tot$AddtlOil], [q-JobTicketInv 10].Comments, [q-JobTicketInv 10].VIN, [q-JobTicketInv 10].[Vehicle ID Number], [q-JobTicketInv 10].AirFilterType, [q-JobTicketInv 10].AddTransQts, [q-JobTicketInv 10].RepairTicketNbr, [q-JobTicketInv 10].LaborAmt, [q-JobTicketInv 10].PartsAmt, [q-JobTicketInv 10].Distributor, [q-JobTicketInv 10].DistributorsInvoiceNbr, [q-JobTicketInv 10].DistributorsCreditNbr, [q-JobTicketInv 10].Date, [q-JobTicketInv 10].Year, [q-JobTicketInv 10].Make, [q-JobTicketInv 10].Model, [q-JobTicketInv 10].[Clients ID], [q-JobTicketInv 10].CustomerName, [q-JobTicketInv 10].OilQuartsCapacity, [dt-Customers].AddressStreet, [dt-Customers].AddressCity, [dt-Customers].AddressState, [dt-Customers].AddressZip, [q-JobTicketInv 10].EnviroFee, [q-JobTicketInv 10].WiperPrice, [q-JobTicketInv 10].AirFilterPrice, [q-JobTicketInv 10].FuelFilterPrice, [q-JobTicketInv 10].TransmissionPrice, [q-JobTicketInv 10].CoolantPrice, [dt-Customers].PriceOLF, [q-JobTicketInv 10].AdditionalRepair1, [q-JobTicketInv 10].AdditionalRepair2, [q-JobTicketInv 10].AdditionalRepairPrice1, [q-JobTicketInv 10].AdditionalRepairPrice2, [dt-Customers].TaxExempt, [q-JobTicketInv 10].PurchaseOrder, [dt-Customers].PayTerms, [q-JobTicketInv 10].Diesel, [q-JobTicketInv 10].DieselOilChangePrice, [q-JobTicketInv 10].[paid?], [q-JobTicketInv 10].paidChqCCnbr, [q-JobTicketInv 10].PaidNotes
    FROM [dt-Customers] RIGHT JOIN [q-JobTicketInv 10] ON [dt-Customers].ClientName = [q-JobTicketInv 10].CustomerName;

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordset not updateable (access 2000)

    If there is no unique index on the ClientName field in the dt-Customers table (that is the field used to join it to q-JobTicketInv 10), the query will not be updateable.

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordset not updateable (access 2000)

    Thank you HansV...Worked fine.. But why is it so? Why does it need a primary key?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordset not updateable (access 2000)

    Microsoft changed something in the query engine between Access 97 and Access 2000. It is now much more picky about things like this. Why? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    BTW It does not need to be a primary key, having a unique key is enough.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •