Select Query Intermittent Error: value you entered isn't valid for this field

[RuralGuy]

Great point Ajax. Good eye.

[ssanfu]

My modifications:

    Dim rst As dao.Recordset
    Dim iVal As Integer
    Dim iVal2 As String
    Dim sSQL As String

    iVal = Nz(Me.cbointReportID, 0)

    iVal2 = Nz(Me.cbovarReportTitle, "")

    sSQL = "SELECT *"
    sSQL = sSQL & " FROM dbo_tbl_Custom_Subscription_Subscriptions"
    sSQL = sSQL & " WHERE intReportID = " & iVal & " AND varReportTitle = '" & ConvertQuotesSingle(iVal2) & "'"
    '   Debug.Print sSQL
    Set rst = CurrentDb.OpenRecordset(sSQL)

    If Not rst.EOF Then  '<<-- like Ajax suggests
        Me.txtintSubID = rst![intSubscriptionID]
        Me.txtvarExtensionSettingValueList = rst![varExtensionSettingValueList]
        Me.cbobitActiveForNoRecords = rst![bitActiveForNoRecords]
        Me.cbobitActive = rst![bitActive]
        Me.txtdatStartDate = rst![datStartDate]
        Me.txtdatEndDate = rst![datEndDate]
        Me.txtdatAdded = rst![datAdded]
        Me.txtdatModified = rst![datModified]
    Else
        MsgBox "No records to return"
    End If

    ' clean up
    rst.Close
    Set rst = Nothing

Putting the SQL string into a string variable allows you to print the SQL string to the immediate window (using Debug.Print sSQL) to see if the SQL is properly formed. You can copy the SQL from the immediate window and paste it into a new query (SQL view), then execute the query to see if records are returned. One of my most used debugging methods.

I also use this function to deal with apostrophe's in text:

Function ConvertQuotesSingle(InputVal)
   ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

One more thing: "iVal" is declared as an Integer, so you cannot assign an empty string to it if Me.cbointReportID is NULL.

Incorrect:
iVal = Nz(Me.cbointReportID.Value, "")

Correct:
iVal = Nz(Me.cbointReportID, 0)

[AAAndy]

That seems to work, I will monitor the issue. Thanks for everyones help.