The next part of this project is pretty technical.

I have a question…

Purchase Orders Subform
Date Job Part Description Price PO GPC Requested Received Due Rejected Invoice Number
11/9/2007 222 2252 bolts $2.25 27 CAM 5 3 2 0 MOSL223224
11/9/2007 222 2452 nuts $1.85 27 CAM 10 4 6 0 MOSL223224
11/9/2007 222 2241 washers $0.96 27 CAM 20 12 8 0 MOSL223224
11/9/2007 222 2216 springs $0.52 27 CAM 50 20 30 0 MOSL223224
11/9/2007 222 2171 sprockets $1.95 27 CAM 79 59 20 0 MOSL223224
11/14/2007 222 2252 bolts $2.25 27 CAM 5 1 4 0 MOSL223555


Each row above is a Recordset?

The goal is to get the DUE field to update as items are received.

For instance, the first row and the last row, they each are for part number 2252 but have different Dates and Invoice numbers. However, they are from the same job (Job# 222), so I want the updated amount due to populate in the most recent recordset.

I think the way to start is to define the variables which will be used in the If statement to accomplish this.

Dim vJob As Integer
Dim vPart As String
Dim vRec As Integer
Dim vDue As Integer
Dim vDate As Date
Dim vOrdered as Integer

vOrdered = Me.txtUnitsOrdered
vJob = Me.txtJob
vPart = Me.txtPart
vRec = Me.txtUnitsReceived
vDue = Me.txtUD
vDate = Me.txtDate

Now comes the logic,

But before I can start to put it together, I need to know how to uniquely identify each recordset. If I knew how to do this, I could code an If statement to update the Due field in the most recent recordset based on Recordset(1)vDate < Recordset(2)vDate.

Something like….

If Recordset(1) vJob And vPart = Recordset(2) vJob And vPart Then
Recordset(2) vDue = Recordset(1) vOrdered – Recordset(1) vRec + Recordset(2) vRec

End If

Does that make any sense?
I know its probally not even close on how to do it, but then again, I don’t know how to work with individual recordsets.
Can someone please show me how?

Okay, here's a little info and maybe you can get it from there.

A recordset is a collection of records (hint rows, plural, of records, not just one row). A recordset CAN be one row but it isn't usually. Usually you are pulling a set of records (a record being a row made up of one or more columns, also known as fields).

So what you are trying to do is to find the RECORD (not recordset) that matches the criteria you are looking for. This can be done with a query, and an update can be done with an update query.

And, if you're looking for a little bit of an introduction to using recordsets then maybe this link http://www.devdos.com/vb/lesson4.shtml will be of interest. I found it helpful when I was first starting out with recordsets. Just remember that a recordset can be a table or a query.

You have a VBN problem here. That's VERY BIG NORMALIZATION problem.

Very rarely would you EVER do it this way, if those were raw records you showed us.

The way to do this "right" is that amount due would be the difference between amount ordered and amount received. Each of the latter amounts would be sums of individual orders and individual received shipments. And amount on hand would be formed by summing the amount sold, subtracting from the amount received. Again, where the individual items are sums.

Now...how do you make them behave? Look up UNION queries as a way to form a query on what APPEAR to be disjoint recordsets.

Search this forum for INVENTORY and STOCK CONTROL to see lengthy discussions of how to do this type of tracking. You would NEVER EVER see something like what you showed us involving an update of a due amount in an elementary record. But it is easy if you use a normalized table or two. Or six.

You have a VBN problem here. That's VERY BIG NORMALIZATION problem.

Very rarely would you EVER do it this way, if those were raw records you showed us.

The way to do this "right" is that amount due would be the difference between amount ordered and amount received. Each of the latter amounts would be sums of individual orders and individual received shipments. And amount on hand would be formed by summing the amount sold, subtracting from the amount received. Again, where the individual items are sums.

Now...how do you make them behave? Look up UNION queries as a way to form a query on what APPEAR to be disjoint recordsets.

Search this forum for INVENTORY and STOCK CONTROL to see lengthy discussions of how to do this type of tracking. You would NEVER EVER see something like what you showed us involving an update of a due amount in an elementary record. But it is easy if you use a normalized table or two. Or six.

=[UnitsOrdered]-[UnitsReceived] is how I keep track of units due.

You think I have a normalization problem? That is one of my biggest fears, that I didn't set this up properly to begin with. I asked about it at one point and didn't receive any negative feedback on table structure.

I am reading and learning about records and recordsets now and soon I'll look into the queries I was told about. Thanks again for pointing me in the right direction guys.

Marty

Alright,

I now have a better understanding of what queries can be used for. I don't believe a union query is necessary because one table holds all of the fields I need to work with.

I may need a query to hold the values of each record since txtUD (Units Due) is not stored in an actual table but is calculated on the fly.

Would I then use something like DSum()?

I've been trying to write a query that sums UnitsRecieved sorted by PartNumber and Job Number but am not able to get it to work.

Am I off track or is this what I should be learning about?

Date Job Part Description Price PO GPC Requested Received Due Rejected Invoice Number
11/9/2007 222 2252 bolts $2.25 27 CAM 5 3 2 0 MOSL223224
11/9/2007 222 2452 nuts $1.85 27 CAM 10 4 6 0 MOSL223224
11/9/2007 222 2241 washers $0.96 27 CAM 20 12 8 0 MOSL223224
11/9/2007 222 2216 springs $0.52 27 CAM 50 20 30 0 MOSL223224
11/9/2007 222 2171 sprockets $1.95 27 CAM 79 59 20 0 MOSL223224
11/14/2007 222 2252 bolts $2.25 27 CAM 1 4 0 MOSL223555


To better clarify what the goal is I will try to explain a bit better.

The top row and the bottom row in the above example are the same part number. They share the same job number. They have 2 different transaction dates and 2 different invoice numbers.

I am trying to get the Items Due field to update when a part is received with the same job number as the row in which it was ordered.

I am getting nowhere, I can create the query that sums up the amount received but I have no idea how to write an expression that compares each record against every other record and updates an unbound field based on if 2 fields (part number and job) match.

I've tried the expression builder and it returns a #Name? error.

There has to be a simple way to do it. If any of you know how can you please help me? I don't expect you to do it for me, but I would like to have it explained. I've been searching the forum all day and I've seen some similiar topics but nothing that compares the records the way I'm wanting to.

Its frustrating being as ignorant as I am.

:( Marty

I am trying to get the Items Due field to update when a part is received with the same job number as the row in which it was ordered.

Sounds something like:

UPDATE table_name SET Due = your_new_value
WHERE ((([Part])=[your_received_part_number]) AND (([Job])=[your_received_job_number]));

You'll need to tell the query what the relevant part and job numbers are, as well as whatever value you want to update the Due field to. You could supply these as parameters or in controls on form. If the value can be calculated from other information in the db then you'd need to supply the relevant expression instead.

I'm still unable to solve this.

I keep getting a #Name? or #Error display in my control when I run the form.

in the control txtUD I've set the following under the BeforeUpdate event

Dim intUD as Integer
Dim strPart as String
Dim strJob as String

intUD = DSum("[Inventory Transactions]![UnitsReceived]","Inventory Transactions","[Inventory Transactions]![PartNumber] = " & strPart)
strPart = Me.txtPart
strJob = Me.txtJob

UPDATE Inventory Transactions SET txtUD = intUD
WHERE ((([Inventory Transactions]![PartNumber]) = strPart AND (([Inventory Transactions]![Job]) = strJob));

What I don't understand is how is an update query going to update a field on a form that isn't bound to a table or query? It just doesn't make sense, I was hoping that DSum would work, but it doesn't do it either.

Any suggestions?

Leaving aside the issues with your code, your form is not bound to any tables?

And all you want to do is change the value in an unbound textbox to sum up quantities in a certain field of a table where a part number field in that table = (value in a combo on your form) and a job number field in that table = (a value in another combo on your form)?

If so, then you are probably right....all you need is a DSum statement triggered from some event(s) on your form.

Let's say your part combo is called cmboPart and your job combo is called cmboJob
Each combo stores the pk from the relevant tables. You want the result to show up in an unbound textbox called txtMyResult.

You have a query (qryPartsReceived) or table that has these three fields: Part, Job, and UnitsReceived.

you could use the after_update event of your cmboJob control like

Private Sub cmboJob_AfterUpdate()
Me.txtResult = DSum("[UnitsReceived]","qryPartsReceived","[Job]=" & Nz(Me.cmboJob,0) & " AND [Part]=" & Nz(Me.cmboPart,0))
End sub

You could put the same code in the after update event of your cmboPart too.

This assumes that the UnitsReceived, Job, and Part fields are numeric datatypes (not TEXT).

Yes, the Form is bound to a table named "Purchase Orders".
The subform(datasheet view) I am working from is bound to a table named "Inventory Transactions".

And all you want to do is change the value in an unbound textbox to sum up quantities in a certain field of a table where a part number field in that table = (value in a combo on your form) and a job number field in that table = (a value in another combo on your form)?

Not Quite, but close.

I have a textbox, it's name is "txtUD".

That is a textbox for Units Due.

txtUD Needs to Sum from two fields in the the table "Inventory Transactions"

These two fields are named...and should calculate

UnitsOrdered -(minus)- UnitsReceived

They should only Sum from these fields if two more fields in table Inventory Transaction match what is displayed in the record on the datasheet.

These two fields are named...

PartNumber and Job

..........................

So I've tried adding the following to the AfterUpdate() event in txtUD.

Private Sub txtUD_AfterUpdate()
Me.txtUD = DSum("[UnitsReceived]","Inventory Transactions","[Job]=" & Nz(Me.txtJob,0) & " AND [PartNumber]=" & Nz(Me.txtPart,0))
End sub

I no longer get a #Name? error, but nothing is displayed in the control. It's just blank.

Also, I checked that the UnitsReceived, Job, and PartNumber fields are numeric datatypes (not TEXT) and they are.

Maybe I can offer some useful advice here Md...Yes, the Form is bound to a table named "Purchase Orders".
The subform(datasheet view) I am working from is bound to a table named "Inventory Transactions".

I have a textbox, it's name is "txtUD".

That is a textbox for Units Due.

txtUD Needs to Sum from two fields in the the table "Inventory Transactions"

These two fields are named...and should calculate

UnitsOrdered -(minus)- UnitsReceived

They should only Sum from these fields if two more fields in table Inventory Transaction match what is displayed in the record on the datasheet.The way I read it, what you basically said in red is that, the condition upon which to sum the txtbox is weather or not the Inventory Transactions DS has more than two records in it that hold the parent record's partID or InvoiceID (I don't know which one though).

I may also say, that for anyone trying to help here, a file, or picture, or anything visual would be greatly appreciated...

...on a form that isn't bound to a table or query...

the Form is bound to a table named "Purchase Orders".

This story keeps changing so fast my head is spinning.

So, what you really want is to display the result of a calculation in an unbound textbox called txtUD which is on a form bound to a table that contains the fields PartNumber and Job.

The calculation is (for all records in the Inventory Transactions table with the same PartNumber and Job number) to sum up the field UnitsOrdered and to subtract from that value the sum of the field UnitsReceived.

Then, in the on_current event of the form, and in the after update events of the txtPart and txtJob controls on that form, you need to put the following code. (NOT the after update event of the txtUD control.)

Me.txtUD = DSum("[UnitsOrdered]","[Inventory Transactions]","[Job]=" & Nz(Me.txtJob,0) & " AND [PartNumber]=" & Nz(Me.txtPart,0)) - DSum("[UnitsReceived]","[Inventory Transactions]","[Job]=" & Nz(Me.txtJob,0) & " AND [PartNumber]=" & Nz(Me.txtPart,0))

Craig,

Thanks for the help. The code works if I select a record on the subform manually with the mouse. The funny thing is, it sets every due field to whatever value the record that has focus holds.

Is it possible to have each record update individually and retain its value when the focus isn't on that individual record?

Here is a copy of the database as small as I can make it.

For some reason the subform only "sees" one record at a time. Each field in txtUD is being populated with whatever record has focus.

Me.txtUD = DSum("[UnitsOrdered]", "[Inventory Transactions]", "[Job]=" & Nz(Me.txtJob, 0) & " AND [PartNumber]=" & Nz(Me.txtPart, 0) & " AND [TransactionID]<=" & Nz(Me.txtID, 0)) - DSum("[UnitsReceived]", "[Inventory Transactions]", "[Job]=" & Nz(Me.txtJob, 0) & " AND [PartNumber]=" & Nz(Me.txtPart, 0) & " AND [TransactionID]<=" & Nz(Me.txtID, 0))

The code above correctly updates the txtUD field, but how can I have each records txtUD field show the correct value without selecting it specifically?

The funny thing is that each record shows txtUD's value as the value in the record that has focus.

However,

I've also noticed that the control source in the data tab uses a different methodology, if I set an expression to calculate UnitsOrdered - UnitsReceived each record populates properly.

I think this is happening because Me.txtUD isn't unique to each record. Since this is a form wide code and not unique to the individual record it can only return the value that it has focus on?

Any ideas on how to correct this? I was thinking maybe somekind of Loop, I'm just grasping at straws here.

:(

Hmm. I only have Access 2000 and I'm guessing you're using 2003 or later? In any case, could not open your example.

I didn't twig that you were doing this on a subform in continuous form view. I had thought you were doing this bit on the main form in single form view.

The reason for the issue you have is that an unbound control shown on a continuous form is actually only one control shown many times. The only way around this is to bind the control's control source.

One way would be to base your form on a query of the table you are currently using as your record source. Then, add an additional field to that query with something like

MyCalc: DSum("[UnitsOrdered]", "[Inventory Transactions]", "[Job]=" & Nz([Job], 0) & " AND [PartNumber]=" & Nz([PartNumber], 0) & " AND [TransactionID]<=" & Nz([TransactionID], 0)) - DSum("[UnitsReceived]", "[Inventory Transactions]", "[Job]=" & Nz([Job], 0) & " AND [PartNumber]=" & Nz([PartNumber], 0) & " AND [TransactionID]<=" & Nz([TransactionID], 0))

...in the field row for that new field.

Then set the control source for txtUD to MyCalc.

how can I have each records txtUD field show the correct value without selecting it specifically?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.The funny thing is that each record shows txtUD's value as the value in the record that has focus.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... :)

Here is the 2000 version Craig. I converted it.

Hmm. Thanks Adam but I get a weird error when I try to open it.

Something about being unable to create the database window and needing to upgrade to a newer version of internet explorer :eek: Then it froze up Access like a frog at the north pole.

Try it now...

Frogs freeze at the north pole??

Same problem.

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

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

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 understandingCraig did most of the work, I'm just an observer...

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.

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" ;)

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.

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 (http://www.allenbrowne.com/ser-61.html)gives information on when a (Select) Query is read only.

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 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.

Opening an action query RUNS that action query.That's about the best short wind explanation I've ever heard!! :)