Transfer an expression calculated value to my table

[Guest]

I am using Access 2000
I Have a Products Table with a field named QOH
On my form I have a txtbox named "QOH" which is bound to my Products table and
another named "UnitsOnHand".
txtUnitsOnHand has an expression to add all products recieved from my
purchase Orders
in my subform. The expression is as follows:

=[Products Subform].Form!UnitsOnHand

what I need is a command button or something so when I load the form and
update my
recieved products in the subform "UnitsRecieved" I can click it and add
recieved products
to QOH in my table Products. Then everything ealse will work fine and keep
track of my
inventory and let me know if I have enough child products to build my Parent
Products.
I have test driven it by manually putting in this value and all is well.
I have tried an append and an update query. The update query was close but
it replaces any info with the latest vaues. I need all values to keep my
Inventory correct. I'm new to acces and any help is greatly appreciated.
Thank you so much in advance.
Alvin

 

[Wayne Morgan]

First, it is not normally necessary to store data that can be calculated and
it isn't recommended except for in a very few cases. Just do the calculation
when you need the data. This calculation may be able to be done in a query,
form, or report.

Next, their are ways to store calculated data. The Update Query that you
tried would be one way. You just need to make sure that you put enough
restrictions into the WHERE clause of the Update Query to limit the query to
updating only the desired record. The unique ID field of the record would do
this. You can also use a hidden textbox on the form that is bound to the
field you are storing this value in. In the BeforeUpdate event of the form,
you would take the value from the calculated textbox and place it in the
hidden textbox. The form will then store this value when it saves the
record. However, for this to work, something has to make the form "dirty" to
start with. This can be done by changing the value in another field or by
programmatically setting the form's Dirty value to True.

The problem with using a button to do this is that if you forget to click
the button, you're data that is stored is now wrong. This is something that
needs to be fully automated if you're going to do it.

 

[Guest]

Thank you Wayne for the response. I understand what your saying and I totally
agree with you but I think this is one of those rare cases. I am very new to
Access
In my Assembly creation it only allows me to post an assembly only if I have
enough child products. My Inventory Transaction table keeps track of quanity
used, Ordered and on order By posting the QOH in my Products table is the
only way I know of doing this. It is a small database right now if you want
to look at it.
My email is
(e-mail address removed)
I am totaly lost on this one and
Like I said I don't know what ealse to do.
Thank you again so much for your response

First, it is not normally necessary to store data that can be calculated and
it isn't recommended except for in a very few cases. Just do the calculation
when you need the data. This calculation may be able to be done in a query,
form, or report.

Next, their are ways to store calculated data. The Update Query that you
tried would be one way. You just need to make sure that you put enough
restrictions into the WHERE clause of the Update Query to limit the query to
updating only the desired record. The unique ID field of the record would do
this. You can also use a hidden textbox on the form that is bound to the
field you are storing this value in. In the BeforeUpdate event of the form,
you would take the value from the calculated textbox and place it in the
hidden textbox. The form will then store this value when it saves the
record. However, for this to work, something has to make the form "dirty" to
start with. This can be done by changing the value in another field or by
programmatically setting the form's Dirty value to True.

The problem with using a button to do this is that if you forget to click
the button, you're data that is stored is now wrong. This is something that
needs to be fully automated if you're going to do it.

--
Wayne Morgan
MS Access MVP

I am using Access 2000
I Have a Products Table with a field named QOH
On my form I have a txtbox named "QOH" which is bound to my Products table
and
another named "UnitsOnHand".
txtUnitsOnHand has an expression to add all products recieved from my
purchase Orders
in my subform. The expression is as follows:

=[Products Subform].Form!UnitsOnHand

what I need is a command button or something so when I load the form and
update my
recieved products in the subform "UnitsRecieved" I can click it and add
recieved products
to QOH in my table Products. Then everything ealse will work fine and keep
track of my
inventory and let me know if I have enough child products to build my
Parent
Products.
I have test driven it by manually putting in this value and all is well.
I have tried an append and an update query. The update query was close but
it replaces any info with the latest vaues. I need all values to keep my
Inventory correct. I'm new to acces and any help is greatly appreciated.
Thank you so much in advance.
Alvin

 

