Solved

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

Posted on 2014-11-03
21
137 Views
Last Modified: 2014-11-06
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
0
Comment
Question by:Jass Saini
  • 8
  • 5
  • 4
  • +2
21 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40420907
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.

Kelvin
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40420937
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.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40420941
Good thought Mike. I had overlooked that.


Kelvin
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40420943
As you know, I am your student. You have helped me a lot in the past.

Thanks,

Mike

BTW,  JAss SAini is a new member. Welcome to EE  JAss SAini.
0
 
LVL 84
ID: 40421165
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40421670
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.
0
 

Author Comment

by:Jass Saini
ID: 40421706
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
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 375 total points
ID: 40421763
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.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 40421820
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?
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40421838
Suggested queries for your consideration:
SELECT 
   o.Analyst
   , o.Org
   , o.OrgName
   , o.CostCenter
   , o.Fund
   , o.PEC
   , or.ProgramName
FROM dbo_tblOrgLook_master o  
WHERE 
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');
==========
SELECT 
   f.ID
   , 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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Jass Saini
ID: 40421899
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.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40421987
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.

Mike
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40422052
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.
0
 

Author Comment

by:Jass Saini
ID: 40422224
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40422241
Click on the subform control.  Then open the properties dialog and on the data tab, you can enter the master/child links.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40422299
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.
0
 

Author Comment

by:Jass Saini
ID: 40422312
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.
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 375 total points
ID: 40422341
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,

Regards,

Mike  

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.
0
 

Author Comment

by:Jass Saini
ID: 40422431
Hello Mike...Sorry...but how do you repopen so I can distribute the points.
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 375 total points
ID: 40422453
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

Thanks,

Mike
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now