Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No updating (Access 2007)

    Edited by HansV to enable breaking long lines

    Hi Hans ,
    In my last post I was struggling with the error message "No current record". Meanwhile i've found the reason for that: I made a lookup for a value that did'nt exists. I solved the problem by using: On error resume next. But now i have another problem.
    The code below is used to do the following :
    If in a orderitem that already is entered, the articlenr has to be changed because the wrong articlenumber was entered, the stock of the article that will be replaced has to be corrected. The reason therefore is, that by entering the orderitem, the quantity ordered is subtracted of the stock of the article. But when the article has to be replaced, this quantity has to be put back in the stock of this article. Therefore i have the following code in the AfterUpdate event of the subfrmOrders. When I run this code everything seems to go well, but when i go after that to the table tblArticles, the stock of the article isn't changed.

    Private Sub Form_AfterUpdate()

    On Error GoTo Nabijwerken_Fout

    Dim RsNaleveringen As DAO.Recordset
    Dim RsKlantArt As DAO.Recordset
    Dim RsArtikelen As DAO.Recordset
    Dim QD As QueryDef
    Dim Aantal As Integer
    Dim waarde As Integer
    Dim Stock As Integer
    Dim txtArtikel As String
    Dim Toename As Currency
    Dim Ordernr As String
    Dim txtVolgnummer As String
    Dim Nr As String
    Dim strSQL As String
    Dim BijgewerkteVoorraad As Integer


    strSQL = "PARAMETERS Artikelnr Text;Select tblOrderdetails.Order_ID, tblOrderDetails.Artnr, tblOrderDetails.Geleverd, tblOrderdetails.Nalev, tblOrderDetails.Eenheidsprijs, tblOrderdetails.Btw, tblOrderdetails.Korting, tblOrders.Bedrag, tblOrders.Bedrag2, tblOrders.Saldo,tblOrders.Leveringsbon, tblArtikelen.Voorraad From tblArtikelen Inner Join (tblOrders INNER JOIN tblOrderdetails ON tblOrders.Order_ID = tblOrderDetails.Order_ID) On tblArtikelen.Artnr = tblOrderdetails.Artnr Where ((tblOrderDetails.Nalev > 0) AND (tblOrders.Leveringsbon = No) AND (tblOrderdetails.Artnr=""" & txtArtnr & """" & ")) ORDER BY tblOrderdetails.Order_ID;"

    Set WS = DBEngine.Workspaces(0)
    Set db = CurrentDb
    Set RemoteDB = WS.OpenDatabase(acbGetLinkPath("tblOrders_ID"), False, False, "") ' this is a sub that makes the linking to the backend possible
    Set RsArtikelen = RemoteDB.OpenRecordset("tblArtikelen", dbOpenTable) ' opening a recordset of type dbOpentable based on the table tblArticles
    RsArtikelen.Index = "Primarykey"


    If GewijzigdArtikelnr Then In the orderitem the articlenr is replaced bij anotherone
    WS.BeginTrans The stock ( = Voorraad) of the replaced article has to be corrected
    BlnIntTrans = True 'ReedsAanwezigeVoorraad = stock of the article before it was replaced
    ReedsAanwezigeLevering = Qty of the article that reserved for the orderitem
    BijTeWerkenVoorraad = the corrected value of the stock of the replaced article
    ReedsAanwezigArtikel - Articlenumber of the replaced article
    BijTeWerkenVoorraad = ReedsAanwezigeVoorraad + ReedsAanwezigeLevering
    RsArtikelen.Seek "=", ReedsAanwezigArtikel
    If Not RsArtikelen.NoMatch Then
    MsgBox ("The record with artikelnr " & RsArtikelen!Artnr & " is found")
    RsArtikelen.Edit
    RsArtikelen("Voorraad") = BijTeWerkenVoorraad
    MsgBox (" The stock is now " & RsArtikelen!Voorraad) 'seems to be correct during runtime , however isn't the case in the table tblArticles
    RsArtikelen.Update
    RsArtikelen.Close
    Else
    MsgBox ("De record werd niet gevonden")
    End If

    If ReedsAanwezigeNaLevering > 0 Then ' is > 0 when the stock of the article wasn't sufficient the moment the order was entered, but this is in the test not the case so can be ignored
    RsNaleveringen.Seek "=", Order_ID, ReedsAanwezigArtikel
    If Not RsNaleveringen.NoMatch Then
    RsNaleveringen.Delete
    End If
    End If
    If ReedsAanwezigeVoorraad = 0 And ReedsAanwezigeLevering > 0 Then
    Nr = Me![Order_ID]
    On Error Resume Next
    db.QueryDefs.Delete ("Mijnquery")
    On Error GoTo Nabijwerken_Fout
    Set QD = db.CreateQueryDef("Mijnquery", strSQL)
    QD.Parameters("Artikelnr") = ReedsAanwezigArtikel
    Set Rs = QD.OpenRecordset()
    On Error Resume Next
    Rs.MoveLast
    Aantal = Rs.RecordCount
    On Error GoTo Nabijwerken_Fout
    If Aantal >= 1 Then
    Rs.MoveFirst
    Do While Not Rs.EOF
    Ordernr = Rs![Order_ID]
    If Rs!Nalev <= Rs!Voorraad Then 'de nalev van die record is hoogstens gelijk aan de beschikbare voorraad
    Rs.Edit
    Rs!Geleverd = Rs!Geleverd + Rs!Nalev ' wat niet kon worden geleverd kan nu wel
    Rs!Voorraad = Rs!Voorraad - Rs!Nalev ' de beschikbare voorraad overeenkomstig verlagen
    Toename = CCur(CLng(Rs!Eenheidsprijs * Rs!Nalev * (1 - Rs!Korting) * (1 + Rs![BTW]) * 100) / 100)
    Rs!Bedrag2 = Rs!Bedrag2 + Toename
    Rs!Bedrag = RoundUP(Rs!Bedrag + Toename, 2)
    Rs!Saldo = RoundUP(Rs!Saldo + Toename, 2)
    Rs!Nalev = 0
    Rs.Update
    Rs.Close
    Set RsNaleveringen = RemoteDB.OpenRecordset("tblNaleveringen", dbOpenTable)
    RsNaleveringen.Index = "Ordernr"
    RsNaleveringen.Seek "=", Ordernr, txtArtikel
    If Not RsNaleveringen.NoMatch Then
    RsNaleveringen.Delete
    End If
    ElseIf Rs!Nalev > Rs!Voorraad Then ' De nalevering van die record overtreft de beschikbare voorraad
    Rs.Edit
    Rs!Nalev = Rs!Nalev - Rs!Voorraad
    Rs!Geleverd = Rs!Geleverd + Rs!Voorraad
    Toename = CCur(CLng(Rs!Eenheidsprijs * Rs!Voorraad * (1 - Rs!Korting) * (1 + Rs![BTW]) * 100) / 100)
    Rs!Bedrag2 = Rs!Bedrag2 + Toename
    Rs!Bedrag = RoundUP(Rs!Bedrag + Toename, 2)
    Rs!Saldo = RoundUP(Rs!Saldo + Toename, 2)
    Rs!Voorraad = 0
    Rs.Update
    RsNaleveringen.Seek "=", Ordernr, txtArtikel
    If Not RsNaleveringen.NoMatch Then
    rst.Edit
    rst!Nalev = rst!Nalev - Stock
    rst.Update
    End If
    Exit Do
    End If
    Rs.MoveNext
    DoEvents
    Loop
    End If
    Rs.Close
    End If
    Set RsKlantArt = RemoteDB.OpenRecordset("tblKlantArt", dbOpenTable)
    Rs.Seek "=", Klnt_ID, ReedsAanwezigArtikel
    If Not Rs.NoMatch Then
    If Rs("frequentie") > 1 Then
    Rs.Edit
    Rs("lsteOrdernr") = Rs("Vlsteordernr")
    Rs("klnt_ID") = Klnt_ID
    Rs("Artnr") = ReedsAanwezigArtikel
    Rs("Qty") = ReedsAanwezigeQty
    Rs("Frequentie") = Rs("frequentie") - 1
    Rs.Update
    Else
    Rs.Delete
    End If
    End If
    WS.CommitTrans
    BlnIntTrans = False
    End If




    Nabijwerken_Afsluiten:

    vervangen = False
    VerlaagdeNaLev = False
    NewStockNaLevNull = False
    StockNullNaLevNull = False
    NewStock = False
    StockNullNewNaLev = False
    VerhoogdeNaLev = False
    weggewerkt = False
    Verwijderen = False
    GewijzigdArtikelnr = False
    leeg = False
    Gewijzigd = False
    Bijpassen = False
    Exit Sub

    Nabijwerken_Fout:

    If BlnIntTrans = True Then
    WS.Rollback
    End If
    Mededeling = foutbericht("Na bijwerken van het formulier subfrmOrders", "subfrmOrders", Err)
    Resume Nabijwerken_Afsluiten

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No updating (Access 2007)

    Apparently you don't require that variables must be declared explicitly. Hence you don't receive a warning if you use a variable that hasn't been defined.

    I suspect that the variable rst should be RsNaleveringen in the following code:

    If Not RsNaleveringen.NoMatch Then
    rst.Edit
    rst!Nalev = rst!Nalev - Stock
    rst.Update
    End If

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No updating (Access 2007)

    Dear Hans,
    Thank you very much for your help.
    However, the problem has to do with the excution of the following code snippet inside the After-Update event of subfrmOrders.
    The code begins by checking the boolean 'GewijzigdArtikelnr", that has to be true if in the orderitem the Artikelnr has been changed.
    In such a case the stock of the article that is replaced by a new one, must be corrected. The correction is formed by the addition of
    the amount of stock that the article possed on the moment of changing of the orderitem and the amount of units that was reserved for delivery.
    "BijTeWerkenVoorraad = ReedsAanwezigeVoorraad + ReedsAanwezigeLevering"
    After that the article in question is searched in the table tblArtikelen, and when found, the stock of it is changed to "BijTeWerkenVoorraad" and updated.

    If GewijzigdArtikelnr Then
    MsgBox ("gewijzigdartikel is true")
    WS.BeginTrans
    BlnIntTrans = True
    BijTeWerkenVoorraad = ReedsAanwezigeVoorraad + ReedsAanwezigeLevering
    RsArtikelen.Seek "=", ReedsAanwezigArtikel
    If Not RsArtikelen.NoMatch Then
    MsgBox ("the record with Artnr " & RsArtikelen!Artnr & " is found")
    RsArtikelen.Edit
    MsgBox (" The stock is now " & RsArtikelen!Voorraad)
    RsArtikelen("Voorraad") = BijTeWerkenVoorraad
    MsgBox (" The stock is now " & RsArtikelen!Voorraad)
    RsArtikelen.Update
    MsgBox (" The stock is now " & RsArtikelen!Voorraad)
    RsArtikelen.Close
    Else
    MsgBox ("De record werd niet gevonden")
    End If
    End If

    I have put some messages inside the code in order to get a better idea of what is going on, during runtime.
    All the messages gives me the expected answers. For example the last message just before "RsArtikelen.Close shows me the new value of the stock and this is exactly what it has to be. It gives me the idea that the stock for the article in question is indeed correctly changed. However, when i close the the project and look for the article in question in the table tblArtikelen, the stock isn't changed at all !!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No updating (Access 2007)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •