Modules & VBA :: FIFO Inventory Calculation - Cost Of Items?
Jan 29, 2015
I am setting up a inventory database and i will like the cost of the items to be calculated using FIFO.
Sample tables:
PARTS TABLE:
part code
description
cost
quantity on hand
PART TYPES
part type id
party type
INVENTORY
invent id
location
reorder quantity
INVOICE
id
date
part code
part type
qty
cost
ext cost
NB
For every item i will like the previous cost to be charged before the new cost. eg. if 10 pens where entered at $2 and another 5pens were entered at $4 each and 1 need 11 pens, i want the first 10 to be charged at $2 each and one at $4.
View Replies
ADVERTISEMENT
Apr 17, 2013
I want to make inventory fifo(first in first out) db. Example:
February
1- beginning balance : 800 unit @ 6$ per unit
4- received 200 unit @ 7$ per unit
10- received 200 unit @ 8$ per unit
11- issued 800 unit
12- received 400 unit @ 8$ per unit
20- issued 500 unit
25- returned 100 unit to storeroom to be recorded as latest issued prices
28- received 600 unit @ 9$ per unit
The report must be like pic attached
I dont know how to make transaction to do this....
My database in attachment
View 3 Replies
View Related
Feb 18, 2014
I'm trying to add items received to my inventory table. If the item is already in the table, I just want to update the number and cost etc. If the item is not in the inventory table I want to add it. My problem is determining which item in the source table is already in the target table so I can either update of add. how to find an item number in target table by looping through the source table?
Here's the code I've written which doesn't work.
Dim I As Integer
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Set rs = Nothing
Set rs2 = Nothing
Set db = CurrentDb
[code]....
View 5 Replies
View Related
Jun 19, 2015
I have a list box that allows multiple selections [Inventory]. I also have a combo box that has multiple selections [Shows].
Right now, user selects from list box and from a combo box and clicks a button. On button click, the items from the list box are associated with the PK from the combo and stored in a junction table. This allows me to quickly associate many inventory items to one show.
I realized that there I currently have no way to prevent duplicate Inventory+show records in the junction table besides having a composite key. This would be fine except no records get inserted into the junction table if there's a duplicate entry.
Ideally, I think that the user should select from the combo box [Shows]. This should narrow down what shows up in the list box [Inventory] in a way that Inventory items already associated with the show are not displayed.
If I have 10 Inventory items and Inventory items 1-5 are already associated with Show 1; after I select the combo box, the list box only displays Inventory items 6-10.
Here's the associated code
Option Compare Database
Option Explicit
Private Sub cmdAddRecords_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
[Code] ....
View 13 Replies
View Related
Mar 13, 2014
I have a table with inventory items, a separate table with storage charges per day (ex .03, .04, .05 per day/per item)
I have created some queries where I take the items & # of days they have been in storage and when I try and create an expression for storage charges based upon QTY & # of days I am getting results like it is multiplying whole numbers and not very small increments like .03
I have checked the math, and its not multiplying by 3 instead of .03... I cant quite figure out how it is coming up with the numbers.
Again, the pricing is coming from a lookup wizard to another table. It seems like it should be a very straight forward expression but I cannot get it to work. Does the figures being from a lookup have any issues?
View 6 Replies
View Related
Sep 22, 2013
I have a combo box in a sub form with three values, rate1, rate2, and rate3. I have another three fields in the sub form rate1, rate2, and rate3. The rates are dependent on the item. When I select the rate from the combo box I want a cost field to update on change to the relevant rate. I tried this to no avail:
Code:
txtCost = DLookup(cboRate.Value, "tblItem", "ItemID=" & ItemID)
View 5 Replies
View Related
Dec 26, 2014
I have discovered that the solution should be a FIFO system (first-in,first-out).
My question here now is how do i go about this FIFO designing.
View 2 Replies
View Related
Jan 3, 2014
I have a large database of items we sell on Amazon, I am looking to automate the process of uploading the inventory.
I am uploading a tab delimeted text file using the following code,
With CreateObject("msxml2.xmlhttp")
.Open "POST", strURL, False
.setRequestHeader "Host:", "mws.amazonservices.co.uk"
.setRequestHeader "User-Agent:", "VBA"
.setRequestHeader "Content-MD5:", md5hdr2
.setRequestHeader "Content-Type:", "text"
.send c2a
Forms!Form1.Text3.value = .responseText
End With
I am confident I have the signature and the MD5 Header, but I cannot get the data into Amazon!
I keep getting a non-descript error "InputDataError".
When debugging my Play API, I was told that the "send" command was not uploading the file contents, it was uploading the filename! So c2a is a string variable that contains the tab delimited data. This works like a charm for Play, but no joy for Amazon.
View 4 Replies
View Related
Jan 5, 2014
I have an inventory app, how to make daily opening stock /closing stock.I want the system done automatically,that is as users exit,closing stocks & next day opening stocks as users log in.Then daily stock reports can be generated.
View 2 Replies
View Related
Feb 5, 2015
Code that will allow me to post selective data from a purchase received form to the inventory using a command button. this command should also add to the existing quantity in the inventory.
View 2 Replies
View Related
Sep 10, 2013
how to get the sum of column 2 of a list box total bags is in the second column, i only want the total of bags of the ones selected
I can get the sum of all the boxes but only want highlighted ones
Public Function SumListBox(sForm As String, _
sCtrl As String, iColumn As Integer) As Variant
Dim frm As Form
[Code]....
View 1 Replies
View Related
Jul 29, 2013
I'm stuck on this one part of my code where I am trying to count values in a column called 'ItemQty' in table 'dbo_Item'. I only want to count the values with the associated values in column 'OrderNumber'. I am getting the values of 'OrderNumber' from an array. Here is my code...(it doesn't work though. When I put a Msgbox to print out what the ItemQuantity value ends up to be, it only prints my code back to me.)
Code:
For Each Order In q
MsgBox "Order = '" & Order & "'"
ItemQuantity = ItemQuantity + ("count(ItemQty) Where OrderNumber LIKE '*" & Order & "'")
Next Order
View 3 Replies
View Related
Sep 9, 2014
I have two listbox (SearchResults5 has two columns) and list_asset_add (one column) both have Extended multi-selection active. I have this code that automatically moves the items between the two listbox (it runs after pushing a button):
Code:
Sub CopiTo_Click()
Dim Msg As String
Dim i As Variant
If SearchResults5.ListIndex = -1 Then
Msg = "Nothing"
[Code] ....
This works quite well if I manually select the items that I want to move. Most of the times SearchResults5 has lots of elements so I have decided to create a "Select All" button to speed up the process, here the code:
Private Sub Command271_Click()
Dim n As Integer
With Me.SearchResults5
For n = 0 To .ListCount - 1
.Selected(n) = True
Next n
End With
End Sub
When I use the "Select All" button and I try to move the items between the two listbox, the function does NOT work.
BUT if I manually select one or more items in the first listbox, then I clear the selection and finally I use the subroutine to move the items between the two listbox, then the it works well again. How to make it work properly.
View 5 Replies
View Related
Apr 5, 2015
I have managed to amend records based on the user selecting multiple items in a list box by using the following code.
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Usage", dbOpenDynaset, dbAppendOnly)
Set ctl = [Forms]![frmsearch]![lstSelector]
[Code].....
what I need to do now, is to loop(I think) through and amend all the records in a populate listbox, with no selections, similar to above, but without selection.
View 3 Replies
View Related
Aug 6, 2013
I'm trying to setup a listbox so that multiple items may be selected and removed at once. The Listbox Multi Select property is currently set to "Extended" and I have the following code on the onclick event of a command button:
Code:
Private Sub Command12_Click()
Dim i As Integer
For i = List10.ListCount - 1 To 0 Step -1
[Code]....
I have tested selecting 4 or so records on the list box and then pushing the remove button but it seems that the only record that is removed is the selected record that is furthest down on the listbox. The other selected records become unselected and must be reselected in order to continue removing records.
View 1 Replies
View Related
Jan 9, 2014
In my form I have the listFunctions list box set to Multi Select "Extended" in the following code contains a line to execute a query based on the selected items in a listbox. but for some reason instead of only changing the selected items it is changing all items in the listbox. I stepped through the code and it is looping the correct number of times based on the amount selected but is still changing all.
eg.if I select 3 items from the list, it loops through the execute 3 times.but the total 6 items will change.
Code:
Private Sub cmdEdit_Click()
Dim varItm As Variant
Dim sSQL As String
Dim ssSQL As String
[code]...
View 1 Replies
View Related
Jul 8, 2014
I have added a list box to a form with Single Selection mode on. Its purpose is to improve the interface. It contains items that represent all records. Clicking on the list box causes the form to jump to another record.
The problem is following: if I click on the List Box, it clears the selection and nothing is highlighted but it jumps to correct record. When I use standard record selection buttons, it highlights the correct items.
I read the index of selected item from .ListIndex property because Selected() does not work in a Single Selection mode. However, this is read-only property and I cannot use this to highlight the item back from VBA. But when I use Selected() it is not working. I mean when I click again on the same item it's selected. It's weird. I attached a simple database file with this problem.
The second problem is, when using standard record selection buttons, access iterates through all records and then jump to empty one. That is not like a new record. I don't know which event to use to control this situation. I would like to deselect all items, let user enter the data and re-query the List Box with a new record.
View 3 Replies
View Related
Jun 17, 2015
I have a multiselect listbox and two date fields (StartDate & EndDate) in an Access form.I am trying to add records to the Table through the form on a button click.I select multiple items from the list box and the date range between the start date and end date will be equal to the items selected from listbox.For each item selected from the list box I need to add a separate record with a date.So the first record will have List box item selected1 and the start date.Next record will have item 2 from list box and date as dateadd("d",startdate,1)And final record will have last item selected from the listbox and date as enddate.
View 5 Replies
View Related
Aug 18, 2014
How do I remove items from a right click menu in access 2003? For example I need to remove the form view / design view button from a right click menu but i want to keep the hide/show columns button on the same menu.
View 1 Replies
View Related
May 28, 2014
In appendix is .mdb file with this thema.
Inside you can find one form with listbox (with multi selecting ability).
I use this code :
Dim strSQL As String
Dim i As Variant
With Me.se1
For Each i In .ItemsSelected
SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
CurrentDb.Execute (SQL)
Next
End With
Me.se1.Requery
Result is this one :
1) If I delete only one item in list, it is ok. I can do it again and again and it is working fine.
BUT
2) If I delete more items in list at once, it is ok - but if I will try to do it again then there is an error because .ItemData(i) value is Null.
View 10 Replies
View Related
Oct 20, 2014
If there a way i can remove duplicates from an email list? I pull these emails from a recordset. But i email may appear more than once and it doesn't look good e.g
johndoe@mail.com;johndoe@mail.com;jo...hndoe@mail.com.
I'd like to remove the duplicated email, if its possible. Code below.
Code:
Set rs = CurrentDb.OpenRecordset("select * from query")
With objMailItem
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs![email]) Then
vRecipientList = vRecipientList & rs![email] & ";"
rs.MoveNext
Else
rs.MoveNext
End If
Loop Until rs.EOF
.To = vRecipientList
View 6 Replies
View Related
Apr 10, 2015
I want to use an array to store data from a list box into a variable. I want it to be able to store one value, or multiple values, depending on what is selected.
Main problem: this list box feeds off a table which has employee names and their e-mails. The list box itself only shows the names, and when I select what I want the array to store is their e-mails, not their names.
Code:
Dim strNames As String
Dim varItem As Variant
Dim intCount As Integer
For Each varItem In Me.lstNames.ItemsSelected
intCount = intCount + 1
Select Case Len(strNames)
[Code] ....
That code successfully displays the item I selected, but only displays the name. I need to make it look in the table and get me column #2. I also want it to be able to select more than one item at a time.
View 5 Replies
View Related
Aug 6, 2015
I am currently in the process of creating a form that will allow you to enter details for an email sent, select a number of documents from a list box and then open the mail message pre-written with attachments. The attachments reside in a list box currently, and I am attempting to use a "For Each" with item selected.column(3) as the file path, as column(3) contains the filepath from the table.
Currently my code is:
Private Sub OutlookBut_Click()
Dim olApp As Object
Dim objMail As Object
Dim varItm As Variant
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
[Code] ....
When this code runs, outlook will open as a process when it is closed, but then freeze and not allow me to see it or access it in anyway. If Outlook is opened Microsoft office usually gets angry at me and decides to throw error messages at me.
View 2 Replies
View Related
Aug 12, 2013
i have a form that there is a list box inside that. after selection of items (usually 20 items) and right click the mouse on items it should open another pop up form,the problem is after right click selected items will be unselected except one item that there is mouse on that. how can i prevent list box from deselecting items after right click .
the code for mouse right click is like below:
Private Sub ItemList_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
Const RIGHTBUTTON = 2
Dim udtPos As POINTAPI
Dim frm As Access.Form
If Button = RIGHTBUTTON Then
Set mp = New [*clsMousePosition]
GetCursorPos udtPos
DoCmd.OpenForm "frmshortcut"
DoCmd.MoveSize udtPos.x * mp.TwipsPerPixelX, udtPos.y * mp.TwipsPerPixelY
Forms!frmshortcut!txtparameter = Me.ItemList.Value
End If
End Sub
View 3 Replies
View Related
Jul 31, 2013
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K
and 5 pc set = A,B,C,D,F
and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000
B=3000
C=3000
D=3000
F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
View 3 Replies
View Related
Jul 8, 2014
In my Access app, I need to be able to retrieve a specific email by date/time, then take the Subject, the Date received and body of that email and save it to a column in a table.how to do this in Access VBA?
View 1 Replies
View Related
