UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combobox, Access 2013    
 
   
fogline
post May 21 2020, 08:35 AM
Post#1



Posts: 229
Joined: 5-August 15
From: Ringgold, GA. USA


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

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 
nuclear_nick
post May 21 2020, 08:55 AM
Post#2



Posts: 1,872
Joined: 5-February 06
From: Ohio, USA


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...

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
fogline
post May 21 2020, 09:12 AM
Post#3



Posts: 229
Joined: 5-August 15
From: Ringgold, GA. USA


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

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 
nuclear_nick
post May 21 2020, 09:28 AM
Post#4



Posts: 1,872
Joined: 5-February 06
From: Ohio, USA


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!

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
fogline
post May 21 2020, 09:44 AM
Post#5



Posts: 229
Joined: 5-August 15
From: Ringgold, GA. USA


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

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 
FrankRuperto
post May 21 2020, 10:02 AM
Post#6



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


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?

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
fogline
post May 21 2020, 10:34 AM
Post#7



Posts: 229
Joined: 5-August 15
From: Ringgold, GA. USA


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.

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 
BruceM
post May 21 2020, 11:03 AM
Post#8


UtterAccess VIP
Posts: 8,134
Joined: 24-May 10
From: Downeast Maine


Another option for managing quotes within quotes (but not dates) can be found here. 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).
Go to the top of the page
 
fogline
post May 21 2020, 11:31 AM
Post#9



Posts: 229
Joined: 5-August 15
From: Ringgold, GA. USA


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.
This post has been edited by fogline: May 21 2020, 11:31 AM

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th July 2020 - 07:01 AM