If record exisits then update, else addnew

[EndlessNights]

What is the best way of handling this situation in Access via VBA?

Currently I am planning on openning a recordset based on criteria that determines the unique record, check if a record exists, if it does update some data fields of the record (e.g. !field1 = field1 + 1), if it doesn't exist create a new one.

I am assuming that this is likely a routine that is quite common... Is there a "best of" example for how to handle this situation.

Thanks...

 

[SgtJarrow]

while maybe routine, it is no means common....there are many ways to skin this cat depending on your situation. I have an example of this for importing files and updating or adding new records...

If you can give a bit more information, perhaps we can help you find the solution you need....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[EndlessNights]

This is where I am at so far. The un-commented part works fine - but I cannot get the recordset filter to work properly... keeps saying "Operation is not supported for this type of object." (It is the second filter that I ultimately need working - but for testing the first one doesn't work either)

I am thinking I may need to re-write the app in ADO...

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub CreatePlayerStats(PlayerYear, PlayerMonth, PlayerAlias As String, PlayerWins As Integer, PlayerPoints As Integer, PlayerEmail)
Dim rsPlayerStats As DAO.Recordset
Set rsPlayerStats = CurrentDb.OpenRecordset("PlayerStats", dbOpenTable)
' rsPlayerStats.Filter = "PlayerAlias = '" & PlayerAlias & "'"
' rsPlayerStats.Filter = "PlayerYear = '" & PlayerYear & "' and PlayerMonth = '" & PlayerMonth & "' and PlayerAlias = '" & PlayerAlias & "'"
' If Not rsPlayerStats.EOF Then
' rsPlayerStats!PlayerWins = rsPlayerStats!PlayerWins + PlayerWins
' rsPlayerStats!PlayerPoints = rsPlayerStats!PlayerPoints + PlayerPoints
' rsPlayerStats!PlayerTourneys = rsPlayerStats!PlayerTourneys + 1
' If Len(PlayerEmail) <> 0 Then
' rsPlayerStats!PlayerEmail = PlayerEmail
' End If
' rsPlayerStats.Update
' Else
rsPlayerStats.AddNew
rsPlayerStats!PlayerYear = PlayerYear
rsPlayerStats!PlayerMonth = PlayerMonth
rsPlayerStats!PlayerAlias = PlayerAlias
rsPlayerStats!PlayerWins = PlayerWins
rsPlayerStats!PlayerPoints = PlayerPoints
rsPlayerStats!PlayerTourneys = 1
If Len(PlayerEmail) <> 0 Then
rsPlayerStats!PlayerEmail = PlayerEmail
End If
rsPlayerStats.Update
' End If
Set rsPlayerStats = Nothing
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Thanks...

 

[SgtJarrow]

Why not try this:

Sub CreatePlayerStats(PlayerYear, PlayerMonth, PlayerAlias As String, PlayerWins As Integer, PlayerPoints As Integer, PlayerEmail)
Dim rsPlayerStats As DAO.Recordset
Set rsPlayerStats = CurrentDb.OpenRecordset(&quot;PlayerStats&quot;, dbOpenTable)
' rsPlayerStats.FindFirst = &quot;[PlayerAlias] = '&quot; & PlayerAlias & &quot;' And [PlayerYear] = '&quot; & PlayerYear & &quot;' And [PlayerMonth] = '&quot; & PlayerMonth & &quot;'&quot;
' If rsPlayerStats.NoMatch Then
rsPlayerStats.AddNew
rsPlayerStats!PlayerYear = PlayerYear
rsPlayerStats!PlayerMonth = PlayerMonth
rsPlayerStats!PlayerAlias = PlayerAlias
rsPlayerStats!PlayerWins = PlayerWins
rsPlayerStats!PlayerPoints = PlayerPoints
rsPlayerStats!PlayerTourneys = 1
If Len(PlayerEmail) <> 0 Then
rsPlayerStats!PlayerEmail = PlayerEmail
End If
rsPlayerStats.Update
' Else
rsPlayersStats.Edit
' rsPlayerStats!PlayerWins = rsPlayerStats!PlayerWins + PlayerWins
' rsPlayerStats!PlayerPoints = rsPlayerStats!PlayerPoints + PlayerPoints
' rsPlayerStats!PlayerTourneys = rsPlayerStats!PlayerTourneys + 1
' If Len(PlayerEmail) <> 0 Then
' rsPlayerStats!PlayerEmail = PlayerEmail
' End If
' rsPlayerStats.Update
' End If
Set rsPlayerStats = Nothing
End Sub


Should be pretty close.....you are searching for the matching record. If found, update, if no addnew

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[EndlessNights]

Yes - that looks like a good way.

However I am receiving a Compile Error: Argument not optional on the .FindFirst

Thanks Robert.

 

[EndlessNights]

Ok - I have gotten this code to work properly... found it easier to re-write in ADO

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub CreatePlayerStats2(PlayerYear, PlayerMonth, PlayerAlias As String, PlayerWins As Integer, PlayerPoints As Integer, PlayerEmail)

strSQL = &quot;SELECT * FROM PlayerStats Where PlayerYear = '&quot; & PlayerYear & &quot;' and PlayerMonth = '&quot; & PlayerMonth & &quot;' and PlayerAlias = '&quot; & PlayerAlias & &quot;'&quot;

Set rsPlayerStats = New ADODB.Recordset
Set rsPlayerStats.ActiveConnection = CurrentProject.Connection
rsPlayerStats.CursorType = adOpenDynamic
rsPlayerStats.LockType = adLockOptimistic
rsPlayerStats.Open strSQL
Debug.Print strSQL


If Not rsPlayerStats.EOF Then
rsPlayerStats!PlayerWins = rsPlayerStats!PlayerWins + PlayerWins
rsPlayerStats!PlayerPoints = rsPlayerStats!PlayerPoints + PlayerPoints
rsPlayerStats!PlayerTourneys = rsPlayerStats!PlayerTourneys + 1
If Len(PlayerEmail) <> 0 Then
rsPlayerStats!PlayerEmail = PlayerEmail
End If
rsPlayerStats.Update
Else
rsPlayerStats.AddNew
rsPlayerStats!PlayerYear = PlayerYear
rsPlayerStats!PlayerMonth = PlayerMonth
rsPlayerStats!PlayerAlias = PlayerAlias
rsPlayerStats!PlayerWins = PlayerWins
rsPlayerStats!PlayerPoints = PlayerPoints
rsPlayerStats!PlayerTourneys = 1
If Len(PlayerEmail) <> 0 Then
rsPlayerStats!PlayerEmail = PlayerEmail
End If
rsPlayerStats.Update
End If
Set rsPlayerStats = Nothing

End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Do you see anything that needs improving?
Thanks for your help, Robert...