Solved

Why when enter a number in a field on a form are all the field populating with that number

Posted on 2014-02-18
11
445 Views
Last Modified: 2014-02-25
I have a form in continuous forms view.  The forms record source is a query.  Also on the form is an unbound field.  When I enter a number in that field, all of the records get filled in with that number instead of just the record I am working on.

If the query is the problem and I can't figure out why, here is the SQL:

SELECT tblProducts.LocationID, tblProducts.ProdRef, Sum([IncomingQty]-[SoldQty]) AS Balance, tblInventoryDetails.ProductID, tblLocations.LocationDesc
FROM (tblProducts INNER JOIN tblInventoryDetails ON tblProducts.ProductID = tblInventoryDetails.ProductID) INNER JOIN tblLocations ON tblProducts.LocationID = tblLocations.LocationID
GROUP BY tblProducts.LocationID, tblProducts.ProdRef, tblInventoryDetails.ProductID, tblLocations.LocationDesc
HAVING (((tblLocations.LocationDesc) Like "*" & [Forms]![frmSelectLocationForReconcileInventory]![txtLocationName] & "*"))
ORDER BY tblProducts.ProdRef;
0
Comment
Question by:SteveL13
  • 5
  • 5
11 Comments
 
LVL 1

Accepted Solution

by:
MarvinM80 earned 167 total points
ID: 39868785
Hi Steve,

This sounds like a problem that I had a while ago. Let me guess. Is the field in question in the detail section of the form? That's what I did. So even though the field is just one, unbound control on the form in design view, it is actually the same field (control) repeated for every record in the detail section. To illustrate, let's call the control "txtNewField". Then you add that control in the detail section after the bound controls for "ItemID" and "ItemName". When the detail section repeats for every record, your results might look like this:

ItemID     ItemName     NewField
  121          Widget1          txtNewField
  122          Widget2          txtNewField
  123          Widget3          txtNewField
  124          Widget4          txtNewField

Since the control, txtNewField, is not bound to the record source, it simply repeats that control again and again. So if a value is entered in that control, such as "New Thing", then that value is also repeated for every record.

The way that I resolved this was to create a temp table that included a column for that new field. Then the control can be bound to it. That way there will be a different value for each record. If your form is bound to the query results, then just add an empty column to your SELECT statement. That should work, but I am only familiar with tables.

HTH
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 333 total points
ID: 39869126
Yup,

An unbound control on a continuous form can only have a single value.  In a report, each time the detail section formats, something different can occur and therefore different values can show if you manipulate the control with VBA code -- but a form only has a Current Event and therefore whatever value you enter into the unbound control on any one record becomes the value they all show (note that because they are unbound this has no effect on data in tables.)

I have an article about the limitations of unbound fields and workarounds here
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_6692-Overcoming-unbound-continuous-forms-limitations-by-staging-data.html

Unbound controls on continuous forms are NOT like VB6 control arrays, unfortunately.
0
 

Author Comment

by:SteveL13
ID: 39872054
Just so I understand... I need to create a temporary table that the unbound field values can drop into.  Then when I'm done and close the form do I need to run a delete query to dump the records that were added to the temporary table?  I expect so.  Please confirm.

-Steve
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 333 total points
ID: 39872084
a temporary table that the unbound field values

No, you cannot have a unbound control on a continuous form that will have anything but a single value (whatever you've entered into it by hand, or by code).  Unbound controls display only a single value for each and every record on a continuous form -- and that's the problem.

Only a bound control can display differing values for each record on a continuous form.  So you need to bind that presently unbound control to SOMETHING.  Now, if you already had a good 'something,' you'd have bound the control already and not had a problem.

So you create a table and field(s) to hold the data that you'd like to enter into that presently-unbound control.  You have to code to fill that table with what you'd like to initially display, and you have to code a method to take the data out of that 'temporary data' table and put it in its permanent home, and you have to code to dump the 'temporary data' out for the next time.

The table itself is not temporary.  Creating/deleting tables repeatedly is a good way to screw up a db.  The data in it is temporary.  

Generally, I have code that runs
Application.SetOption "Confirm Action Queries", False
DoCmd.RunSQL ("delete * from tblTemporaryData;")
Application.SetOption "Confirm Action Queries", True
and then I run some recordset code that adds data back into tblTemporaryData
Then I open the form.

The form has two buttons -- add new records, or discard this data
(generally, an update scenario has no need of unbound controls)
and the code need to knock the data into its permanent location
0
 

Author Comment

by:SteveL13
ID: 39872100
Regarding...

"If your form is bound to the query results, then just add an empty column to your SELECT statement."  

I don't know how to do that.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:SteveL13
ID: 39872110
I added the  empty column to your SELECT statement but when I try to make an entry in that field I get a "This recordset is not updateable"
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39872139
@MarvinM80

Given Table1 with fields ID and Alpha, you can create a query
SELECT Table1.ID, Table1.Alpha, "" AS Beta FROM Table1;

And build a form around it, but you cannot edit Beta.
It is "", and is not editable.

For a form control to be editable, the data it contains must also be editable.
0
 

Author Comment

by:SteveL13
ID: 39873663
I am still stuck.  This continuous form uses a query as it's recordsource.  But also on the form is an unbound field.  When I enter a number into the unbound field all of the records display that number in the field.

Is very frustrating and I've tried everything I can think of.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39874155
That behavior is by design, and you cannot change it.
In order to have different values in a control for each record, the control MUST be BOUND.

Why do you have an unbound control on the form?
What is its purpose?
What kind of control is it? A textbox?
Given that it is unbound, what do you intend to do with the values entered into it?
(Since it is unbound those values are not going into any records in any table)

What code are you using to do things with the values you'd like to enter into this control?
0
 

Author Comment

by:SteveL13
ID: 39874228
It is a number field.  After the number is entered a data entry will be made into a table.  The value of the data entry into the table is a calculation based on the number field a number is entered into x another field on in the query.  So I added this unbound field to the table, made it a bound field, and now when I try to make an entry I get the "This recordset is not updateable" message.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39874476
So, the first thing is to open the table.
Is the new field accepting data there?
It should without question.
Next open the query
Is the new field accepting data there?
If not, look at the query and ensure that you don't have cruft left from earlier experiments

Then try the form.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

707 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

11 Experts available now in Live!

Get 1:1 Help Now