Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2009
    Posts
    185

    Question Unanswered: Don't how to update.

    I have a lookup field apID where I lookup up a plane 's name and number of flights and last flight date.
    Now if a user edits a record and changes the apid I need to update the original apid. but it's not the current record anymore so how do I update the original apid fields. I have the original apID's value saved and in formupdate
    I update the current(new) flights count here in apid

    I have a if statement
    if iorigipID is <> ipID then
    I need to update number of flights field in the original here
    but don't know how.
    I need to read the original's record then update two of it's fields.
    End if

  2. #2
    Join Date
    Jul 2009
    Posts
    185
    icycles = DLookup("ibtotalCycles", "battery", "abid=1")
    MsgBox "icycles=" & icycles
    DoCmd****nSQL "Update battery set ibTotalCycles=ibtotalCycles -1 where abid=1" Where **** is "runSql
    MsgBox "ibTotalCycles=" & ibTotalCycles
    This seems to sort of work but I get a "About to update prompt ...."
    Also if I answer no it traps into debug on that line "user aborted update.?
    Last edited by Bob2119; 07-19-09 at 00:13.

  3. #3
    Join Date
    Jul 2009
    Posts
    185
    The following seems to work. But what errors should I can I check for?


    ' Don't forget the Dim statements at begining of the sub
    ' Dim db As DAO.Database
    ' Dim strSql As String
    strSql = "Update battery set ibTotalCycles=ibtotalCycles -1 where abid="
    strSql = strSql & iorigiBatteryID
    MsgBox "Update strSql=" & strSql
    Set db = DBEngine(0)(0)
    db.Execute strSql, dbFailOnError
    Set db = Nothing
    Last edited by Bob2119; 07-19-09 at 13:35.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is this apID part of a relationship??

    Seems to me that you just need Cascade Update Related Fields switched on for the relationship.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jul 2009
    Posts
    185
    I am enter data to a flight table and apID is the Planes table which is related. I don't understand what a Cascade update is.
    I read the help and I don't think it applies as apid is a autonumber field. and I am changing a field in a different record than the current record.
    In doing the edit and changing the current planes ID I need to update the orig Planes ID number of flights by less one.

    Quote Originally Posted by StarTrekker
    Is this apID part of a relationship??

    Seems to me that you just need Cascade Update Related Fields switched on for the relationship.
    Last edited by Bob2119; 07-19-09 at 13:35.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so you want to decrement a number in a record.

    I'd just use a recordset:

    Code:
    dim rs as Recordset
    set rs = CurrentDB.OpenRecordset("SELECT * FROM Battery WHERE apID = " & TheapIDToEdit)
    If rs.RecordCount > 0 Then
       rs.Edit
       rs!ibTotalCycles = rs!ibTotalCycles - 1
       rs.Update
    End If
    set rs = Nothing
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jul 2009
    Posts
    185
    Thak that looks easy.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hopefully it is

    See if it works for you.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jul 2009
    Posts
    185
    I have but I get a Compile Error: method or data member not found.
    When I type rs.edit I only get Editmode but that does work either.
    I'll searched the net and it looks correct? What have I missed.
    'Don't forget the Dim rs As Recordset in the sub's Dim statements
    Debug.Print "Select * From Battery Where apID =" & iorigiBatteryID
    Set rs = CurrentDb.OpenRecordset("Select * From Battery Where apID =" & iorigiBatteryID)
    If rs.RecordCount > 0 Then
    rs.Edit
    rs!ibTotalCycles = rs!ibTotalCycles - 1
    rs!sCurrentMode = "????"
    rs!dLastFlown = "01/01/01"
    rs.Update
    End If
    Set rs = Nothing

  10. #10
    Join Date
    Jul 2009
    Posts
    185
    I forgot to say the break is in rs.edit?

  11. #11
    Join Date
    Jul 2009
    Posts
    185
    Should use Dim rs as DAO.Recordset vs just recordset?

  12. #12
    Join Date
    Jul 2009
    Posts
    185
    I am also using Access 2003. Some of what I have read talks about there is differences but don't say exactly what they are.
    a quote from one of the internet hits.
    I had him check the link to Microsoft DAO 3.6 Object Library under references, but it
    still won't allow the method "recordset.edit". I am not sure why. I
    don't have 2003 on my machine. I asked him to retype the line from VBA
    and "edit" does not show up as a method in the autotype feature of the
    VBA editor for the recordset. The code worked fine under Access 2000
    and Access XP. Has VBA been drastically changed.

  13. #13
    Join Date
    Jul 2009
    Posts
    185
    Changing the Dim to rs as DAO.Recordset the vba autotype now has edit. Now I get a Run time error '3061': Too few parameters. Expected 1.On the
    Set rs = CurrentDb.OpenRecordset(strSql) I print out the strSql and it looks ok to me. it is
    CurrentDB.OpenRecordset strSql = Select * From Battery Where apID=5
    I am at a lose what I have found on the internet this should work.

  14. #14
    Join Date
    Jul 2009
    Posts
    185
    If I change strSql to "Select * From Battery" the code works Only has a recordcount of one. And updates record apid=1 . What is wrong with with my strSql statement?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Bob2119
    Should use Dim rs as DAO.Recordset vs just recordset?
    there is a potentail pitfall in as much as both the DAO and ADO data objects can coexist in VBA,
    ADO is the newer approach but DAO is still used.
    when copying code from t'net you need to be clear in your (and the programs) mind as to which onject library you are using

    I'd suggest you use the helpfile to get up to speed with these two
    if you don't explicitly declare it Access 2000 and later will default to ADO.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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