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

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;
SteveL13Asked:
Who is Participating?
 
MarvinM80Connect With a Mentor Commented:
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
 
Nick67Connect With a Mentor Commented:
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
 
SteveL13Author Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Nick67Connect With a Mentor Commented:
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
 
SteveL13Author Commented:
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
 
SteveL13Author Commented:
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
 
Nick67Commented:
@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
 
SteveL13Author Commented:
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
 
Nick67Commented:
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
 
SteveL13Author Commented:
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
 
Nick67Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.