[Wayne Morgan]

Another option to check to see if you have quantity on-hand is to use DSum()
or DCount() with appropriate parameters to add up the number received and
the number sold then subtract the two to see what is on-hand. This could be
done as needed and would save keeping a running total in a table.

 

[Guest]

Thank you for your help Wayne,
I don't know that much about codeing. my assembly creation form is boud to
an assembly creations table. If I want to build say 20 products and my "QOH"
which is bound to my "Assembly creation" Table doesn't have enough child
products it won't let me post that transaction. I have been trying Query's,
Update Query's, Append Query's with Dsum, Dlookup and I can't get the proper
structure in any of them. If I Manually type the QOH in the form where I edit
quanity received for my child products all else works great.
I do have a txtbox on my child parts form that calculates Quanity On Hand.
but I can't seem to get the txtQOH in my assembly creations form to recognize
that value.
I am totaly lost and have no Idea of what else to do.

 

[Wayne Morgan]

Is the Child Parts form open when you try to get your Assembly Creations
form to recognize the value from it? If so, where is the Child Parts form,
is it a subform of Assembly Creations or a different form? If it is a
different form, is it a subform of the other form?

 

[Guest]

Thank you Wayne for trying to help.
It's obviouse you really wan't to help and want it done correctly also. I
can appreciate this. But anyhow
I Didn't know how to explain my structure so you can understand it, LOL so
I uploaded it to the Internet.
The address is.

http://www.pittmangamecalls.com/Product_Assembly.mdb

The following is where I assign my child parts to my Parent
"Enter/View Assembly Details"
"Assembly Detail"> Bound to my Products
"Kits" >Subform for "Assembly Detail" it list all the
child parts
If you download it you will see what I've been talking about.

when you click on "Enter/View Order childparts"
you will see the "Units Ordered", "units Received" and "Units Used" in the
subform
and on that main Form you will see txtbox "QOH"
If I manually type in units Received in that text box "QOH" all works fine.
But, If I don't the
Assembly Creations doesn't recognize that value and I can post an assembly
even though I don't have the parts.
Example:
Click on assembly creations then in txtbox "Quanity to make" Type in 1 and
post Transaction will enable but don't click it yet click Exit for now.
Now click on Enter/View & Order Child Parts
In there you will see where I have
Units ordered = 75
Units Received= 75
Units Used = 0
QOH = Blank
Now, If you click in "QOH" on that form and enter the amount Received from
the subform which is "75" for first product.
Scroll through the first 4 products and add the amount received to QOH. Now
go to the main Switchboard and click on
"Assembly Creations" and you will see QOH in the subform = 75 for each that
you listed from "Enter/View Order child Products"
If you try to make say 200 of that product it won't let you because there
isn't enough childparts and it recognizes QOH.
But if you erase the QOH from Enter/View Order Child Parts then go back QOH
shows "0" you will be able to Post a transaction
and you will see that value in Inventory Transactions Table.
I have a Backup copy so don't worry about anything you do to this one.
Thanks again and sorry but I didn't really know how to explain it to you
without you looking at it.

It got a little confusing but I hope I explained all well enough.
Thanks again Wayne.
Alvin

 

[Wayne Morgan]

You're right, it's confusing. If I follow the steps you mention, step by
step, the number you mention don't show.

 

[Guest]

