Working with Recordsets...

[mdschuetz]

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?

 

[boblarson]

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.

 

[CraigDolphin]

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.

 

[The_Doc_Man]

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.

 

[mdschuetz]

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

 

[mdschuetz]

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?

 

[mdschuetz]

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

 

[CraigDolphin]

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.

 

[mdschuetz]

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?

 

[CraigDolphin]

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

 

[mdschuetz]

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.

 

[ajetrumpet]

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

 

[CraigDolphin]

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

 

[mdschuetz]

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.

 

[mdschuetz]

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.

 

[CraigDolphin]

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.

 

[ajetrumpet]

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

 

[ajetrumpet]

Here is the 2000 version Craig. I converted it.

 

[CraigDolphin]

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 Then it froze up Access like a frog at the north pole.

 

[ajetrumpet]

Try it now...

Frogs freeze at the north pole??