Update Quantities from one table to another 1

[rockiwood]

I want to update Quantities from one Table (Issues or Receives) to another Table (Inventory). I am using a seperate Form for each Table.

Thanks in advance for any help

Jerry

 

[scriverb]

Do you want to do them all at once or on an individual basis when a particular record is the active record on your form? If you just want to do this all at once these queries will work.

Updates to Inventory quantity to reflect additions to inventory:
UPDATE INVENTORY INNER JOIN RECEIVES ON INVENTORY.InvItemNumb = RECEIVES.InvItemNumb
SET INVENTORY.QUANTITY = INVENTORY.QUANTITY + RECEIVES.QUANTITY;

Updates to Inventory quantity to reflect subtractions from inventory:
UPDATE INVENTORY INNER JOIN ISSUES ON INVENTORY.InvItemNumb = ISSUES.InvItemNumb
SET INVENTORY.QUANTITY = INVENTORY.QUANTITY - ISSUES.QUANTITY;

If this is not what you are looking for please get back with more information.

Bob Scriver

 

[rockiwood]

Thanks for the quick reply. I guess I was little too brief. I am building an Inventory Database and would like to have a Receive Form and an Issue Form to be able to track the Issues and Receives in seperate tables and report on them.
What I would like to do is when an item is recieved(or Issued), I will have a record of that Receive (or Issue) in it's own table and would like to update the Main Inventory Table at the time of Receipt (or Issue)

Thanks again for the reply.

jerry

 

[scriverb]

