Using rs.FindFirst with SQL Back End

[winshent]

Hi

I'm migrating a backend to SQL Server and a piece of my code is no longer working. The table structure is exactly the same, but it now fails when inserting a a new record to a recordset using .addnew

Here is the code:

Public Function GenerateBlankDates(ClientType As String, ClientID As Long) As Boolean
  
  On Error GoTo PROC_ERR
  
  Dim db As DAO.Database
  Dim rsCDT As DAO.Recordset
  Dim rsCD As DAO.Recordset
  
  Dim SQL As String
  
  Set db = CurrentDb
  SQL = "SELECT ClientDateTypeID FROM ClientDateType WHERE ClientType='" & ClientType & "'"
  Set rsCDT = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
  
  If Not rsCDT.BOF And Not rsCDT.EOF Then
    rsCDT.MoveFirst
  
    SQL = "SELECT ClientID, ClientDateTypeID, dtDate FROM ClientDates"
    Set rsCD = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    Do Until rsCDT.EOF
      With rsCD
        rsCD.FindFirst "ClientDateTypeID=" & rsCDT!ClientDateTypeID
        If rsCD.NoMatch Then
          '## FAILS HERE ##
          rsCD.AddNew
            !ClientDateTypeID = rsCDT!ClientDateTypeID
            !ClientID = ClientID
            !dtDate = 0
          rsCD.Update
        End If
      End With
      rsCDT.MoveNext
    Loop
    GenerateBlankDates = True
  Else
    MsgBox "There are no ClientDateTypes for the client type: " & ClientType
  End If

PROC_EXIT:
  Exit Function
  
PROC_ERR:
  MsgBox Err.Number & ": " & Err.Description
  Resume Next
  Resume
  
End Function

On the line before .addnew... when hovering over '!ClientDateTypeID =', this contains a value when I assume it should be NULL

!ClientDateTypeID = rsCDT!ClientDateTypeID

So, are .FindFirst and .NoMatch compatible when using a SQL Server back end ?

 

[Steve R.]

As a quick guess. Are any of your fields identified below a PrimaryKey? If so, do not assign a value to the PrimaryKey, that is done automatically by SQL Server.

rsCD.AddNew
            !ClientDateTypeID = rsCDT!ClientDateTypeID
            !ClientID = ClientID
            !dtDate = 0
          rsCD.Update

So, are .FindFirst and .NoMatch compatible when using a SQL Server back end ?

Yes. I have used them.

 

[Steve R.]

Since Pat brought this up (append query). This is what I have used as an append query with SQL server as the back-end. Note: No primary key is referenced.

    Rem -------------------------------- ADD NEW RECORD Block ------------------------------------------------
    If OKtoProceed00 Then
        Dim RSTnew As DAO.Recordset
        Set RSTnew = CurrentDb.OpenRecordset("SELECT * FROM " & csInspectionTable, dbOpenDynaset, dbAppendOnly + dbSeeChanges + dbFailOnError)
        RSTnew.AddNew
            RSTnew!PermitNumber = Me.PermitNumber01
            ..............................
            RSTnew!ErrorFlag = False
        RSTnew.Update   
        ....... ........................... Subsequent code removed
        End If

 

[Steve R.]

@Steve, that isn't an append query. It is DAO code and is what the OP is already using. This is an append query:
In winshent's case the select part should join to the lookup table using a left join with criteria that looks for null in the primary key to indicate that no matching record was found. So the insert only happens if no record was found in the lookup table.

Learn something all the time. Thanks for the clarification.