0

I cannot seem to figure out the best way to identify whether my record table already has the unique 3-column combination of the data using VBA.

My table is as follows:

CREATE TABLE sometable(
col1 NUMERIC,
col2 NUMERIC,
col3 NUMERIC,
col4 NUMERIC,
Primary Key(col1, col2, col3)
);

Now in my form I'm adding values into the table (ie. user fills up 3 fields, clicks an add button and it inserts to the table).

If row of data is already there, pressing the add button does nothing.

What I want to achieve is to check (using VBA) whether such row already exists (based on the uniqueness of col1 & col2 and col3) SUM up the col4 of an already existing record with the new addition.

For example if my already existing data is something like:

col1 col2 col3 col4
1    2    3    10
1    3    3    20

and user wants to add:

1 1 1 10

It would simply add a new row to the table. However, if he wants to add:

1 2 3 10

It would result in table:

col1 col2 col3 col4
1    2    3    20 'added up col4 here
1    3    3    20
1    1    1    10

The reason why I don't want to have an Auto-ID incrementing as col0 is because I would have to check the values in col1, col2 and col3 anyway to determine whether the new data should be added or simply re-calculated.

emihir0
  • 1,200
  • 3
  • 16
  • 39
  • In enterprise databases this is called a `MERGE`, or Upserting. in mySQL it's `on duplicate key update`. I've not been able to find an equivalent in msAccess. All I could think to do would be to code it as an insert, trap for duplicate key error and then, apply it as an update statement. (or vice versa code update when record not found then insert) – xQbert May 12 '15 at 13:16
  • How would I trap for a duplicate key error? Currently I'm getting no error whatsoever when I insert new row of data which already exists - it simply does nothing (no error). – emihir0 May 12 '15 at 13:18
  • "Does nothing.. including not insert the new row?" The connection object should have an error method which you should check after the update. http://stackoverflow.com/questions/5389634/vba-error-handling-on-adodb-connection-open is an example of some error handling – xQbert May 12 '15 at 13:50

2 Answers2

0

You can do this with a single query:

Update and Append Records with One Query

By Alan Biggs

Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld.

Follow these steps:

  1. Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld.

  2. Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld.

  3. Select all the fields from tblOld and drag them onto the QBE grid.

  4. For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld.

  5. Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.)

  6. Run the query and you'll see the changes to tblNew are now in tblOld.

This will only add records to tblOld that have been added to tblNew. Records in tblOld that aren't present in tblNew will still remain in tblOld.

Gustav
  • 53,498
  • 7
  • 29
  • 55
0

I found 2 general ways of handling this. (1) would include error handling - which I find to be a bad practise. (2) would include DCount function to find the row of data first, then an if condition to determine whether UPDATE or INSERT should be used.

Hence I used (2) as follows:

If DCount("*", "sometable", "[col1]= " & Me.col1.value & " AND [col2] = " & Me.col2.value & " AND [col3] = " & Me.col3.value) = 0 Then
    CurrentDB.Execute "INSERT ..."
Else
    CurrentDB.Execute "UPDATE ..."
End If

The (1) solution would be something like:

On Error Resume Next
CurrentDb.Execute "INSERT ...", dbFailOnError
If Err.Number = 3022 Then
    Err.Clear        
    CurrentDb.Execute "UPDATE ...", dbFailOnError
End If
emihir0
  • 1,200
  • 3
  • 16
  • 39