Thank you for looking at it Wayne,
I have almost got it. I have uploaded an update to the one you downloaded.
This one has a Query named "QOH Update". It almost does what I need. I
modified some of the VB code as well as adding the "QOH Update" Query. Please
check it out and you may be able to solve the issue. If you download it, go
ahead and delete all records in the "Inventory Transactions" and the
"Purchase Orders" Tables along with "QOH" values in the products Table. add
products and Quanities then creat a couple of assemblies and after that,
Before you Post an assembly click back into add/view Products and click on
"Update Inventory". You will see that QOH is correst. Problem with my Update
Query is that when you add more Quanities to the products AFTER posting an
assembly it is as if it doesn't read it. The values in my [Products.QOH] are
different than what the calculated values are on the Add/View Products form,
I Know the calculation on that form is correct. I believe the problem is in
my "QOH Update" Query look at it and you may have a solution. It Updates
where [Inventory Transactions.ProductID]=[Products.ProductID] The following
is my Dsum value.

DSum("[UnitsReceived]- [UnitsUsed]","Inventory Transactions","[Inventory
Transactions.ProductID]=" & [Products.ProductID])

I just got a gut fealing the problem lies in there somewhere.
Thanks again Wayne. By the way feel free to keep the program if you want to.
Thank you so much for your help!
Alvin

 

[Wayne Morgan]

I tried to download the new file, but get an error that it can't be found or
isn't available.

 

[Guest]

Sorry about that wayne,
I took it off so it isn't available to everyone.
I have reloaded it till you download it again.
I think I got it but you could still look at it and see if you know of a
better way. on the products form I have an Update Inventory Button and a
Refresh Button.
after any entry of received products the user will click on update then
refresh. I would really like to remove the standard popup message saying you
are about to run an update and replace that with one of my own. But I don't
know how.
Thanks for your help so much and god bless.
Alvin

I tried to download the new file, but get an error that it can't be found or
isn't available.

--
Wayne Morgan
MS Access MVP

Thank you for looking at it Wayne,
I have almost got it. I have uploaded an update to the one you downloaded.
This one has a Query named "QOH Update". It almost does what I need. I
modified some of the VB code as well as adding the "QOH Update" Query.
Please
check it out and you may be able to solve the issue. If you download it,
go
ahead and delete all records in the "Inventory Transactions" and the
"Purchase Orders" Tables along with "QOH" values in the products Table.
add
products and Quanities then creat a couple of assemblies and after that,
Before you Post an assembly click back into add/view Products and click on
"Update Inventory". You will see that QOH is correst. Problem with my
Update
Query is that when you add more Quanities to the products AFTER posting an
assembly it is as if it doesn't read it. The values in my [Products.QOH]
are
different than what the calculated values are on the Add/View Products
form,
I Know the calculation on that form is correct. I believe the problem is
in
my "QOH Update" Query look at it and you may have a solution. It Updates
where [Inventory Transactions.ProductID]=[Products.ProductID] The
following
is my Dsum value.

DSum("[UnitsReceived]- [UnitsUsed]","Inventory Transactions","[Inventory
Transactions.ProductID]=" & [Products.ProductID])

I just got a gut fealing the problem lies in there somewhere.
Thanks again Wayne. By the way feel free to keep the program if you want
to.

 

[Wayne Morgan]

Hi, glad you got it working. For the messages,

Change
stDocName = "QOH Update"
DoCmd.OpenQuery stDocName, acNormal, acEdit

to

stDocName = "QOH Update"
CurrentDb.Execute stDocName, dbFailOnError

This will execute the update query without any prompting. To create your own
message:

