Ms-access – Using an independent textbox to write different values for different rows/registers in a continuous form, repeats the value in every row

ms access

I have a form that is using a continuous form view to show the following:

  • The name of the different products I have.
  • In another column I have the actual quantity of the product.รง

This is made using a form that has the products table as origin, and the name of the products' name textbox is taking the value from table's product name field.
The Quantity field's source is the quantity field in the same table.

All is working fine. I just do this to show the names and quantities but I don't want this fields in this table to be edited so I locked and disabled them.

The problem comes now:
I made an independent textbox field so that I could write a quantity, not for substituting the old one, but for purchase, so that written quantity is subtracted to the quantity field.
I wanted to throw this information to a new table with the id of the product, the quantity and the date but the problem for now it that whenever I write the purchase quantity in the independent textbox and press enter, or Tab, or change to the next one, all the independent textboxes in every row for different products update with the same value.

I am just learning access by experimenting and trial and error.

Another thing I realized is that since I used a linked form to a table, and I didn't take the Id, I can't use that Id to update the table or put it to another registry table so I'm stuck.

I thought It would be easier, just writing a query using the fields I need for an insert into query and trigger it using a button click event but I must be approaching it the wrong way.

Best Answer

You must bind the textbox to a field. A way to do it without having to alter your original table is to create a dummy table that will hold these extra values. This table can be in the local DB.

Table Dummy with columns:

    Id     Numeric / Long, Primary key (with a type matching the Id in the first table)
    Value  with appropriate type

Now, base your form on a query looking like this

SELECT *
FROM
    OriginalTable
    LEFT JOIN Dummy ON OriginalTable.Id = Dummy.Id

Add a textbox bound to Value. Whenever you enter a value there, this will automatically add a record to the Dummy table. Always delete the records of this table before opening the form.

But of course you could simply add a new column to the original table, if this is acceptable.


As for the other problem: Always include the Id column in your query. You don't need add a textbox for it.