G
Guest
How do bulid a databese can show out Qty In, Qty out and Balance on same Forms?
Because I'm new users..
Because I'm new users..
J
John Vinson
Create a Table with fields QtyIn and QtyOut. The table SHOULD NOT
contain a Balance field.
Base a Form on this table, with textboxes for the two Qty fields (and
any other fields that you need to display - surely there's something
that this is a quantity OF).
Put another textbox on the Form and set its Control Source to
=NZ([QtyIn]) - NZ([QtyOut])
This will dynamically calculate the balance; if either quantity is
blank, the NZ() function will treat it as zero, so that if you have 10
"in" and haven't specified anything in "out", the balance will show as
10.
John W. Vinson[MVP]
G
Guest
I try is work.. another few question, How do treat as "ZERO" value after
update Qty Out because each time my quantity out was just 1 item. Just say if
I have 10 Qty In and Qty Out 1, so the balance was 9. If I key in another 1
Qty Out the balance still remain same 9...!! How do count or sum the total in
and total out....? Please help
update Qty Out because each time my quantity out was just 1 item. Just say if
I have 10 Qty In and Qty Out 1, so the balance was 9. If I key in another 1
Qty Out the balance still remain same 9...!! How do count or sum the total in
and total out....? Please help
John Vinson said:
J
John Vinson
Could you explain a bit more? Where are you keying in "another 1 Qty
Out"? What is your actual table structure, and where are you doing the
keying? Do you want to keep a running inventory with multiple ins and
outs? If so you'll need a Totals query to sum all the ins and subtract
all the outs.
John W. Vinson[MVP]
G
Guest
Sorry not explain more in early….
Actually I tying do build a spare parts inventory database with very basic
knowledge only. I have a table with fields Part No, Part Description,
Picture, Qty In, Qty Out and Balance. I need keep tracking on the spare part
had be take out, in and balance on hand.
Each time my guys taken part from the store, they had do update on database
how many qty was taken out. 1pcs they key in 1 in qty out textbox, just say
if another person also taken 1pcs on same parts and saw qty out textbox
already show 1 taken, they not keying just walk away so my balance on hand
not actual anymore. What I mean is each time after keying on qty out or in
the valve must reset to “0†but with actual balance on hand
By the way how to add date/time on fields table? So I can track on
date/time which parts had taken.
Like what you say early, needs a totals query to sum all the ins and
subtract all the outs but I don’t know how to sum all in query, a lot of
functioning. Can you guide me on this?
Actually I tying do build a spare parts inventory database with very basic
knowledge only. I have a table with fields Part No, Part Description,
Picture, Qty In, Qty Out and Balance. I need keep tracking on the spare part
had be take out, in and balance on hand.
Each time my guys taken part from the store, they had do update on database
how many qty was taken out. 1pcs they key in 1 in qty out textbox, just say
if another person also taken 1pcs on same parts and saw qty out textbox
already show 1 taken, they not keying just walk away so my balance on hand
not actual anymore. What I mean is each time after keying on qty out or in
the valve must reset to “0†but with actual balance on hand
By the way how to add date/time on fields table? So I can track on
date/time which parts had taken.
Like what you say early, needs a totals query to sum all the ins and
subtract all the outs but I don’t know how to sum all in query, a lot of
functioning. Can you guide me on this?
J
jahoobob via AccessMonster.com
If you have QtyIn = 10 and you enter QtyOut = 1 the ballance = 9. If you
replace the QtyOut with 1 the balance is still 9 since the value of QtyOut is
still 1.
I would have only the balance in my table. In the form I would have an
adjust quantity field and enter the + or - amount and in code set the balance
field to add this amount to the balance
If the table field is Balance and the adjustment object in your form is
Adjustment then the code in AfterUpdate of Adjustment would be Me!Balance =
Me!Balance + Me!Adjustment
replace the QtyOut with 1 the balance is still 9 since the value of QtyOut is
still 1.
I would have only the balance in my table. In the form I would have an
adjust quantity field and enter the + or - amount and in code set the balance
field to add this amount to the balance
If the table field is Balance and the adjustment object in your form is
Adjustment then the code in AfterUpdate of Adjustment would be Me!Balance =
Me!Balance + Me!Adjustment
G
Guest
I not understand..? What is adjustment object? and what mean
Me!Balance=Me!Balance + Me!Adjusment..? cause i'm stupid on access
Me!Balance=Me!Balance + Me!Adjusment..? cause i'm stupid on access
jahoobob via AccessMonster.com said:
J
jahoobob via AccessMonster.com
Sorry for not elaborating.
Since this is an inventory, the only thing that is needed is how many parts
are there on hand so your table would be PartID(Autonumbeprimary key),
PartNumber, PartName, PartDescription, Balance, and any other information
about the part.
Create a form based on this table with all the fields except PartID. Add an
unbound text box by selecting the ab| on the Forms toolbar in design view.
Open the properties of this textbox and enter Adjustment in the Name propety.
Move down through the properties list until you find AfterUpdate. Click with
the left mouse button in the blank area to the right of AfterUpdate. You
will see three dots (...) show up to the right of this field. Click these
dots with the left mouse button. You will see a popup with three choices.
Select the third one, Code Builder, and then select OK. This will take you
to the Visual Basic Editor and you cursor will be flashing between Private
Sub Adjustment_AfterUpdate() and End Sub. Begin typing where the cursor is:
Me!Balance = Me!Balance + Me!Adjustment.
Close the VB Editor the normal way of closing a Windows application. Close
the Properties window and close and save the form.
what did all of this do?
The textbox Adjustment is a place to enter any adjustment + (in) or -(out).
When you enter a value in this field and move to another field or press Enter
it triggers the AfterUpdate which runs the code you entered. The code say
get the value in Adjustment in the current form Me!Adjustment, add it to the
value of Balance in the current form Me!Balance and replace Balance in the
current form with this value. When Balance is replaced in te form the table
is updated as well.
Me is the way code "gets" information from the open form that runs the code.
I hope this explains it a little better,
Bob
Since this is an inventory, the only thing that is needed is how many parts
are there on hand so your table would be PartID(Autonumbeprimary key),
PartNumber, PartName, PartDescription, Balance, and any other information
about the part.
Create a form based on this table with all the fields except PartID. Add an
unbound text box by selecting the ab| on the Forms toolbar in design view.
Open the properties of this textbox and enter Adjustment in the Name propety.
Move down through the properties list until you find AfterUpdate. Click with
the left mouse button in the blank area to the right of AfterUpdate. You
will see three dots (...) show up to the right of this field. Click these
dots with the left mouse button. You will see a popup with three choices.
Select the third one, Code Builder, and then select OK. This will take you
to the Visual Basic Editor and you cursor will be flashing between Private
Sub Adjustment_AfterUpdate() and End Sub. Begin typing where the cursor is:
Me!Balance = Me!Balance + Me!Adjustment.
Close the VB Editor the normal way of closing a Windows application. Close
the Properties window and close and save the form.
what did all of this do?
The textbox Adjustment is a place to enter any adjustment + (in) or -(out).
When you enter a value in this field and move to another field or press Enter
it triggers the AfterUpdate which runs the code you entered. The code say
get the value in Adjustment in the current form Me!Adjustment, add it to the
value of Balance in the current form Me!Balance and replace Balance in the
current form with this value. When Balance is replaced in te form the table
is updated as well.
Me is the way code "gets" information from the open form that runs the code.
I hope this explains it a little better,
Bob
G
Guest
Thakns for helping me...
Now I have balance in table, How do check the quantity In or Out had add..?
Why the value in Adjustment still there after update..? even move to another
field or press Enter. Until I close the form and re-open...!the balance was
ok..! How do add a "date" can track all value be add?
Now I have balance in table, How do check the quantity In or Out had add..?
Why the value in Adjustment still there after update..? even move to another
field or press Enter. Until I close the form and re-open...!the balance was
ok..! How do add a "date" can track all value be add?
jahoobob via AccessMonster.com said:
J
jahoobob via AccessMonster.com
Do you want to keep track of every adjustment (every In and every Out)?
To zero the adjustment, after Me!Balance = Me!Balance + Me!Adjustment enter
this:
Me!Adjustment = 0
To zero the adjustment, after Me!Balance = Me!Balance + Me!Adjustment enter
this:
Me!Adjustment = 0
G
Guest
Hi,
Yes...! I need keep track evey adjustment In and Out.
Yes...! I need keep track evey adjustment In and Out.
jahoobob via AccessMonster.com said:
G
Guest
Can you tell me how to keep track every adjustment (evey In anr every Out)
please help...!
please help...!
jahoobob via AccessMonster.com said:
J
jahoobob via AccessMonster.com
Ok forget about storing the balance. You will however need an additional
table that keeps track of In's and Out's.
Let's say you have 400 parts and each part is kept in a Parts table with part
#, part description, etc.
You shouldn't store each In and Out in this table for two reasons. If you
stored each In and each out with the parts info you would have duplicated
data (part #, part description, etc.) or you stored only the last In and last
Out if you had two fields or the last In OR Out if you had only one field you
wouldn't get all the In's and Out's.
So you will need another table with Part # and InventoryAdjustment. You
don't need two fields since In is + and Out is -. Each time you adjust the
inventory you will need to sum the InventoryAdjustmetn for that Part #. You
may also want to store the date the adjustment was made. This can be done in
the form automatically by setting the AdjustmentDate default to Date().
Personally, I would go with my original method of storing the balance (Sorry
John, but I think this is one calculated field that should be stored because
it reflects what is. When inventory time comes the number of parts will then
either match or not match the database number and if it doesn't the new
number can be entered directly (via a form of course.)
table that keeps track of In's and Out's.
Let's say you have 400 parts and each part is kept in a Parts table with part
#, part description, etc.
You shouldn't store each In and Out in this table for two reasons. If you
stored each In and each out with the parts info you would have duplicated
data (part #, part description, etc.) or you stored only the last In and last
Out if you had two fields or the last In OR Out if you had only one field you
wouldn't get all the In's and Out's.
So you will need another table with Part # and InventoryAdjustment. You
don't need two fields since In is + and Out is -. Each time you adjust the
inventory you will need to sum the InventoryAdjustmetn for that Part #. You
may also want to store the date the adjustment was made. This can be done in
the form automatically by setting the AdjustmentDate default to Date().
Personally, I would go with my original method of storing the balance (Sorry
John, but I think this is one calculated field that should be stored because
it reflects what is. When inventory time comes the number of parts will then
either match or not match the database number and if it doesn't the new
number can be entered directly (via a form of course.)
G
Guest
Thanks again... 
I want a Msgbox pop up "Would you like to save your change?" if a value in
or out a form is changed with yes/no option. if no the valve will undo.
Any ideas...? please help
I want a Msgbox pop up "Would you like to save your change?" if a value in
or out a form is changed with yes/no option. if no the valve will undo.
Any ideas...? please help
jahoobob via AccessMonster.com said: