Solved

Access Form: Using an apostrophe?

Posted on 2012-04-06
8
276 Views
Last Modified: 2012-06-21
Good Afternoon!

I am working on a form in Access for my employees to use to create weekly task reports.

There is a field for "Tasks."  Once this is filled out, the user clicks on a button and the task is added to two tables and I am able to create a report.

I am running into the problem that the user receives an error message if an apostrophe is used. I have seen code that allows a particular string to have an apostrophe, but I don't know what to do if the apostrophe could be entered at any time in different descriptions of the tasks.

How do I fix this?

Thank you!
0
Comment
Question by:Megin
  • 4
  • 3
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37817410
two ways you can treat this problem, assuming string is varString

1. use chr(34) as wrapper of the string

     chr(34) & varString & chr(34)

2. use the replace function    > replace 1 ' single quote with 2 '  single quotes
    '" & replace(varString,"'", "''") & "'  
     '" & replace(varString,chr(39),chr(39) & chr(39)) & "'
0
 
LVL 75
ID: 37817412
Surround with double quote = Chr(34)

Chr(34) & YourStringWithApostrophe & Chr(34)

mx
0
 

Author Comment

by:Megin
ID: 37817617
I am so sorry, but I am new to this. Where do I put this code?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37817673
where are you using the string with apostrophes'?

give more detailed explnation
0
 

Author Comment

by:Megin
ID: 37817687
The field is "ActName."

The code is below.




Private Sub btnAdd_Click()
DoCmd.SetWarnings False

Dim strSQL As String
Dim db As Database
Dim rst As DAO.Recordset
Dim LngItem As Long

Set db = CurrentDb()




Dim rowc As Integer


With Me.LstNewAct
  For rowc = 0 To .ListCount - 1
    strSQL = "SELECT * FROM tbl_Activities WHERE actName='" & .Column(0, rowc) & "'"
    
    Set rst = db.OpenRecordset(strSQL)
    If (rst.BOF And rst.EOF) Then ' There are no records if Beginning-Of-File and End-Of-File are both true.
      DoCmd.RunSQL "INSERT INTO tbl_Activities (actName) VALUES ('" & .Column(0, rowc) & "')"
      rst.Close
    End If
    strSQL = "SELECT actID FROM tbl_Activities WHERE actName='" & .Column(0, rowc) & "'"
    Set rst = db.OpenRecordset(strSQL)
    DoCmd.RunSQL "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, '" & rst.Fields(0).Value & "', WkDate, actType1)"
  Next rowc
End With



With Me.LstAct
  For Each varItem In .ItemsSelected
    LngItem = .Column(0, varItem)
    strSQL = "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, " & LngItem & ", WkDate, actType1)"
    DoCmd.RunSQL strSQL, dbFailOnError
    
  Next varItem
  
End With
DoCmd.SetWarnings True

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37817694
try this format

strSQL = "SELECT * FROM tbl_Activities WHERE actName=" & chr(34) &  .Column(0, rowc) & chr(34)
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37817698
Private Sub btnAdd_Click()
DoCmd.SetWarnings False

Dim strSQL As String
Dim db As Database
Dim rst As DAO.Recordset
Dim LngItem As Long

Set db = CurrentDb()




Dim rowc As Integer


With Me.LstNewAct
  For rowc = 0 To .ListCount - 1
    strSQL = "SELECT * FROM tbl_Activities WHERE actName=" & chr(34) &  .Column(0, rowc) & chr(34)
    
    Set rst = db.OpenRecordset(strSQL)
    If (rst.BOF And rst.EOF) Then ' There are no records if Beginning-Of-File and End-Of-File are both true.
      DoCmd.RunSQL "INSERT INTO tbl_Activities (actName) VALUES (" & chr(34) & .Column(0, rowc) & chr(34) & ")"
      rst.Close
    End If
    strSQL = "SELECT actID FROM tbl_Activities WHERE actName=" & chr(34) &  .Column(0, rowc) & chr(34) & "
    Set rst = db.OpenRecordset(strSQL)
    DoCmd.RunSQL "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, '" & rst.Fields(0).Value & "', WkDate, actType1)"
  Next rowc
End With



With Me.LstAct
  For Each varItem In .ItemsSelected
    LngItem = .Column(0, varItem)
    strSQL = "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, " & LngItem & ", WkDate, actType1)"
    DoCmd.RunSQL strSQL, dbFailOnError
    
  Next varItem
  
End With
DoCmd.SetWarnings True
                                            

Open in new window

0
 

Author Closing Comment

by:Megin
ID: 37817703
Thank you! That worked like a charm!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Best way to create dynamic, short cut menu 7 27
Create tables in access db (2016)  using vba 13 41
Any Way to Print an Import Spec? 3 29
IIF help, YN field 7 21
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question