Kevin,
Always add debug.print when you are developing or troubleshooting:
debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
What do you see in the debug window?
You often need to replace a single quote with two single quotes in your SQL statements but I'm not sure this is your issue since you are pulling most of the fields values from another record.
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Qingqing qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, January 23, 2019 5:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Top right comma won't be inserted .
Sent: Wednesday, January 23, 2019 5:13 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Top right comma won't be inserted .
Dear All,
I've been using SQL Insert method for quite a while, and always get the best result I wanted, except for this one,the text with top right comma " ' " , it will stop the SQL insert. Can this be solved?
Here is the code:
Dim strSQL As String
strSQL = "INSERT INTO tblSubRoomingList (SeqNo, CityBookingID, SRLGroupID, ChineseName, EnglishName, GuestGender, PassportNumber, " & _
"DateOfBirth, Birthplace, PlaceOfIssue, IssuingDate, ExpiryDate, GuestType, RoomType, Remark) " & _
"SELECT tblRoomingList.SeqNo, " & Nz(Me.CityBookingID, 0) & ", " & Me.CBGroupID & ", tblRoomingList.ChineseName, " & _
"tblRoomingList.EnglishName, tblRoomingList.GuestGender, tblRoomingList.PassportNumber, tblRoomingList.DateOfBirth, " & _
"tblRoomingList.Birthplace, tblRoomingList.PlaceOfIssue, tblRoomingList.IssuingDate, tblRoomingList.ExpiryDate, " & _
"tblRoomingList.GuestType, tblRoomingList.RoomType, tblRoomingList.Remark " & _
"FROM tblRoomingList " & _
"WHERE tblRoomingList.GroupID = " & Me.CBGroupID
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery
If the "EnglishName" contains " ' "，It won't start the process. Please help! Thanks.
Best Regards,
Kevin
------------------------------------
Posted by: Qingqing <qingqinga@yahoo.com>
------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
I've been using SQL Insert method for quite a while, and always get the best result I wanted, except for this one,the text with top right comma " ' " , it will stop the SQL insert. Can this be solved?
Here is the code:
Dim strSQL As String
strSQL = "INSERT INTO tblSubRoomingList (SeqNo, CityBookingID, SRLGroupID, ChineseName, EnglishName, GuestGender, PassportNumber, " & _
"DateOfBirth, Birthplace, PlaceOfIssue, IssuingDate, ExpiryDate, GuestType, RoomType, Remark) " & _
"SELECT tblRoomingList.SeqNo, " & Nz(Me.CityBookingID, 0) & ", " & Me.CBGroupID & ", tblRoomingList.ChineseName, " & _
"tblRoomingList.EnglishName, tblRoomingList.GuestGender, tblRoomingList.PassportNumber, tblRoomingList.DateOfBirth, " & _
"tblRoomingList.Birthplace, tblRoomingList.PlaceOfIssue, tblRoomingList.IssuingDate, tblRoomingList.ExpiryDate, " & _
"tblRoomingList.GuestType, tblRoomingList.RoomType, tblRoomingList.Remark " & _
"FROM tblRoomingList " & _
"WHERE tblRoomingList.GroupID = " & Me.CBGroupID
CurrentDb.Execute strSQL, dbFailOnError
Me.Requery
If the "EnglishName" contains " ' "，It won't start the process. Please help! Thanks.
Best Regards,
Kevin
------------------------------------
Posted by: Qingqing <qingqinga@yahoo.com>
------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
SPONSORED LINKS
.
__,_._,___
Tidak ada komentar:
Posting Komentar