Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Groningen, Netherlands
    Posts
    1

    Red face Unanswered: Help with update

    I'm trying to update a stored value in a table using the following code. It gets the criteria from a subform. It works well when ther is only one field in the subform but only updates the first record when there are multiple rows in the subform. Can anyone take a look at the code and tell me what I'm doing wrong?

    Private Sub cmdAddRec_Click()

    Dim frm As Form

    Set frm = [Forms]![2,3,1 Binnengekomen Bestellingen]

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    Set db = CurrentDb
    strSQL = "SELECT * FROM Artikelen WHERE Artikelnr = &_
    ""& frm![te verwachten bestelling subform]!Artikelnr & " ;"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    rs.MoveFirst
    Do While Not rs.EOF
    rs.Edit
    rs.Fields("Voorraad").Value = frm![te verwachten bestelling subform]!Aantal + frm![te verwachten bestelling subform]!Voorraad
    rs.Update
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    End Sub

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    you should have posted this in MS Access thread

    is this correct
    strSQL = "SELECT * FROM Artikelen WHERE Artikelnr = &_
    ""& frm![te verwachten bestelling subform]!Artikelnr & " ;"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    do you filter a Table "Artikelen" by field "Artikelnr", in this case you will get a recordset with same "Artikelnr" field, and I'm not shure about
    frm![te verwachten bestelling subform]!Artikelnr , you get a criterya from the subform, I suppose that you should take criteria from parent form's controll, describe your task more carefully.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

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