How to do select query within vba, then update or insert

[lorirobn]

Hello,

I have a form that Inserts a record onto a table.

I would like to add code to do the following:
- first check to see if the record exists
- if it does exist, update it
- if it doesn't exist, insert it.

This table is not the record source of my form.

I tried coding sql in vba to do the SELECT, and after many rounds of it not working, I read on tek-tips that sql in this way only works for an action query, not a select query. Am not sure how else to do this logic. Set this sql up in a query? (then how do I reference the record count? and how would i do the update?). Or, should I use something like currentdb.execute? Would I have 3 separate statements? (find, then insert or update?)

I know I need to retrieve the record, check the record count, and if it's 0, add the record; if it's >0, update the record. Just not sure about the details on how to do so.

Appreciate any help!
many thanks in advance

 

[CautionMP]

lorirobn,
Something like this? It assumes that there is one field that is the primary key (a date field in this example) and the record details will be passed to the routine as Comma Seperated Value string (for testing I was using [tt]"DateTime,10/26/2005"[/tt]).

Sub RecordHandler(RecordPrimaryKey As Date, csvFieldNameValues As String)
On Error GoTo RecordHandler_Error
Dim MyDatabase As Database
Dim MyDAORecordset As DAO.Recordset
Dim intField As Integer
Dim MyData() As String
Dim sqlDAORecordset As String

MyData = Split(csvFieldNameValues, ",")
'MyData needs to be a pairing of field names and values
'so if the count is odd, exit the routine
'NOTE: UBound() returns a 0 based array so add 1 before testing
If (UBound(MyData) + 1) Mod 2 Then
  MsgBox "Invalid Field Value pairing passed to function.", vbCritical, "RecordHandler Error"
  Stop
End If

Set MyDatabase = CurrentDb
sqlDAORecordset = "SELECT * FROM tblDateTime WHERE DateTime=#" & RecordPrimaryKey & "#;"
Set MyDAORecordset = MyDatabase.OpenRecordset(sqlDAORecordset, dbOpenDynaset)
Select Case MyDAORecordset.RecordCount
  Case 1
  'There is a current record so update it
    MyDAORecordset.Edit
    For intField = 0 To UBound(MyData) Step 2
      MyDAORecordset.Fields(MyData(intField)) = MyData(intField + 1)
    Next intField
    MyDAORecordset.Update
  Case Else
  'There is no current record so create a new one
    With MyDAORecordset
      .AddNew
      For intField = 0 To UBound(MyData) Step 2
        MyDAORecordset.Fields(MyData(intField)) = MyData(intField + 1)
      Next intField
      .Update
    End With
End Select

RecordHandler_CleanUp:
MyDAORecordset.Close
Set MyDAORecordset = Nothing
Set MyDatabase = Nothing
Exit Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[lorirobn]

Hi Caution,

Thanks so much - I got it working! The select, the insert, and I will attempt the update next.

So I was wrong when I thought I read about not using SQL within VBA unless it's an action query. I didn't think that made sense.

Thanks for your help - greatly appreciated!

LR