Help from access query design for beginning and ending inventory

Question

i want to transfer the end inventory of previous date to current date row as beg inventory with the same ID
can someone help me plsss

i148.     photobucket   .com

/albums/s1/blackcore94/jh.png

Answer 1

link is broken...

Answer 2

not just remove the spaces because microsoft does not allow me to post link

Answer 3

Link works ok. For those who can't access it:

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 4

Air code:

Private Sub ID_AfterUpdate()
Dim sq As String
Dim rs As DAO.Recordset

   On Error GoTo ID_AfterUpdate_err

   If Me.NewRecord Then
      
      'WARNING!!!
      'End is an MS Access keyword, avoid using it in different context!
      sq = "SELECT TOP 1 Shitxi.End FROM Shitxi " & _
              "WHERE (CLng(Shitxi.DateDel) < " & CLng(Me.[DateDel]) & ") And " & _
                          "(Shitxi.ID = " & Me.[ID] & ") " & _
              "ORDER BY Shitxi.DateDel DESC;"
      Set rs = CurrentDb.OpenRecordset(sq)
      
      If Not (rs.BOF And rs.EOF) Then
         Me.[Beg] = Nz(rs(0), 0) + 1
      Else
         Me.[Beg] = 1
      End If
      
   End If

ID_AfterUpdate_exit:
   On Error Resume Next
   rs.Close
   Set rs = Nothing
   Exit Sub

ID_AfterUpdate_err:
   MsgBox Err.Number & "  " & Err.Description
   Resume ID_AfterUpdate_exit
   
End Sub

HTH

---

Vladimir Cvajniga

Answer 5

tnx :)

Answer 6

ohh tnx a lot x)

but where i can input this

through vb.net or access?

tnx a lot again

Answer 7

Access VBA, see event procedures.

Create a form which is based on Shitxi table. Recordsource sould be the following SQL-query:

SELECT * FROM Shitxi
ORDER BY Shitxi.DateDel, ID;

Then put controls on the form and add an event procedure AfterUpdate for the filed ID. This is the "Private Sub ID_AfterUpdate()" that I posted earlier.

Then you can easily edit data in Shitxi from within the form.

---

Vladimir Cvajniga

Answer 8

Or download sample project: http://www.alis.cz/relax/download/access/samples/myShixi.mdb

---

Vladimir Cvajniga

Answer 9

tnx a lot again
srry bout my confusing questions

but this is what i really want

an access than serves as the database from vb.net where the id i mentioned is equal to prodno

if prodno of last date is = 1 then it is product 1 then if the end inventory of the last date is 50, then the beginv of new record of product is equal to 50

just one last x) tnx a lot again , even a query or form, just somethin i can use as database for vb.net

you don't have to code them a lot, i just only want how to transfer end inventory of one type of product to beg of that one type product on next date

http://s148.photobucket.com/user/blackcore94/media/Warehouse.png.html

Answer 10

You might find the file Inventory.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

of some help.  This little file demo was produced a while ago for someone with a similar requirement to yours.  The qryTransactions query used as the subform's RecordSource uses VBA domain functions to make the query updatable.  For a read-only query subqueries should be more efficient

---

Ken Sheridan, Stafford, England

Answer 11

You can simply at logic behind the starting form on your project to load each time the project is loaded Or you add the logic behind the closing form each time the project closes. Here is a sample code included in the Load even of a form:

Option Compare Database

Private Sub Form_Load()
Dim ProdNo1 As Integer
Dim EndInvt1 As Integer
Dim LDate1 As Date
Dim ProdNo12 As Integer
Dim EndInvt2 As Integer
Dim LDate2 As Date
Dim rs As DAO.Recordset

ProdNo1 = DMin("ID", "tblShitxi")
LDate1 = DMax("DateDel", "tblShitxi", "ID=" & ProdNo1)
ProdNo2 = DMax("ID", "tblShitxi")
LDate2 = DMax("DateDel", "tblShitxi", "ID=" & ProdNo2)
EndInvt1 = Nz(DLookup("EndInvt", "tblShitxi", "ID=" & ProdNo1 & " And DateDel=#" & LDate1 & "#")) + 0
EndInvt2 = Nz(DLookup("EndInvt", "tblShitxi", "ID=" & ProdNo2 & " And DateDel=#" & LDate2 & "#")) + 0

If LDate1 < Date Then
    Set rs = CurrentDb.OpenRecordset("tblShitxi")
        With rs
            .AddNew
            .Fields(0) = ProdNo1
            .Fields(1) = Date
            .Fields(2) = EndInvt1
            .Update
        End With
        With rs
            .AddNew
            .Fields(0) = ProdNo2
            .Fields(1) = Date
            .Fields(2) = EndInvt2
            .Update
        End With
    rs.Close
    Set rs = Nothing

End If

End Sub

The IF checks to see if the transfer has been done for the current date. If yes, it does nothing (to avoid duplicate). If no, then it implement the transfer.

--I hope this help!

---

IbrahimBadaru

Answer 12

is this access code or vb?

Answer 13

Hi X_x,

If you intends to use Access as an end database for a VB.NET application, you may try to ask the question at the VB.NET forum.

Have a look at the following link to let you get started about obtaining data from access and display it in DataGridView in VB.NET:

Using DataGridView With Access

Good day.

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 14

VBA.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.