When you have a form from a query why wont it allow user input

I have a query from two tables.  I took the query to make a form and the user input repeats for all the queried records
Jass SainiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
Generally this happens when the form uses fields from the "child" table.

You can try looking at the form properties and set the Recordset Type property to Dynaset (Inconsistent Updates) instead of Dynaset.

Mike EghtebasDatabase and Application DeveloperCommented:
Run the query supplying the data. Try editing this query. If it allows the edit, then you need to change your form's setting as Kelvin has described. Otherwise, you have joins other than inner join in your query.
Kelvin SparksCommented:
Good thought Mike. I had overlooked that.

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Mike EghtebasDatabase and Application DeveloperCommented:
As you know, I am your student. You have helped me a lot in the past.



BTW,  JAss SAini is a new member. Welcome to EE  JAss SAini.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the SQL of the query? To do that, open the query in the designer, and select the SQL View. Copy and paste that here.

In general, basing a form on a query that includes more than one table can be troublesome, especially if you're dealing with Parent/Child data, as the other Experts have mentioned. If you have a Parent/Child data relationship, you should be using a MainForm/Subform form. This will show the data relationship correctly, and will allow the user to add Child records to a specific Parent, and will also allow the user to add new Parent records, edit existing Parent or Child records, etc.
If you are worried about the repetition, then use a main form for the parent table and a subform for the child table.  Make sure to set the master/child links so Access will keep the two sync'd.

