Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Errors + Error Handling _ Combobox

Posted by: fogline May 21 2020, 08:35 AM

I have an Category Combo Box to to find records on a form
all of my combo box Categories work just fine
But I have one Category that has an Apostrophe in it like -> Ray's
If I pick it then I get an error and it cant find the record
If I change that Category name to -> Rays
Then it works.

Here is my SQL for the combo box. I know I just need to add something so the Apostrophe will not throw an error.

Private Sub Combo3_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String


strSQL = "SELECT DISTINCT tblDocPDF.Category FROM tblDocPDF "
strSQL = strSQL & " WHERE tblDocPDF.Category = '" & CatCombo3 & "'"
strSQL = strSQL & " ORDER BY tblDocPDF.Category;"


strSQLSF = "SELECT * FROM tblDocPDF "
strSQLSF = strSQLSF & " WHERE tblDocPDF.Category = '" & CatCombo3 & "'"


Me.RecordSource = strSQLSF
Me.Requery
End Sub

Posted by: nuclear_nick May 21 2020, 08:55 AM

I posted this a long time ago...

CODE
Public Function FixValueForSQL(ControlValue As Variant) As String
  Dim Result As String
  Dim p As Long
  If IsNull(ControlValue) Or IsEmpty(ControlValue) Then
    
    Result = "NULL"
  
  Else
  
    Result = ControlValue
    
    If VarType(ControlValue) = vbString Then
    
      'Add quotation marks in front of all embedded quotes so string can
      ' be correctly contacted in SQL string
      p = InStr(ControlValue, """")
      
      While p > 0
      
        Result = Left(Result, p - 1) & """" & Mid(Result, p, 1) & Mid(Result, p + 1, Len(Result))
         p = InStr(p + 2, Result, """")
        
      Wend
    
      '  Add qoutes to beginning and end so string can be correctly contacted in SQL string
      Result = """" & Result & """"
    
    ElseIf VarType(ControlValue) = vbDate Then
    
      '  Add # to beginning and end for date formats
      Result = "#" & Result & "#"
      
    End If
  
  End If
  FixValueForSQL = Result
End Function


It's use, in your case...
CODE
strSQL = "SELECT DISTINCT tblDocPDF.Category FROM tblDocPDF "
strSQL = strSQL & " WHERE tblDocPDF.Category = " & FixValueForSQL(CatCombo3)
strSQL = strSQL & " ORDER BY tblDocPDF.Category;"


strSQLSF = "SELECT * FROM tblDocPDF "
strSQLSF = strSQLSF & " WHERE tblDocPDF.Category = " & FixValueForSQL(CatCombo3)


Hope that helps...

Posted by: fogline May 21 2020, 09:12 AM

You The Man Nick notworthy.gif
That works GREAT thumbup.gif

Posted by: nuclear_nick May 21 2020, 09:28 AM

Glad to hear it works for you.

Also, if you notice, it works for dates as well, adding the octothorope (#) at the beginning and end of dates..

Good luck!

Posted by: fogline May 21 2020, 09:44 AM

Also, if you notice, it works for dates as well, adding the octothorope (#) at the beginning and end of dates.. thumbup.gif

Posted by: FrankRuperto May 21 2020, 10:02 AM

If the ComboBox displays text descriptions but uses the lookup table's CategoryID field for the query's filter, why would it matter if the description's value has an apostrophe in it?

Posted by: fogline May 21 2020, 10:34 AM

Hi Frank
In my case I was not using CategoryID
I was just using text description, So if you had an apostrophe in it
then it would look at that apostrophe as code and give you an error.

I know you should always us CategoryID in a ComboBox
But this was some old code and the BE table did not have a CategoryID field.
So with this code from Nick was a quick fix and it works great.

Posted by: BruceM May 21 2020, 11:03 AM

Another option for managing quotes within quotes (but not dates) can be found https://www.UtterAccess.com/wiki/QuoteFix. It works for both single and double quotes in a text string, including both appearing together in the same string. I use it routinely for any search values or values included in strings (names, in particular, but could include product codes and so forth).

Posted by: fogline May 21 2020, 11:31 AM

Thanks Bruce thumbup.gif
That worked great also.
As it not working with Dates
I don't think that I have ever used a Combo Box with Dates to lookup records anyway.