Inventory on Access

[rysch]

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.

[rysch]

I am still looking for some insight.

Anything is appreciated.

Many thanks.

[ADezii]

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.

[ADezii]

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:
   

   SELECT [Inventory Transactions].[Inv_ID], [Inventory Transactions].[ItemName] FROM [Inventory Transactions] ORDER BY [Inventory Transactions].[ItemName];
    

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.
   

   'You need both an Inventory Item and a Quantity in order to initialte a Transfer
   If IsNull(Me![cboInventoryItems]) Or IsNull(Me![txtQuantityToTransfer]) Then
     Exit Sub
   ElseIf Not IsNumeric(Me![txtQuantityToTransfer]) Then
     MsgBox "You must enter a Number in the Quantity Field!", vbExclamation, "Invalid Entry"
       Exit Sub
   ElseIf Val(Me![txtQuantityToTransfer]) <= 0 Then
     MsgBox "You must enter a Quantity to Transfer that is greater than 0!", vbExclamation, "Invalid Entry"
       Exit Sub
   'See if the Transfer Quantity > Quantity on Hand for the specific Item
   ElseIf Val(Me![txtQuantityToTransfer]) > Val(DLookup("[Quantity]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])) Then
     MsgBox "The Quantity you wish to transfer exceeds the Quantity on Hand!", vbExclamation, "Invalid Entry"
       Exit Sub
   End If
    
   'If you get to this point, everything is OK, your can start the Transfer
    

7. Look over everything in detail, and should you have any questions, feel free to ask.

[rysch]

Thank you so much for your reply! I am going to test this solution as soon as possible. I greatly appreciate your help!

[rysch]

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?

[ADezii]

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,

[ADezii]

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.

'You need both an Inventory Item and a Quantity in order to initialte a Transfer
If IsNull(Me![cboInventoryItems]) Or IsNull(Me![txtQuantityToTransfer]) Then
  Exit Sub
ElseIf Not IsNumeric(Me![txtQuantityToTransfer]) Then
  MsgBox "You must enter a Number in the Quantity Field!", vbExclamation, "Invalid Entry"
    Exit Sub
ElseIf Val(Me![txtQuantityToTransfer]) <= 0 Then
  MsgBox "You must enter a Quantity to Transfer that is greater than 0!", vbExclamation, "Invalid Entry"
    Exit Sub
'See if the Transfer Quantity > Quantity on Hand for the specific Item
ElseIf Val(Me![txtQuantityToTransfer]) > Val(DLookup("[Quantity]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])) Then
  MsgBox "The Quantity you wish to transfer exceeds the Quantity on Hand!", vbExclamation, "Invalid Entry"
    Exit Sub
End If
 
'If you get to this point, everything is OK, your can start the Transfer
'Declare Variables to hold values from the 8 Fields to be Appended to
'the Individual Manifest Table based on Me![cboInventoryItems] ([Inv_ID])
Dim varItemCategory, varItemType, varItemName, varItemWeight, varItemWeightType
Dim varUnits, varUnitType, varUnitValue, intQuantity
 
varItemCategory = DLookup("[ItemCategory]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
varItemType = DLookup("[ItemType]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
varItemName = Me![cboInventoryItems].Column(1)
varItemWeight = DLookup("[ItemWeight]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
varItemWeightType = DLookup("[ItemWeightType]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
varUnits = DLookup("[Units]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
varUnitType = DLookup("[UnitType]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
varUnitValue = DLookup("[UnitValue]", "Inventory Transactions", "[Inv_ID]=" & Me![cboInventoryItems])
intQuantity = Val(Me![txtQuantityToTransfer])
'Decided to go the code route (DAO) basically because I despise SQL
  Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  Set MyDB = CurrentDb()
  Set MyRS = MyDB.OpenRecordset("Individual Manifest", dbOpenDynaset)
    With MyRS
      .AddNew
        ![ItemCategory] = varItemCategory
        ![ItemType] = varItemType
        ![ItemName] = varItemName
        ![ItemWeight] = varItemWeight
        ![ItemWeightType] = varItemWeightType
        ![Units] = varUnits
        ![UnitType] = varUnitType
        ![UnitValue] = varUnitValue
        ![Quantity] = intQuantity
      .Update
    End With
  MyRS.Close
 
'Must now subtract the Quantity from the Inventory Transactions Table
  Dim MySQL As String
  DoCmd.SetWarnings False
    MySQL = "UPDATE [Inventory Transactions] SET [Inventory Transactions].[Quantity] = [Inventory Transactions].[Quantity] - "
    MySQL = MySQL & intQuantity & " WHERE [Inv_ID] = " & Me![cboInventoryItems]
      DoCmd.RunSQL MySQL
  DoCmd.SetWarnings True
 

[rysch]

Wow.

I can't tell you how much I am thankful for your help!

[ADezii]

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.