Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Stopping a record saving

    I have a form which displays records all with one field in common. The common field is not displayed.
    The user then uses a combo to select a new common field. (NewFGNo) and the data is entered into the the database using the insert into command.

    Sometimes a user wished to change qtys or something in the records for the new common field. (RM Stk No, Qty per Lot, I/P WF)

    I don't want this to change in the records that are attached to the orginal common field.

    I have tried me.undo but as I have moved between records it is to late they have already saved.

    Is there something I can put to stop the records saving after an update?

    Here is the code I currently have.

    Private Sub cmdS_CCpyBOM_Click()
    ' RMCount is the number of records returned by the query
    Amount = RMCount
    DoCmd.GoToRecord , , acFirst
    Do
    'Checking the new finished goods number has been entered
    If Len(NewFGNo & vbNullString) > 0 Then
    'Inserting the fields into BOM records
    DoCmd****nSQL "Insert into BOMs([FG Stk No],[RM Stk No],[Qty per Lot],[I/P WF]) Values (NewFGNo.Value,[RM Stk No].Value,[Qty per Lot].Value,[I/P WF].Value);"
    Else
    'sending an error if there isn't anything in the finished goods stock number
    MsgBox ("You need to add a FG Stock Number")
    End If
    'Adding 1 to the counter
    Counter = Counter + 1
    'Moving to the next record
    Recordset.MoveNext
    'Checking whether the counter equals the number of records repeating until it does
    Loop Until Counter = Amount
    'Undoing the changes made to the copied BOM
    Me.Undo
    DoCmd.Close
    End Sub

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Is there something I can put to stop the records saving after an update?
    Depends on the form. If it's bound, then no. AfterUpdate fires after the record has been updated. If this is the case, you need to put code on the BeforeUpdate event that will set the Cancel variable to True if your conditions are met.

    If your form is unbound, you will need to check the status of the records between the user updating the form and the updates being committed to the database.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    I have put the following code in

    Private Sub RM_Stk_No_BeforeUpdate(Cancel As Integer)

    If Me.Dirty = True Then
    Cancel = True
    End If

    End Sub

    but can not move from that field after changing it. Then the database crashed.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    In the BeforeUpdate event, Dirty will always be true if something on the form has changed. You need to carry out a specific text for the conditions mentioned in your original post.

    As an alternative, why not lock the text boxes that relate to the field you don't want updated for existing records?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jun 2011
    Posts
    4
    Hi

    Ok I understand the problem there.
    The issue issue I want the user to be able to change any of the fields but for when it is inserting as a new record (The insert into section of the code shown above)
    Maybe I have approached the whole form incorrectly.

    The data base contains materials that go into products.
    I have a table with a list of products and another with a list of materials.
    There is then a third table which takes the unique id from the products and the materials and links the 2.
    Some products will contain all the same materials but be packaged differently so have a different product name.
    What I would like is for the user to be able to copy the list of materials assigned to one product and assign these all to another product.

    The approach i took was to use a query based on the old products ID to get the list of materials. Then type in the new product ID in and individully insert each material into the third table with the new product ID.

    The issue I have now is the new product and materials are going in as I wanted but the old products materials are changing to match the new.

    If anyone has any ideas on a different approach it would be much appreciated I feel I have got a little lost in this now.

    Thanks
    Hannah

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Ah, I begin to see.

    What you have is effectively a manufacturing database. You have items that are consumed, items that are produced and the link between the two.

    If you take a leaf out of our old manufacturing system's book, you could save yourself a lot of trouble. Just have one table for items, regardless of whether they are consumed or produced. Then, in the BoM table, insert a field to flag whether that item is consumed or produced. You should also move some of the fields from the existing materials table to the BoM table (those that change according to the item being produced). That way, you can have default values appearing in the BoM table/form that are maintained in the item table (supplier, lot number, cost, etc) but can still be over-written when the BoM for that FG item is created or maintained. Also, your users will be able to amend an existing BoM without affecting the others or the items.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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