Working with Recordsets...

[CraigDolphin]

Same problem.

...and yes, unless they're wearing their woolies

 

[mdschuetz]

The best way to do this is to set the record source of the puchase orders subform to the inventory transactions query instead of the table. I don't think you're going to get what you want (whatever that is) just by messing around with an unbound box. If you set the RS to the Invent. Trans. query and include in that a calculated field for the "Due" column, it will display the "column - column" figure that you are looking for in each record.That's not at all a mistake Md, because the code you wrote was with the OnCurrent event. That's exactly what's suppose to happen.

Personally, I think you are reading way too much into this!! I don't have a lot of time to try and figure out what means what in your database, but if the above solution does result in an answer for you, I think people would be "all ears" regarding an explanation of what your intentions were when setting this up. I personally, wouldn't mind hearing...

*What exactly the "due" column is for.
*And what role you want that column to play in the "live" computation of "on-hand" inventory or tracking receipts.

Just by browsing through, I can see the typical setup for inventory tracking based on purchase orders, and the differences in receipts/shipments. More explanation, even in-depth stuff is welcome, at least from me...

Well,

I changed the subform control source to a query and did as Craig instructed and it WORKS! THANK YOU CRAIG!

To answer some of your questions AJ,

The "due" column is used specifically in the command button cmdQC9. The button exports different fields on the form and subform to different fields within the excel spreadsheet it is set to.

Why? The excel speadsheet is a controlled form we must maintain to be compliant in our ISO-9000 standards.

The "role" it will play in the live computation of "on-hand" inventory is pretty basic. It will serve as a visual reminder when the end user is utilizing the form that a certain amount of a product ordered hasn't arrived yet and can also be useful in a report that will state what has been ordered but not yet received.

What I don't understand is how the query is updating the table.
I thought that was what an update query was for. This is a select query and yet, it is updating the table properly. Quite amazing, I wish I understood how.

If anyone is interested, I will post a copy of the database and of the two excel spreadsheets I use in exporting the information. I'll also go in and adjust the code so the spreadsheets will populate if they are saved to your desktop.

I'm very happy, this project is nearing completion. Thanks again for the help you two. I couldn't have done it without you. The knowledge you share helps people like me, who are new to access and vba and programming in general, learn and develop an understanding which will serve us for years to come.

Thanks again,

Marty

 

[ajetrumpet]

What I don't understand is how the query is updating the table.

Calculated columns in queries only execute when it is necessary for the query to execute. So any changes made in the tables that are being queried will make it look like the query is "updating" everytime it is opened (using the form).

I thought that was what an update query was for. This is a select query and yet, it is updating the table properly.

See above...

Thanks again for the help you two. The knowledge you share helps people like me, who are new to access and vba and programming in general, learn and develop an understanding

Craig did most of the work, I'm just an observer...

 

[mdschuetz]

Adam,

The subform's control source is set to a select query.
I am able to enter information into the subform.
The subform saves the information entered to the table I originally had the control source set to.

This is expected and normal?

I thought a select query could only display information.
I thought an update query was used to update tables.

Obviously, it isn't as black and white as I thought.

I still don't understand how.

 

[ajetrumpet]

The subform's control source is set to a select query.
I am able to enter information into the subform.
The subform saves the information entered to the table I originally had the control source set to.

I have said this a million times (although not in this thread, but many others), but I'll say it again...

Updates made to tables is a two way street. Data changes in queries transfer to the source tables. A query's data from any and/or all tables, is still, in all reality, the table's data. Thus, any information you change in the query will be changed in the table. How could you change information in a query if it is not a source of any kind in its own right? Tables are the guts of database programs!

I thought a select query could only display information.
I thought an update query was used to update tables.

They are, but your thinking is a bit too strict. It's not heavily regulated. Access has "pliable rules" that guide the program, not "government issued statutes"

 

[Banana]

The subform's control source is set to a select query.
I am able to enter information into the subform.
The subform saves the information entered to the table I originally had the control source set to.

This is expected and normal?

I thought a select query could only display information.
I thought an update query was used to update tables.

Obviously, it isn't as black and white as I thought.

I still don't understand how.

If you can pry open the internals of Access (or if you are using a ODBC backend, read the SQL output log), you'd see that Access executes much more than you saw.

It indeed does select a recordset from tables to display in a form but when you modify values and save the changes, Access issues an update query internally without you seeing it. Ditto for inserting a new record or deleting a record.

 

[Rabbie]

A Select query is just a way at looking at some or all of the data in one or more tables. If you change this data then the change will be reflected back into the tables where Access is able to identify the correct record. Not all Select Queries are updateable. This link gives information on when a (Select) Query is read only.

 

[CraigDolphin]

I was about to make the same point Rabbie To paraphrase: As long as the db can determine which specific record in the queried table to change, the query will be updateable. If there's any kind of ambiguity about which record in which table would need to be changed because of a user edit, then the query won't be updateable.

And I'm glad you got your form doing what you wanted MD

 

[The_Doc_Man]

The trick is that a select query provides a recordset. There is a default method of opening that recordset. SELECT queries opened in table mode or dynaset mode are often still updateable. (Note updateABLE vs. updated.) You are confusing queries that do UPDATE on their own vs. queries that give you a recordset through which YOU can manually do an update.

I doubt you would ever see the table if you open an UPDATE query. Opening an action query RUNS that action query.

 

[ajetrumpet]

Opening an action query RUNS that action query.

That's about the best short wind explanation I've ever heard!!