Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: How to prevent a record from being deleted?

    I have 2 tables, tblProducts, tblOrders.

    I create a new order and add a product (productID) to tblOrders e.g. orderID 001 & productID 999.

    I would like Access to prevent the user (by the way of a message box) from deleting a product from tblProducts if the productID is currently added to an order in tblOrders.

    How can I go about this?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    define a relation using RI.
    do not use delete cascade
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Open the Relationships window (in Acc 2003: Tools --> Relationships...)
    2. Add both tables (TblProducts and TblOrders).
    3. Create the link (drag-drop) between the primary key of the master table (tblProducts in your case) and the foreign key of the child table (tblOrders).
    4. Double-click on the link to specify the type of relationship. In your case, it will be One (TblProducts) to Many (TblOrders).
    5. Click on the checkbox "Enforce Referential Integrity".
    6. Click on the "Create" button.
    Have a nice day!

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    Thanks guys, but maybe I didnt explain correctly, all the relationships between my tables are fine, I need this to work via a command button on a form.

    The user will select a product from a listbox (list1) displaying records from tblProducts, then when they press the command button, if the selected product has an orderId along side it in the table then I want a message to inform the user that the product has an order and cannot be deleted at that time.

    I found this piece of code from a database I created about 7 years ago, and Im sure it was the correct piece of code, as it was basically the same priniciple, but when I try to run it I get an error 3061 "too few parameters, expected 1" -
    Code:
    Dim delRS As Recordset, delString As String
    
    delstring = "select tblOrders.productID FROM tblOrders WHERE (((tblOrders.productid)='" & [Forms]![form1]![list1] & "'));"
    Set delRS = CurrentDb.OpenRecordset(delstring)
    If delRS.RecordCount > 0 Then
    MsgBox "You cannont delete this product at this time because it is related to an order on the database."
    Exit Sub
    End If

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is product id a number or a string/text value
    if numeric then you don't need the ' to encapsulate the value

    the error message you are getting suggests that there is no value for the SQL engine, probably in the where clause
    that would be easy enough to check if you put a breakpoint on the delstring assignment and check the resultant stuff is valid SQL

    you could do the similar function using the domain function dcount, which may be marginally more efficient
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    productID is numeric, autonumber.

    Ive removed the ' and still get the message.

  7. #7
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Did you put a breakpoint in like healdem suggested and check the actual sql statement that your code is generating? Most likely, you're not getting what you think you are so copy the resulting sql statement, paste it into a new MS access query and see what you get.

    Steve

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Quote Originally Posted by moss2076 View Post
    Thanks guys, but maybe I didnt explain correctly, all the relationships between my tables are fine, I need this to work via a command button on a form.

    The user will select a product from a listbox (list1) displaying records from tblProducts, then when they press the command button, if the selected product has an orderId along side it in the table then I want a message to inform the user that the product has an order and cannot be deleted at that time.

    I found this piece of code from a database I created about 7 years ago, and Im sure it was the correct piece of code, as it was basically the same priniciple, but when I try to run it I get an error 3061 "too few parameters, expected 1" -
    [CODE]Dim delRS As Recordset, delString As String
    Hi Moss,

    In my databases where there are Products and Orders I simply don't give the user the access to Delete a product. The only way my users can make a product disappear is to actually pull that count into an Order. And it constantly reduces the amount. I just wouldn't give them the ability to actually Delete a product. That's just my way of looking at it.
    Will the command button actually be a button to Delete products?

    Have a nice one,
    Bud

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd still declare the relationship
    then trap for the approipriate error message when the app triesd to delete a row and finds it cannot. then dispaly an appropriate error message
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Aug 2004
    Posts
    364
    Thanks guys I got it to work

    Code:
    Dim delRS As Recordset, delString As String
    
    delString = "select tblOrders.productID FROM tblOrders WHERE (((tblOrders.productID)=" & [Forms]![form1]![List1] & "));"
    
    Set delRS = CurrentDb.OpenRecordset(delString)
    
    If delRS.RecordCount > 0 Then
    
    MsgBox "You cannont delete this product at this time because it is related to an Customer Order on the database."
    
    Exit Sub
    
    End If
    
    DoCmd****nSQL "DELETE tblproducts.productID, tblproducts.* FROM tblproducts WHERE (((tblproducts.productID)=[Forms]![form1]![list1]));"
    
    Me.List209.Requery

Posting Permissions

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