 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / June 2006Bulid database
How do bulid a databese can show out Qty In, Qty out and Balance on same Forms? Because I'm new users.. >How do bulid a databese can show out Qty In, Qty out and Balance on same Forms? >Because I'm new users.. 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] 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 > >How do bulid a databese can show out Qty In, Qty out and Balance on same Forms? > >Because I'm new users.. [quoted text clipped - 16 lines] > > John W. Vinson[MVP] >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 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] 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? > >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 [quoted text clipped - 9 lines] > > John W. Vinson[MVP] 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 >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 [quoted text clipped - 7 lines] >> >> John W. Vinson[MVP] I not understand..? What is adjustment object? and what mean Me!Balance=Me!Balance + Me!Adjusment..? cause i'm stupid on access > 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 [quoted text clipped - 18 lines] > >> > >> John W. Vinson[MVP] 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 >I not understand..? What is adjustment object? and what mean >Me!Balance=Me!Balance + Me!Adjusment..? cause i'm stupid on access [quoted text clipped - 4 lines] >> >> >> >> John W. Vinson[MVP] 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? > Sorry for not elaborating. > Since this is an inventory, the only thing that is needed is how many parts [quoted text clipped - 35 lines] > >> >> > >> >> John W. Vinson[MVP] 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 >Thakns for helping me... > [quoted text clipped - 8 lines] >> >> >> >> >> >> John W. Vinson[MVP] Hi, Yes...! I need keep track evey adjustment In and Out. > Do you want to keep track of every adjustment (every In and every Out)? > [quoted text clipped - 14 lines] > >> >> >> > >> >> >> John W. Vinson[MVP] Can you tell me how to keep track every adjustment (evey In anr every Out) please help...! > Do you want to keep track of every adjustment (every In and every Out)? > [quoted text clipped - 14 lines] > >> >> >> > >> >> >> John W. Vinson[MVP] 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.) >Can you tell me how to keep track every adjustment (evey In anr every Out) >please help...! [quoted text clipped - 4 lines] >> >> >> >> >> >> >> >> John W. Vinson[MVP] 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 > Ok forget about storing the balance. You will however need an additional > table that keeps track of In's and Out's. [quoted text clipped - 24 lines] > >> >> >> >> > >> >> >> >> John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.