How to change code to avoid apostrophe/single quote
Run-time error '3075':
Syntax error (missing operator) in query expression 'tblClients.Subgect
IN ('1Sample1-Bank','1Sample2-Credit Card')'.
Primary key is text (Subgect); CustomerID is defined as AutoNumber in tables and queries.
1Sample1-Bank and 1Sample2-Credit Card are text primary key Subgect of sample records.
Using Access 97, so Replace not available. How to use
chr(39?) or dim multiple double quotes for line with Replace:
CODE portion
For Each varItm In ctl.ItemsSelected
strList = strList & ",'" & ctl.ItemData(varItm) & "'"
strList = strList & ",'" & Replace(ctl.ItemData(varItm), "'", "''") & "'"
'Debug.Print ctl.ItemData(varItm)
Next varItm
A query results form's listbox with selected items opens these selected records in
either a form or reports when press spacebar.
full code
---- CODE
Private Sub lstResult_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case 32 ' vbKeySpace key
KeyCode = 32 ' this "revaluation" kills escape key action
'Open frmCustomer based on Subgect(s) from lstResult listbox
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String
strList = ""
Set frm = Forms!frmAndOr
Set ctl = frm!lstResult
With Forms!frmAndOr!lstResult
For Each varItm In ctl.ItemsSelected
strList = strList & ",'" & ctl.ItemData(varItm) & "'"
'Debug.Print ctl.ItemData(varItm)
Next varItm
End With
' strList = Left(strList, Len(strList) - 2)
If strList <> "" Then
If MsgBox("View results in form (Yes) or reports (No) view?", vbYesNo, "Format of Results") = vbYes Then
DoCmd.OpenForm "frmCustomer", acNormal, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
Else
DoCmd.OpenReport "rptSubjects2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
DoCmd.OpenReport "rptSubtopics2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
End If
End If
Case Else ' see key code generated by other keystrokes
'MsgBox KeyCode ' comment this out when it annoys you
End Select
End Sub
Run-time error '3075':
Syntax error (missing operator) in query expression 'tblClients.Subgect
IN ('1Sample1-Bank','1Sample2-Credit Card')'.
Primary key is text (Subgect); CustomerID is defined as AutoNumber in tables and queries.
1Sample1-Bank and 1Sample2-Credit Card are text primary key Subgect of sample records.
Using Access 97, so Replace not available. How to use
chr(39?) or dim multiple double quotes for line with Replace:
CODE portion
For Each varItm In ctl.ItemsSelected
strList = strList & ",'" & ctl.ItemData(varItm) & "'"
strList = strList & ",'" & Replace(ctl.ItemData(varItm), "'", "''") & "'"
'Debug.Print ctl.ItemData(varItm)
Next varItm
A query results form's listbox with selected items opens these selected records in
either a form or reports when press spacebar.
full code
---- CODE
Private Sub lstResult_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case 32 ' vbKeySpace key
KeyCode = 32 ' this "revaluation" kills escape key action
'Open frmCustomer based on Subgect(s) from lstResult listbox
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String
strList = ""
Set frm = Forms!frmAndOr
Set ctl = frm!lstResult
With Forms!frmAndOr!lstResult
For Each varItm In ctl.ItemsSelected
strList = strList & ",'" & ctl.ItemData(varItm) & "'"
'Debug.Print ctl.ItemData(varItm)
Next varItm
End With
' strList = Left(strList, Len(strList) - 2)
If strList <> "" Then
If MsgBox("View results in form (Yes) or reports (No) view?", vbYesNo, "Format of Results") = vbYes Then
DoCmd.OpenForm "frmCustomer", acNormal, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
Else
DoCmd.OpenReport "rptSubjects2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
DoCmd.OpenReport "rptSubtopics2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
End If
End If
Case Else ' see key code generated by other keystrokes
'MsgBox KeyCode ' comment this out when it annoys you
End Select
End Sub
