How to set a field as Updateable

[Jass7869]

I am trying to input info into my form

I got

Me.TotalBC1.Value = Me.TotalBC1.Value

Now it's saying the "Recordset is not updateable" and I can't find it for the textbox where to change it to an updateable field

 

[kevlray]

I do not really have a answer, just a question. Why are you setting the value to itself?

 

[Jass7869]

Just trying different things
:banghead:

 

[kevlray]

If the control is bound by a query, table, etc. then you cannot update the value by setting it the way you have you have shown. Please provide more information about what you are trying to accomplish.

 

[Jass7869]

I am trying to enter in accounting info for each different analyst into form.

I have two tables one with the Analyst and who they report to and what institution and the other table with the budget for each analyst. I made a query to draw the analyst and other info they need and the budget for each analyst. Once I do that I need a form for them to enter the data ..allocating to each item of the budget. This is where I run into a problem. I have checked the internet...I can't even update on the query. I export the query into a excel file and I can do my data enter. But my boss wants in the DB on a form.

 

[kevlray]

Not sure where the text box comes into play. Are you trying to update two tables at the same time with a query? Not sure that is possible. I deal more with MS-SQL than Access, so I might not be the best person to try to tackle this issue.

 

[Jass7869]

Thanks for helping..anyone else. I am trying to update a field from my query that is from my table. I have two tables coming into the query..

 

[jdraw]

Please do a screen capture of your relationships window and post.
Please post the sql of your query also.

 

[Jass7869]

SELECT DISTINCT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Org Name:], Final_Table.Field2, Final_Table.[Fund:], Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.[BC1_Change], Final_Table.[TotalBC1], Final_Table.[BC2_Change], Final_Table.[TotalBC2], Final_Table.[BC3_Change], Final_Table.[TotalBC3], Final_Table.[BC4_Change], Final_Table.[TotalBC4]
FROM Final_Table INNER JOIN dbo_tblOrgLook_master ON Final_Table.ID = dbo_tblOrgLook_master.OrgLookID
WHERE dbo_tblOrgLook_master.Analyst IN('D.Heitz') AND dbo_tblOrgLook_master.Org IN('5340') AND dbo_tblOrgLook_master.CostCenter IN('0560000000') AND dbo_tblOrgLook_master.Fund IN('15G0010000' , '15G0010001' , '15G0020000') AND dbo_tblOrgLook_master.PEC IN('2550000');


this is also hard code into my code ...As I have an initial form the DBO_tblOrglook...provides the fields. Then from I have it run a query of my two tables. the query is not allowing any updates. I have tried everything...please help

 

[Jass7869]

Has far as the relationship page I only have the Final_table..I have tried several different relations and nothing is working

 

[Minty]

I am guessing that Final_Table contains the fields you are trying to update? What fields are you trying to update with what data?

Could you post a screen shot of the query in design view, I'm certain that the query is linked in such a way that it would not be updateable.

Is dbo_tblOrgLook a SQL Table or a View?

 

[Jass7869]

I am able to do paste a screen shot...i am able to put it into a email. That's weird

 

[MarkK]

A query that uses the DISTINCT keyword is not updatable. There may be multiple rows in a table that are represented by one row in a DISTINCT query, therefore, which row would be updated is ambiguous. Such a query is read-only by definition.

 

[Jass7869]

So I did remove the "Distinct"..but then my details in my form disappear. I conquer one problem and then come up with another.

 

[MarkK]

What I recommend with queries is play with them in the query designer. The SQL you posted, for instance, copy that into the SQL view of a new query, delete the word DISTINCT, and run it. What happens? Does the query return records after you have made this change? If the query is updatable, but returns no records, then it will be showing a new record, which, obviously, has no data.

 

[Jass7869]

Thanks Markk...I will try that as I need to have six input fields. Also I noticed if I take out the DISTINCT...it gives me two records exactly the same. Any ideas

 

[Minty]

The way you have joined the query is causing the problem, the 2 records indicate that something in the underlying data is pulling a second matching record. The record is probably not EXACTLY the same it just links with your query.

As Markk suggested use the query editor to play around to get an updateable recordset. You may need to break it down into a number of queries, that you then link or requery a second time.

 

[Jass7869]

Hello Minty,

I got rid of the "Distinct" and I have been able to update my query. Now the problem is that my blank fields for the user to input data into, will still not allow me to enter data into those fields. Any ideas?

 

[jdraw]

Please tell us more about your tables:
.Final_Table
.dbo_tblOrgLook_master

What is the Primary Key of each?

What do they represent?
What are you trying to do in plain, simple English?

 

[Jass7869]

Table Org--Has my Analyst and which institution that report too. Their Program name..etc

The Final_Table has the institution with the program and thier budget. I ran a query to pull this data. I also have blank fields for the Analyst to make change to the budget according to thier institutions.

SELECT Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Org Name:], Final_Table.Field2, Final_Table.[Fund:], Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.[BC1_Change], Final_Table.[TotalBC1], Final_Table.[BC2_Change], Final_Table.[TotalBC2], Final_Table.[BC3_Change], Final_Table.[TotalBC3], Final_Table.[BC4_Change], Final_Table.[TotalBC4]
FROM Final_Table LEFT JOIN dbo_tblOrgLook_master ON (Final_Table.PEC = dbo_tblOrgLook_master.PEC) AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) AND (Final_Table.Field2 = dbo_tblOrgLook_master.CostCenter)
WHERE dbo_tblOrgLook_master.Analyst IN('D.Heitz') AND dbo_tblOrgLook_master.Org IN('5340') AND dbo_tblOrgLook_master.CostCenter IN('0560000000') AND dbo_tblOrgLook_master.Fund IN('15G0010000') AND dbo_tblOrgLook_master.PEC IN('2560000');


Now from the query I am making a form where it will display the Analyst and thier budget..and the blank fields for them to make adjustment.

THe problem I am having now is the blank fields will not allow me to enter any data.