By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Inventory on Access

P: 5
Hi, I am building a warehouse database for a small non-profit organisation that works in Africa.

Currently, I have created a data entry form and table. The table is called: Inventory Transactions. This table contains information regarding the donation or purchase of a product.

One of the reports I am making is a shipping manifest. I want to take the information from Inventory Transactions and put five fields from that table into the table Individual Manifest. I want to transfer the relevant information (i.e. ItemCategory, ItemType, ItemName, ItemWeight, ItemWeightType, Units, UnitType, UnitValue).

So here is my question: How do I transfer a select number of fields from Inventory Transactions into Individual Manifest without deleting anything from Inventory Transactions, unless the product quantity in Inventory Transactions is 0. I want to be able to select an item from from Inventory Transactions enter a quantity to ship and have all that information placed into Individual Manifest. In the meantime, I want the quantity in Individual Transactions to reflect the number of items on hand. For example, if I received 50 items and I ship 25 of them, I want to be able to see that I still have 25 items left in the warehouse.

I hope this isn't to confusing, because I think it is really confusing.

Perhaps an example is relevant:

I am creating a new manifest in a form and I want to add an item to the manifest. I select an item from Inventory Transactions enter a quantity (ideally into a dialogue box) and that is displayed in the table Individual Manifest. So I select "Blankets" and enter a quantity of 50. This subtracts 50 blankets from the Inventory Transactions and adds 50 blankets to Individual Manifest.

I hope this is understandable. Please accept my apologies for being confusing. I don't know too much about Access but I kind of got roped into doing this.
Jul 31 '07 #1
Share this Question
Share on Google+
9 Replies


P: 5
I am still looking for some insight.

Anything is appreciated.

Many thanks.
Aug 2 '07 #2

ADezii
Expert 5K+
P: 8,610
I am still looking for some insight.

Anything is appreciated.

Many thanks.
I'll have a workable solution for you by this evening - please be patient. Please understand that your request is not being ignored. The majority of the Moderators/Experts here all have full time jobs, and it is not always easy to resond to Posts, especially when the answer is not simply a couple of lines of code, which is not applicable in your case. See you soon.
Aug 7 '07 #3

ADezii
Expert 5K+
P: 8,610
I am still looking for some insight.

Anything is appreciated.