If you can't update the query, you may be joining on the wrong columns so Access can't determine the actual relationship between the two tables.
Jass SainiAuthor Commented:
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.[Total BC #1 Change to Authority], Final_Table.[Total BC #1 Allotment], Final_Table.[Total BC #2 Change to Authority], Final_Table.[Total BC #2 Allotment], Final_Table.[Total BC #3 Change to Authority], Final_Table.[Total BC #3 Allotment], Final_Table.[Total BC #4 Change to Authority], Final_Table.[Total BC #4 Allotment]
FROM dbo_tblOrgLook_master RIGHT JOIN Final_Table ON (dbo_tblOrgLook_master.CostCenter = Final_Table.Field2) AND (dbo_tblOrgLook_master.PEC = Final_Table.PEC)
WHERE (((dbo_tblOrgLook_master.Analyst) In ('A.Gladden')) AND ((dbo_tblOrgLook_master.Org) In ('5351')) AND ((dbo_tblOrgLook_master.CostCenter) In ('0250000000')) AND ((dbo_tblOrgLook_master.Fund) In ('13G0010000')) AND ((dbo_tblOrgLook_master.PEC) In ('2550000')));

Here is my SQL to the query.  I tried running the query and making changes and it will not allow me to make changes.  help
Mike EghtebasDatabase and Application DeveloperCommented:
You need to rethink/revise your query. SELECT DISTINCT and RIGHT JOIN you have will not allow you to edit the data.

As Kelvin pointed out you have master/child in one query mixed with outer joins. You need to simplify the query. Have two queries one to supply data to your main form and the other to a subform.
The Distinct predicate aggregates data and queries that aggregate data are not updateable so as eghtebas said, that needs to go.  The Right Join is not an issue.  I am concerned that the join is on two columns.  Is the primary key of the one-side table a compound key?
Mike EghtebasDatabase and Application DeveloperCommented:
Suggested queries for your consideration:
   , o.Org
   , o.OrgName
   , o.CostCenter
   , o.Fund
   , o.PEC
   , or.ProgramName
FROM dbo_tblOrgLook_master o  
o.Analyst In ('A.Gladden') AND 
o.Org In ('5351') AND 
o.CostCenter In ('0250000000') AND 
o.Fund In ('13G0010000') AND 
o.PEC In ('2550000');
   , f.[Org Name:]
   , f.Field2
   , f.[Fund:]
   , f.[Line Item:]
   , f.[Item Number:]
   , f.[Total Initial:]
   , f.[Total BC #1 Change to Authority]
   , f.[Total BC #1 Allotment]
   , f.[Total BC #2 Change to Authority]
   , f.[Total BC #2 Allotment]
   , f.[Total BC #3 Change to Authority]
   , f.[Total BC #3 Allotment]
   , f.[Total BC #4 Change to Authority]
   , f.[Total BC #4 Allotment]
FROM Final_Table f 

Open in new window

Jass SainiAuthor Commented:
Unfortunely I need both tables.  I am still new at this and was given a half bulit DB...The Org table has the Analyst which I need in order to do the search.  My first form is based off that...my query is based off the two tables.  (Can not edit Query for the Blank fields)...My report is based off the query too.  Everything is working except for the last form ..which is based off the query.  I even tried to unbound the fields..put if I place a input into the field it carries to all the records that were queried.
Mike EghtebasDatabase and Application DeveloperCommented:
Correct me if I am wrong:

You have Analyst and AnalystActivity (or something similar):

Make frmAnalyst  to use table dbo_tblOrgLook_master for that as recordsource.

on frmAnalyst  add a subform called sfrmAnalystActivity based on the other table.

Link the master/child fields as required.

This way, when you select an analyst in frmAnalyst, the subform displays all activity editable records for that particular analyst. Meaning your where clause will be also simplified.

You need to get to a point where the query is updateable before the form will be updateable.
What are the primary keys of the two tables and what columns are you joining on?  A one-to-many join (which most are) goes from the PK of the 1-side table to a data field on the many-side table.  The data field on the many-side table is occasionally part of a compound PK on the many-side table.
Post your actual query as you change it.

PS - your column names should be changed before you go much further to conform to standard practice.  They are also on the long side.
[Total BC #2 Change to Authority] could be TotBC2ChngToAuth
Removing the special characters and embedded spaces eliminates the need for the square brackets.  When you use abbreviations, the most important thing is to be consistent.
Jass SainiAuthor Commented:
Mike..dumb question so how do I linl master/Child fields.  I am getting good that this with your help/everybody's help.  This is the only forum where I can actually get answers.  Thank you all...My boss wants me to change the form..so starting from scratch.
Click on the subform control.  Then open the properties dialog and on the data tab, you can enter the master/child links.
Mike EghtebasDatabase and Application DeveloperCommented:
As Pat describes, do that. Access generally will suggest master/child links. Master is the field you have on the form which you want to the subform child field.
Jass SainiAuthor Commented:
Everybody on here is so helpful and you get feedback quickly.  They don't belittle you if you don't know, they try to help you.  I love this website.
Mike EghtebasDatabase and Application DeveloperCommented:
JAss SAini,

You forgot to split the points. Because you are new it is understandable; but you can request to reopen the question so you can distribute the points among experts they helped you.

In distributing the points, there are tow factors you may want to consider.

1. Whomever gave you the answer first (If you look at EE logo, it is like car racing flag, meaning whomever answers first, gets the points).

2. The effort level and its worth to you.

FYI, these points do not translate to money for the experts. It is just a game we play. I am a member just yourself joined Apr 2003. I am sure pretty soon, you will start reading some of the questions to propose a solution and becoming an EE expert yourself. Here all of use love to help others meanwhile sharpen our skills,



P.S. Please put a request to reopen this question so you can distribute the points. If you do not know how to do this, please let me knwo.
Jass SainiAuthor Commented:
Hello Mike...Sorry...but how do you repopen so I can distribute the points.
Mike EghtebasDatabase and Application DeveloperCommented:
No problem. I have not used it for a while and EE keeps improving the site functionally. So, if the following didn't work please ask me again.

1. Go to: http://www.experts-exchange.com/Community_Support/General
2. From the menu at the top, select Solve/As a Question.
3. For sublect add: Reopen Question
4. For the body of message enter: http://www.experts-exchange.com/Database/MS_Access/Q_28550060.html#a40422431

This is the url of your question.

5. Click on Submit



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.