Example:
If MsgBox("Do you really want to update?", vbYesNo+vbQuestion, "Update
Records") = vbYes Then
stDocName = "QOH Update"
CurrentDb.Execute stDocName, dbFailOnError
Else
'If you want to do this, you could have the user manually do this as
well.
Me.Undo
End If



--
Wayne Morgan
MS Access MVP

Sorry about that wayne,
I took it off so it isn't available to everyone.
I have reloaded it till you download it again.
I think I got it but you could still look at it and see if you know of a
better way. on the products form I have an Update Inventory Button and a
Refresh Button.
after any entry of received products the user will click on update then
refresh. I would really like to remove the standard popup message saying
you
are about to run an update and replace that with one of my own. But I
don't
know how.
Thanks for your help so much and god bless.
Alvin

I tried to download the new file, but get an error that it can't be found
or
isn't available.

--
Wayne Morgan
MS Access MVP

Thank you for looking at it Wayne,
I have almost got it. I have uploaded an update to the one you
downloaded.
This one has a Query named "QOH Update". It almost does what I need. I
modified some of the VB code as well as adding the "QOH Update" Query.
Please
check it out and you may be able to solve the issue. If you download
it,
go
ahead and delete all records in the "Inventory Transactions" and the
"Purchase Orders" Tables along with "QOH" values in the products Table.
add
products and Quanities then creat a couple of assemblies and after
that,
Before you Post an assembly click back into add/view Products and click
on
"Update Inventory". You will see that QOH is correst. Problem with my
Update
Query is that when you add more Quanities to the products AFTER posting
an
assembly it is as if it doesn't read it. The values in my
[Products.QOH]
are
different than what the calculated values are on the Add/View Products
form,
I Know the calculation on that form is correct. I believe the problem
is
in
my "QOH Update" Query look at it and you may have a solution. It
Updates
where [Inventory Transactions.ProductID]=[Products.ProductID] The
following
is my Dsum value.

DSum("[UnitsReceived]- [UnitsUsed]","Inventory
Transactions","[Inventory
Transactions.ProductID]=" & [Products.ProductID])

I just got a gut fealing the problem lies in there somewhere.
Thanks again Wayne. By the way feel free to keep the program if you
want
to.

 

[Guest]

Wayne,
I Just wanted to thank you for your support. It's folks like you, Douge
Arvin & Jeff, Just to mention a few of you, that make our efforts to learn
and be patient worth while!!!!!!!
Just a note. I have no VB Knowladge what so ever. Everything I know I have
learned by trial and error and most of it I learn from you guys.
Again Thanks a million!!
Alvin Smith

Hi, glad you got it working. For the messages,

Change
stDocName = "QOH Update"
DoCmd.OpenQuery stDocName, acNormal, acEdit

to

stDocName = "QOH Update"
CurrentDb.Execute stDocName, dbFailOnError

This will execute the update query without any prompting. To create your own
message:

Example:
If MsgBox("Do you really want to update?", vbYesNo+vbQuestion, "Update
Records") = vbYes Then
stDocName = "QOH Update"
CurrentDb.Execute stDocName, dbFailOnError
Else
'If you want to do this, you could have the user manually do this as
well.
Me.Undo
End If



--
Wayne Morgan
MS Access MVP

Sorry about that wayne,
I took it off so it isn't available to everyone.
I have reloaded it till you download it again.
I think I got it but you could still look at it and see if you know of a
better way. on the products form I have an Update Inventory Button and a
Refresh Button.
after any entry of received products the user will click on update then
refresh. I would really like to remove the standard popup message saying
you
are about to run an update and replace that with one of my own. But I
don't
know how.
Thanks for your help so much and god bless.
Alvin

I tried to download the new file, but get an error that it can't be found
or
isn't available.

--
Wayne Morgan
MS Access MVP


Thank you for looking at it Wayne,
I have almost got it. I have uploaded an update to the one you
downloaded.
This one has a Query named "QOH Update". It almost does what I need. I
modified some of the VB code as well as adding the "QOH Update" Query.
Please
check it out and you may be able to solve the issue. If you download
it,
go
ahead and delete all records in the "Inventory Transactions" and the
"Purchase Orders" Tables along with "QOH" values in the products Table.
add
products and Quanities then creat a couple of assemblies and after
that,
Before you Post an assembly click back into add/view Products and click
on
"Update Inventory". You will see that QOH is correst. Problem with my
Update
Query is that when you add more Quanities to the products AFTER posting
an
assembly it is as if it doesn't read it. The values in my
[Products.QOH]
are
different than what the calculated values are on the Add/View Products
form,
I Know the calculation on that form is correct. I believe the problem
is
in
my "QOH Update" Query look at it and you may have a solution. It
Updates
where [Inventory Transactions.ProductID]=[Products.ProductID] The
following
is my Dsum value.

DSum("[UnitsReceived]- [UnitsUsed]","Inventory
Transactions","[Inventory
Transactions.ProductID]=" & [Products.ProductID])

I just got a gut fealing the problem lies in there somewhere.
Thanks again Wayne. By the way feel free to keep the program if you
want
to.

 

[Guest]

I do have one Question,
What is Global Code and how do you code it and use it.
I am totaly lost and have no Idea if I even need it, I just seen it in a few
databases.
Alvin

Hi, glad you got it working. For the messages,

Change
stDocName = "QOH Update"
DoCmd.OpenQuery stDocName, acNormal, acEdit

to

stDocName = "QOH Update"
CurrentDb.Execute stDocName, dbFailOnError

This will execute the update query without any prompting. To create your own
message:

Example:
If MsgBox("Do you really want to update?", vbYesNo+vbQuestion, "Update
Records") = vbYes Then
stDocName = "QOH Update"
CurrentDb.Execute stDocName, dbFailOnError
Else
'If you want to do this, you could have the user manually do this as
well.
Me.Undo
End If



--
Wayne Morgan
MS Access MVP

Sorry about that wayne,
I took it off so it isn't available to everyone.
I have reloaded it till you download it again.
I think I got it but you could still look at it and see if you know of a
better way. on the products form I have an Update Inventory Button and a
Refresh Button.
after any entry of received products the user will click on update then
refresh. I would really like to remove the standard popup message saying
you
are about to run an update and replace that with one of my own. But I
don't
know how.
Thanks for your help so much and god bless.
Alvin

I tried to download the new file, but get an error that it can't be found
or
isn't available.

--
Wayne Morgan
MS Access MVP


Thank you for looking at it Wayne,
I have almost got it. I have uploaded an update to the one you
downloaded.
This one has a Query named "QOH Update". It almost does what I need. I
modified some of the VB code as well as adding the "QOH Update" Query.
Please
check it out and you may be able to solve the issue. If you download
it,
go
ahead and delete all records in the "Inventory Transactions" and the
"Purchase Orders" Tables along with "QOH" values in the products Table.
add
products and Quanities then creat a couple of assemblies and after
that,
Before you Post an assembly click back into add/view Products and click
on
"Update Inventory". You will see that QOH is correst. Problem with my
Update
Query is that when you add more Quanities to the products AFTER posting
an
assembly it is as if it doesn't read it. The values in my
[Products.QOH]
are
different than what the calculated values are on the Add/View Products
form,
I Know the calculation on that form is correct. I believe the problem
is
in
my "QOH Update" Query look at it and you may have a solution. It
Updates
where [Inventory Transactions.ProductID]=[Products.ProductID] The
following
is my Dsum value.

DSum("[UnitsReceived]- [UnitsUsed]","Inventory
Transactions","[Inventory
Transactions.ProductID]=" & [Products.ProductID])

I just got a gut fealing the problem lies in there somewhere.
Thanks again Wayne. By the way feel free to keep the program if you
want
to.

 

[Wayne Morgan]

I suspect that the term refers to code in a standard module (a module
created in the Modules tab of the main database window). It is code that can
be called from anywhere within your procedure. One good use of this is if
you find yourself repeating length pieces of code in multiple locations you
can place that code in a standard module, give it a unique procedure name,
and call that procedure instead of writing the code multiple times. Usually,
you'll need to pass parameters to it to make minor adjustments in the way it
runs depending on where it was called from. Sometimes this would be a simple
as passing the name of the form the code was called from.

Another possibility for the term would be a Class Module. Form and Report
modules are technically Class Modules, but when a module is referred to as a
Class Module, it is usually a Class Module that is located in the Modules
tab of the main database window. The definition of a class module in the
help file is "a module that can contain the definition for a new object.
Each instance of a class creates a new object. Procedures defined in the
module become properties and methods of the object." An example of a Class
Module can be found in this sample database by Stephen Lebans
(http://www.lebans.com/DownloadFiles/ImageClassVer11.zip).