Many thanks.
Since you are relatively new, I'll give my solution to you in segments, so that you are not overwhelmed, and so that we can discuss each segment should you have any questions. First of all, a few assumptions:
  1. You never indicated whether or not you had a Primary Key in the Inventory Transactions, so I'll assume that you do and the Field Name is [Inv_ID] - AUTONUMBER - PRIMARY KEY. Make any required substitutions if necessary.
  2. Inventory Transactions contains a Field for Quantity, so I just called it [Quantity].
  3. I also assumed that the mechanism for this Transfer is a Form, so I created one and called it frmInventoryTransactions (intuitive, isn't it?).
  4. On this Form is a Combo Box named cboInventoryItems. This Combo Box contains 2 Columns: the 1st is Inv_ID (hidden), and the 2nd is ItemName (visible). This Combo is Unbound and is where the User will select his Inventory Item to Transfer Quantity from. This Item will be uniquely identified by the Inv_ID value in Column 1. The Row Source for this Combo Box is:
    Expand|Select|Wrap|Line Numbers
    1. SELECT [Inventory Transactions].[Inv_ID], [Inventory Transactions].[ItemName] FROM [Inventory Transactions] ORDER BY [Inventory Transactions].[ItemName];
    2.  
  5. There is an Unbound Text Box on this Form called txtQuantityToTransfer. It is also Unbound and is where the User will enter a Quantity to Transfer for a specific Item selected in cboInventoryItems.
  6. The following code is placed in the Click() Event of a Command Button with the Caption Transfer. I won't go into detail explaining the code, it is somewhat self-documenting and I don't want to confuse you.
    Expand|Select|Wrap|Line Numbers
    1. 'You need both an Inventory Item and a Quantity in order to initialte a Transfer
    2. If IsNull(Me![cboInventoryItems]) Or IsNull(Me![txtQuantityToTransfer]) Then
    3.   Exit Sub
    4. ElseIf Not IsNumeric(Me![txtQuantityToTransfer]) Then
    5.   MsgBox "You must enter a Number in the Quantity Field!", vbExclamation, "Invalid Entry"
    6.     Exit Sub
    7. ElseIf Val(Me![txtQuantityToTransfer]) <= 0 Then
    8.   MsgBox "You must enter a Quantity to Transfer that is greater than 0!", vbExclamation, "Invalid Entry"
    9.     Exit Sub
    10. 'See if the Transfer Quantity > Quantity on Hand for the specific Item
    11. ElseIf Val(Me![txtQuantityToTransfer]) > Val(DLookup("[Quantity]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])) Then
    12.   MsgBox "The Quantity you wish to transfer exceeds the Quantity on Hand!", vbExclamation, "Invalid Entry"
    13.     Exit Sub
    14. End If
    15.  
    16. 'If you get to this point, everything is OK, your can start the Transfer
    17.  
  7. Look over everything in detail, and should you have any questions, feel free to ask.
Aug 7 '07 #4

P: 5
Thank you so much for your reply! I am going to test this solution as soon as possible. I greatly appreciate your help!
Aug 9 '07 #5

P: 5
Ok. So I entered all the code and set up the combo box and the unbound text box. But nothing happens.

Did I miss something?
Aug 9 '07 #6

ADezii
Expert 5K+
P: 8,610
Ok. So I entered all the code and set up the combo box and the unbound text box. But nothing happens.

Did I miss something?
This is just the preliminary code, I'll try to post the rest of the code this evening. This is the code that will actually do the work,
Aug 9 '07 #7

ADezii
Expert 5K+
P: 8,610
This is just the preliminary code, I'll try to post the rest of the code this evening. This is the code that will actually do the work,
Here is the complete code for your Mini Inventory System. I've thoroughly checked the code and it appears to be fully functional as long as you keep the exact Naming Conventions that exist in the code, do not change Data types, etc. Rather then attempt a detailed explanation, I'm simply post the code. Look it over thoroughly, and if you have any questions, feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. 'You need both an Inventory Item and a Quantity in order to initialte a Transfer
  2. If IsNull(Me![cboInventoryItems]) Or IsNull(Me![txtQuantityToTransfer]) Then
  3.   Exit Sub
  4. ElseIf Not IsNumeric(Me![txtQuantityToTransfer]) Then
  5.   MsgBox "You must enter a Number in the Quantity Field!", vbExclamation, "Invalid Entry"
  6.     Exit Sub
  7. ElseIf Val(Me![txtQuantityToTransfer]) <= 0 Then
  8.   MsgBox "You must enter a Quantity to Transfer that is greater than 0!", vbExclamation, "Invalid Entry"
  9.     Exit Sub
  10. 'See if the Transfer Quantity > Quantity on Hand for the specific Item
  11. ElseIf Val(Me![txtQuantityToTransfer]) > Val(DLookup("[Quantity]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])) Then
  12.   MsgBox "The Quantity you wish to transfer exceeds the Quantity on Hand!", vbExclamation, "Invalid Entry"
  13.     Exit Sub
  14. End If
  15.  
  16. 'If you get to this point, everything is OK, your can start the Transfer
  17. 'Declare Variables to hold values from the 8 Fields to be Appended to
  18. 'the Individual Manifest Table based on Me![cboInventoryItems] ([Inv_ID])
  19. Dim varItemCategory, varItemType, varItemName, varItemWeight, varItemWeightType
  20. Dim varUnits, varUnitType, varUnitValue, intQuantity
  21.  
  22. varItemCategory = DLookup("[ItemCategory]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
  23. varItemType = DLookup("[ItemType]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
  24. varItemName = Me![cboInventoryItems].Column(1)
  25. varItemWeight = DLookup("[ItemWeight]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
  26. varItemWeightType = DLookup("[ItemWeightType]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
  27. varUnits = DLookup("[Units]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
  28. varUnitType = DLookup("[UnitType]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
  29. varUnitValue = DLookup("[UnitValue]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
  30. intQuantity = Val(Me![txtQuantityToTransfer])
  31. 'Decided to go the code route (DAO) basically because I despise SQL
  32.   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  33.   Set MyDB = CurrentDb()
  34.   Set MyRS = MyDB.OpenRecordset("Individual Manifest", dbOpenDynaset)
  35.     With MyRS
  36.       .AddNew
  37.         ![ItemCategory] = varItemCategory
  38.         ![ItemType] = varItemType
  39.         ![ItemName] = varItemName
  40.         ![ItemWeight] = varItemWeight
  41.         ![ItemWeightType] = varItemWeightType
  42.         ![Units] = varUnits
  43.         ![UnitType] = varUnitType
  44.         ![UnitValue] = varUnitValue
  45.         ![Quantity] = intQuantity
  46.       .Update
  47.     End With
  48.   MyRS.Close
  49.  
  50. 'Must now subtract the Quantity from the Inventory Transactions Table
  51.   Dim MySQL As String
  52.   DoCmd.SetWarnings False
  53.     MySQL = "UPDATE [Inventory Transactions] SET [Inventory Transactions].[Quantity] = [Inventory Transactions].[Quantity] - "
  54.     MySQL = MySQL & intQuantity & " WHERE [Inv_ID] = " & Me![cboInventoryItems]
  55.       DoCmd.RunSQL MySQL
  56.   DoCmd.SetWarnings True
  57.  
Aug 9 '07 #8

P: 5
Wow.

I can't tell you how much I am thankful for your help!
Aug 10 '07 #9

ADezii
Expert 5K+
P: 8,610
Wow.

I can't tell you how much I am thankful for your help!
Not a problem. There is a small detail that you may also want to consider. After the User selects an Inventory Item from the Combo Box, you should display the Available Quantity for that specific item. As it stands now, the User has no idea as to how much to transfer to the Individual Manifest since there is no visual cue. Let me know should you want to implement this, it's only an additional line of code and a Text Box.
Aug 10 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.