Unanswered: Adding/Editing Records based on certain factors..
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 .
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.
If rst.RecordCount = 0 Then
'CODE TO ADD NEW RECORD
'CODE TO UPADTE EXISTING RECORD
rst!FieldName = FormFieldName
Set rst = Nothing
MsgBox Err.Number & " - " & Err.Description
Set rst = Nothing