Calculating in a form

[jksb]

I have this in AfterUpdate:

Private Sub EndInventory_AfterUpdate()

EndInventory As Variant

varEndInventory = ("[StartInventory] + [AmtRecd] - [AmtIssued]&quot

End Sub


Obviously, it's not working . I tried to tell it to go to "InventoryTransactions" table but it kept kicking me out "compile error". Do I need to do a query, and if so, how do I get it to run in a form? Or can I have a modified version of this to get it to run?

Thanks!!!

Jackie

 

[TheInsider]

Hi,
Firstly, it looks like you are trying to write a function in a procedure. Second, you are declaring "EndInventory" as a varient - fine - but when you assign it a value you use "varEndInventory". Try this:

Private Function EndInventory_AfterUpdate() As Variant

EndInventory = ("[StartInventory] + [AmtRecd] - [AmtIssued]&quot

End Function

Also, avoid the use of varients - they are slower to calculate on and they take more memory. Just a tip =).

Rob Marriott
rob@career-connections.net

 

[TheInsider]

Hi,
Sorry, I realize now that this is a control's After Update procedure, so you can't create the above function. However, you should be able to see the problem now.

Rob Marriott
rob@career-connections.net

 

[jksb]

Okay, I am being dense today..guess it's Monday after the holiday. When I did a keyword search, someone did tell someone else to do this..put the function in the procedure, which is why I did it. So, you are saying I cannot do this?
Do I run it as a query then? Again, how would I tell it to get the query...AfterUpdate only gives me Event (unless I am supposed to type "run Query" in the module). I am really new to Access, sorry for the simple questions.

Thanks again!

Jackie

 

[TheInsider]

Hi,
"StartInventory", "AmtRecd", and "AmtIssued" - are these A) fields on the form, B) fields in the forms recordsource, or C) none of the above? If A or B, then you don't need a VBA procedure, you simply need to use place this in the field source property of EndInventory:

=[StartInventory] + [AmtRecd] - [AmtIssued]

Note: You might want to use the expression builder.

If your answer was C, then you probly need to open a recordset. I don't think that your answer is C though =).

Rob Marriott
rob@career-connections.net

 

[jksb]

The answer is B and it worked! Thanks!

Now to get a bit more complicated. I have two tables - Inventory Description, which has description, price, other normally nonchanging data, and Inventory Transactions, which I want to use to store any changes (i.e., AmtIssued, AmtRecd, to whom). The Transaction No. is the AutoNo. Common field is CharterNo (foreign key in Transactions).

Now, I made two subforms for these tables. Only the first...Inventory Description, has CharterNo. What I would like is when someone puts in the CharterNo. for changes, the changes for that number be stored in InventoryTransactions table. What is the easiest way to link the two subforms (I assume by the common field, but I don't want it to show on the second subform)?

Also, for StartInventory...how do I get that to constantly update? Do I tell it to get that info from EndInventory using BeforeUpdate?


I hope this makes sense!

Thanks! Your help is really appreciated!

Jackie