JPFred
Registered User.
- Local time
- Today, 10:29
- Joined
- Oct 29, 2017
- Messages
- 47
I am attempting to run an SQL statement from code when a check box is clicked on. I build the statement (seen below) and try to execute it using the DoCmd.RunSQL function. When I execute the code I a 2342 run time error. If I use the the DoCmd.OpenQuery function passing the name of a query defined below it displays a pop-up asking me for the parameter value (which is already defined in the query). In the following example the value of strchksearch is "*AVI*".
Query definition:
Function definition:
The code using DoCmdOpenQuery is as follows:
Query definition:
Code:
SELECT [main.access], [main.Author], [main.Subject], [main.Title], [main.Pub], [main.Year]
FROM main
WHERE [main.Author] IN(SELECT [Author] FROM [main] WHERE [Author] LIKE [strchksearch] ORDER BY Author);
Function definition:
Code:
Private Sub chkauthor_Click()
Dim strFullQuery As String
Dim strQueryPart1 As String
Dim strQueryPart2 As String
Dim strQueryPart3 As String
Dim strdoublequote As String
strdoublequote = """"
strQueryPart1 = "SELECT [main.access], [main.Author], [main.Subject], [main.Title], [main.Pub], [main.Year] FROM main WHERE [main.Author] IN(SELECT [Author] FROM [main] WHERE [Author] LIKE "
strQueryPart2 = ("*" & txtsearch.Value & "*")
strQueryPart3 = " ORDER BY Author);"
strFullQuery = strQueryPart1 & strdoublequote & "*" & strQueryPart2 & "*" & strdoublequote & strQueryPart3
DoCmd.RunSQL strFullQuery
End Sub
The code using DoCmdOpenQuery is as follows:
Code:
Private Sub chkauthor_Click()
dim strsearchit as string
dim strdoublequote as string
strdoublequote = """"
strsearchit = ("*" & txtsearch.Value & "*")
DoCmd.OpenQuery ("testsearch")
End Sub