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

[AAAndy]

I get the below error when running the below code
Run-time error '-2147352567 (80020009)':

The value you entered isn't valid for this field.



The error is on line Me.txtintSubID = rst![intSubscriptionID] but when I remove Me.txtintSubID = rst![intSubscriptionID] then it is on Me.txtvarExtensionSettingValueList = rst![varExtensionSettingValueList]

Also sometimes I run the code and there is no error but sometimes it stops on that line. Does anyone know why? and how to fix it?

    Dim iVal As Integer
    iVal = Nz(Me.cbointReportID.Value, "")


    Dim iVal2 As String
    iVal2 = Nz(Me.cbovarReportTitle.Value, "")
    
    Dim rst As dao.Recordset
       
    Set rst = CurrentDb.OpenRecordset("SELECT intSubscriptionID,varExtensionSettingValueList,bitActiveForNoRecords,bitActive,datStartDate,datEndDate,datAdded,datModified FROM dbo_tbl_Custom_Subscription_Subscriptions WHERE intReportID like " & iVal & " AND varReportTitle like '" & iVal2 & "' ")


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]


rst.Close
Set rst = Nothing

[RuralGuy]

Intermittent usually means data sensitive. I'm used to people using the "*" with the Like word on strings.

[AAAndy]

I changed that line to * but same issue

 Dim iVal As Integer
    iVal = Nz(Me.cbointReportID.Value, "")


    Dim iVal2 As String
    iVal2 = Nz(Me.cbovarReportTitle.Value, "")
    
    Dim rst As dao.Recordset
       
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM dbo_tbl_Custom_Subscription_Subscriptions WHERE intReportID like " & iVal & " AND varReportTitle like '" & iVal2 & "' ")


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]


rst.Close
Set rst = Nothing

[AAAndy]

But I restarted access and now its working again. Maybe the * fixed it. I will write back if it reoccurs again.

[RuralGuy]

What I meant was:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM dbo_tbl_Custom_Subscription_Subscriptions WHERE intReportID like " & iVal & " AND varReportTitle like *'" & iVal2 & "'* ")

[AAAndy]

What I meant was:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM dbo_tbl_Custom_Subscription_Subscriptions WHERE intReportID like " & iVal & " AND varReportTitle like *'" & iVal2 & "'* ")

What do the * do in *'" & iVal2 & "'* ?

I googled it but can't find the answer

[RuralGuy]

It is a delimiter for the Like word. I've not used it but that is what I've seen. Maybe someone else will read this thread and help us out.

[AAAndy]

Error came back. Tried *'" & iVal2 & "'* but same error

[Bulzie]

The * before the variable means it ignores any characters before the value to try to get a match. * After ignores what is after the value.

So if you want to find anything that starts with ABC in first 3 characters, it would be: (Like ABC*)
If you wanted to find ABC and didn't care where it was in the text, it would be (Like *ABC*)

Try this in your statement
Like "*" & iVal1 & "*"
Like "*" & iVal2 & "*"

If your variable name matches the reportname exactly then just use = instead of Like.
intReportID = '" & iVal & "' (that is single quote and double quote on left and double quote and single quote at end)

Is intReportID numeric or text?
If numeric would be something like intReportID = " & iVal & " or intReportID = iVal

[AAAndy]

I figured out why I have an error. The error comes up when drop down menu cbovarReportTitle is searched with a ' for example it searches for Edward it works but when its Edward's it fails. Does anyone know a way around this?

[AAAndy]

I fixed it. need to insert double '' for it to search

added code below before search query

    If InStr(iVal2, "'") Then
      iVal2 = Replace(iVal2, "'", "''")
    End If

[AAAndy]

I'm still having the same error. The apostrophe was a different issue in addition to the one I had.

I attached a screenshot

[AAAndy]

I think I know what the issue was. When loading the form it would populate some of the fields incorrectly, I will fix that and it should be good.

[RuralGuy]

Let us know how you make out. It is a learning experience for me.

[CJ_London]

@AAAndy - just out of curiosity why don't you bind the fields to the controls and use

me.recordsource="SELECT *....

or

set me.recordset=currentdb.openrecordset("SELECT *....

Also, have you confirmed your query is returning records - you would normally use

Set rst = CurrentDb.OpenRecordset("SELECT * ....
if not rst.eof then
    Me.txtintSubID = rst![intSubscriptionID]
    ...
    ...
else
   msgbox "No records to return"
end If

if it is not returning records, then you will be trying to assign a null or nothing value