Solved

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

Posted on 2014-11-03
21
142 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 34

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 34

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 36

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 34

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 36

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 34

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
 

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 34

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 36

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 36

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 34

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 34

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 34

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

730 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