On the Issue and Receive forms you can have a POST button or in your Save Record operation on that form put the following code in OnClick event procedure to update the Inventory table:

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("INVENTORY", dbOpenDynaset)
rs.findfirst "[ID] = " & ME![InvItemNumb]
If Not rs.NoMatch then
rs.edit
rs("Quantity&quot = rs("Quantity&quot + me![QtnRecieved]
rs("Quantity&quot = rs("Quantity&quot - me![QtnIssued]
rs.update
else
MsgBox "No match on Inventory Item Number."
me![InvItemNumb].setfocus
end if
rs.close
db.close

The Red line is for the Received form and the Green line is for the Issued form. Now what you have to guard against is double-posting. Saving the record once and then the user being able to bring the record back up and post it again. This can be done by making your forms Add only. No editing. If an adjustment has to be made then that should be another transaction type JV or InvAdj with a reference to this particular posting.

This should give you the idea on how to do the first part and if you need more help with the other just get back with us.

Bob Scriver

 

[rockiwood]

WOW! What great help. Thanks a million.

Jerry

 

[scriverb]

Anytime, Jerry.

Bob

 

[rockiwood]

The following is the code I am trying to use to update Quantities using a Command button. I get an error of : Compile error. User defined type not defined and the Private sub line is highlighted in Yellow and the Dim db as database is highlighted in Blue.
I am using Access 2000 and noticed that rs.FindFirst is not available. Only Find is available.
The tblINventory is the table that I want to update using the Issue Form and command button on this Form.
I have not mastered the recordset yet and get confused when trying to work with it.
Thanks again for all the help! Jerry


Private Sub cmdIssueNow_Click()
Dim db As database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.openrecordset("tblInventory", dbOpenDynaset)
rs.Find ("[fldPN] = " & Me![fldIssuePN])
If Not rs.NoMatch Then
rs.EditMode
rs("fldQty&quot = rs("fldqty&quot - Me![fldQtyIssued]
rs.Update
Else
MsgBox "No Match on PN."
Me![fldIssuePN].SetFocus
End If

 

[scriverb]

I have not yet much experience with ACCESS 2k, but I have see postings where you have to make your declarations different if you want to use DAO code which this is as it is written in ACCESS 97. If you make the Red indicated changes I believe that the other code will work as ACCESS 2000 will treat the code here as DAO which includes the .FindFirst command. Give this a start and see what happens.

Private Sub cmdIssueNow_Click()
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.openrecordset("tblInventory", dbOpenDynaset)
rs.FindFirst ("[fldPN] = " & Me![fldIssuePN])
If Not rs.NoMatch Then
rs.Edit Mode !!Remove
rs("fldQty&quot = rs("fldqty&quot - Me![fldQtyIssued]
rs.Update
Else
MsgBox "No Match on PN."
Me![fldIssuePN].SetFocus
End If
End Sub

Get back with me with the results.

Bob Scriver

 

[rockiwood]

Bob,
I end up with the same results. I may just put this in Access 97 if it will be easier.

Thanks again for your help.

Jerry

 

[btaber]

You also need to make sure that you have the Microsoft DAO 3.6 Object Library enabled in your references before you can use DAO

 

[rockiwood]

How do I enable DAO?

Thanks, Jerry

 

[scriverb]

Open up any code module or sub in your database. Open up the code identified above and from the Menu bar select Tools\References and from the scroll pick list make sure the Microsoft Microsoft DAO 3.6 Object Library is checked.

That should allow you to use DAO code from within ACCESS 2000.

Bob Scriver

 

[mackers2]

There is a way of using DAO on the current database without setting the references either manually or by code. All you do is miss out the

Dim db As database

line and just use

Set db = CurrentDb

which opens a hidden reference to DAO. There is a little obscure reference in the help files somewhere to this.

I've found this useful when copying databases that have modules using DAO , as otherwise I think I would have had to either set the reference by opening up a module and doing it by hand or by using some code which means I'd have to know all the dll names.

I don't think you can use this to use DAO on other databases though, it has to be the current one.

 

[rockiwood]

The saga continues. I only get to work on this on the weekends.
I am now using Access 97. The Program is progressing nicely. But I am still having issues with updating the quantities.

I am using the this code:

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("INVENTORY", dbOpenDynaset)
rs.findfirst "[ID] = " & ME![InvItemNumb]
If Not rs.NoMatch then
rs.edit
rs("Quantity&quot = rs("Quantity&quot + me![QtnRecieved]
rs("Quantity&quot = rs("Quantity&quot - me![QtnIssued]
rs.update
else
MsgBox "No match on Inventory Item Number."
me![InvItemNumb].setfocus
end if
rs.close
db.close

The problem line now is:
rs.findfirst "[tblInventoryID] = " & ME![tblIssueInvItemNumb]

I get a Method or Data Member Not Found error.
As I am typing in the code and get to the rs.FIndFirst and rs.NoMatch, the Findfirst and NoMatch does not come up in the autolist. Being somewhat new to Access, I'm not sure what this means.

Thanks again for your help.......Jerry

 

[scriverb]

The code I provided is what is called Air code. Since I don't know all of the field names of your data I had to substitute field names and you are then to fit it to your situation. The line of code that you are referring to:

rs.findfirst "[tblInventoryID] = " & ME!tblIssueInvItemNumb]

The code is searching your Table or Recordset in this situation for the record that you want to update. In your case the INVENTORY table. There is a field that is usually indexed that can be searched to find the unique record that you want to perform some editing activity on. In this case the Inventory Item. Do you have a field called something like IventoryCode or Inventory# or such. Well that field should be subsitututed for the reference to tblInventoryID which is inside the first set of brackets.(red] Now the second area that needs to be changed is the right hand side of the equals sign. The ME![tblIssueInvIteNumb] should be the FORM text box that holds the Inventory Code/@/ID value that you are going to match to before you update the record with the transaction. Whatever you call it on your form that name should be substituted here.

Now if you do that correctly this code should work for you. In the event that this does not work and the error stops on this same line it may be that your database field Inventory# is not numeric but rather AlphaNumeric. If so, then a little big different syntax needs to be used to make the comparison on this line successfully.

Let's wait to see if this works before we tackle that one.

Bob Scriver

 

[rockiwood]

Bob,

Here is the exact code for that Statement:
rs.FindFirst [fldPartNumber] = Me![fldIPartNumber]

fldPartNumber is in the Inventory Table that I am trying to update.

fldIPartNumber(please notice the I ) is the Issued Part Number from the Issues form that I am trying to match.

These fields are text.

When i attempt to use quotes around "[fldPartNumber] = " &, it gives me an error of Expected end of Statement.

Thanks for your persistence

Jerry

 

[scriverb]

Use the following in your code:

rs.FindFirst "[fldPartNumber] = '" & Me![fldIPartNumber] & "'"

Because the field in your table is a text(string) field then the expression has to compare using quotes around the value of the form textbox. You see after the rs.FindFirst part of the statement you are building the selection criteria string that the command is going to use to find the record. To do this you have to use "(double-quotes) in the process of building thi expression. To include quotes acuatually in the expression you have to put double-quotes around a single-quote. ACCESS ends up making the comparison like this rs("fieldname&quot = 'InvIDValue'. That is the reason for the single quotes. After it is all put together the criteria statement looks like this if the form text box value is equal to let's say 0245A for an inventory item.

rs.FindFirst [fldPartNumber] = '0245A'

So, copy and paste the first blue line and that should work for you.

Bob Scriver

 

[rockiwood]

I will not give up

Still no worky. I always get confused with the use of the sngle and double quotations.
It now stops on the first & in the rs.FindFirst Statement

Here is the code in full:

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.openrecordset("tblInventory", dbOpenDynaset)
rs.FindFirst "[fldPartNumber] = '" & Me![fldIPartNumber] & "'"
If Not rs.NoMatch Then
rs.Edit
rs("fldQty&quot = rs("fldqty&quot - Me![fldQtyIssued]
rs.Update
DoCmd.GoToRecord , , acNewRec

Else
MsgBox "No Match on Part Number."
Me![fldIPartNumber].SetFocus
End If
rs.Close
db.Close

I appreciate your patience . ......Jerry

 

[rockiwood]

Bob,
I just found that the setfocus method is not working on this form, but it is working on another form in this same .mdb. Could this be part of my problem and if so, what could be the cause? Could it have something to do with converting it from 2000 back to 97?
It's not fun being a newbie

I am about frustrated

Jerry

 

[LLudden]

set a breakpoint at that line, and do debug.print Me![fldIPartNumber]

Make sure that resolves to the correct value. Other than that, everything looks ok.