Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Question Unanswered: Adding/Editing Records based on certain factors..

    Hi people,

    Currently I'm stuck trying to get Access to automatically change/add records in a table depending on info entered in by the user in a form.

    Currently the form is linked to a table that contains info on deliveries that have taken place. This includes the date of delivery, where the delivey has occured, what has been delivered and the quantity.

    What I need it to be able to do, is when the user enters in a new delivery the form should check a different table that holds info on current stock levels in each location and either add a new record or modify an existing one. For example, if the stock levels table already contains info that a location still has X amount of Y product and the user has just created a new delivery that says more of Y product has been delivered to the same location that quantity should be added on to the record in the stock levels table. If, however, there is no reference to a location having any of Y product a new record needs to be created listing the amount that has been delivered.

    So far I've managed to use an append query to add new records to the stock level table but it adds records even if there is reference to a location still haveing X amount of Y stock.

    Sorry for the long post but I could think of no other way of explaining my problem .

    Cheers for any help in the matter,
    Essicutr
    Last edited by Essicutr; 01-14-03 at 07:33.

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: Adding/Editing Records based on certain factors..

    "when the user enters in a new delivery the form should check a different table that holds info on current stock levels in each location and either add a new record or modify an existing one"

    You need to do come coding in the AfterInsert event of the form that will check the other table for an existing record. If it finds one, it will modify it else it will add a new record. Since you are dealing with inventory, it would be a good idea to add some transaction management.

    Somthing like this:

    Private Sub Form_AfterInsert()
    Dim wsp As Workspace, dbs As Database, rst As Recordset, sSQL As String

    On Error GoTo sub_err

    ' Return reference to default Workspace object.
    Set wsp = DBEngine.Workspaces(0)

    ' Return reference to current database.
    Set dbs = CurrentDb

    ' Create Recordset object.
    sSQL = "SELECT * FROM TABLE WHERE FIELD = FIELD"
    Set rst = dbs.OpenRecordset(sSQL)

    ' Start of transaction.
    wsp.BeginTrans

    If rst.RecordCount = 0 Then
    'CODE TO ADD NEW RECORD
    rst.AddNew
    Else
    'CODE TO UPADTE EXISTING RECORD
    rst!FieldName = FormFieldName
    End If
    rst.Update
    wsp.CommitTrans
    rst.Close
    Set rst = Nothing
    Exit Sub

    proc_err:
    wsp.Rollback
    MsgBox Err.Number & " - " & Err.Description
    rst.Close
    Set rst = Nothing
    End Sub

  3. #3
    Join Date
    Jan 2003
    Posts
    4
    Cheers for the help FirstAndGoal4. I'll have a good go over that code and see what I can do.

Posting Permissions

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