Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    message box (Access 2000)

    i have the folloing sql :
    StrSQL = "UPDATE qryOrderDetails SET Tree0 = Tree0 - crates "
    How could i extend the code to have a message box " Attention" when Tree0 > Tree0 - crates and stop the process of substraction ?

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

    Re: message box (Access 2000)

    If you really want a message box, you shouldn't use SQL but open a recordset on qryOrderDetails and loop through the records.

    The condition Tree0 > Tree0 - crates is strange: it is equivalent to crates > 0, so you'd stop whenever crates is greater than 0. Don't you mean that you want to avoid updating if Tree0 - crates would be less than 0, i.e. if crates > Tree0? If so, you could modify the SQL (but you wouldn't get a message box):

    StrSQL = "UPDATE qryOrderDetails SET Tree0 = Tree0 - crates WHERE crates <= Tree0"

  3. #3
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: message box (Access 2000)

    It is perfect,your suggestion,in this way i forbid inadmitted substractions, i will use it now.I think i should also need a warning system that substraction will not be carried out under these conditions.How could i open the recordset to find if crates >Trees0 and exit sub in that case?

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

    Re: message box (Access 2000)

    If you exit the sub, the remaining records will not be updated, even if crates < Tree0 for those records. Is that what you intend? Or does qryOrderDetails return only one record?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: message box (Access 2000)

    Oh yes, you are right.I You see my problem better than me.But i do not know how to proceed now.the qryOrderdetails returns more than one record.The only way out is perhaps to have a preliminary check of the query and not to begin the substraction at all, if something is not in order with the crates.is it possible?

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

    Re: message box (Access 2000)

    The SQL I posted higher up in this thread will update only the correct records.

    An alternative would be this code using DAO:

    Sub UpdateTree0()
    Dim dbs As DAO.Database
    Dim rst As DAO.RecordSet

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryOrderDetails", dbOpenDynaset)

    Do While Not rst.EOF
    If rst!crates > rst!Tree0 Then
    MsgBox "A record with OrderID = " & rst!OrderID & " was not updated.", vbInformation
    Else
    rst.Edit
    rst!Tree0 = rst!Tree0 - rst!crates
    rst.Update
    End If
    rst.MoveNext
    Loop

    ExitHandler:
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: message box (Access 2000)

    That is it. The perfect solution. Thank you and congratiulations to all from this Lounge

Posting Permissions

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