Solved

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

Posted on 2014-11-03
21
140 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 35

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 35

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
 

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 35

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 35

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 35
Combobox row source 2 21
Switch 5 17
Sum with where criteria on a report 5 17
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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