• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Setting a variable and using it in SQL

Dear Experts,
I am having a great difficulty running what I thought to be a simple task.
I have a form with a subform as part of it, which has varying number of records.  When a user pushes "submit" botton, I want to copy the contents of the subform with some calculations done, and also a component of the parent form.
I have written a code attached, which does not append anything.  
Please advise.  
Private Sub Command9_Click()

'  StrSQL As String
'  Value As String
 
 Set dbsDatabase = CurrentDb
 Value = Me![Product Description]
 
 StrSQL = "INSERT INTO FormulaDetails ([Ingredient Information], Prefix) " & _
 "SELECT TemplateDetails.[Product Description], TemplateDetails.Prefix " & _
 "FROM [TemplateDetails] WHERE (TemplateDetails.[Product Description] = 'Value')"

 CurrentDb.Execute StrSQL

End Sub

Open in new window

0
yballan
Asked:
yballan
  • 5
  • 4
  • 3
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
try this:

 StrSQL = "INSERT INTO FormulaDetails ([Ingredient Information], Prefix) " & _
 "SELECT TemplateDetails.[Product Description], TemplateDetails.Prefix " & _
 "FROM [TemplateDetails] WHERE (TemplateDetails.[Product Description] = " & Chr(34) & Value & Chr(34) & ")"

mx
0
 
dqmqCommented:
Try this:


Private Sub Command9_Click()
 
'  StrSQL As String
'  Value As String
 
 Set dbsDatabase = CurrentDb
 'Value = Me![Product Description]
 
 StrSQL = "INSERT INTO FormulaDetails ([Ingredient Information], Prefix) " & _
 "SELECT TemplateDetails.[Product Description], TemplateDetails.Prefix " & _
 "FROM [TemplateDetails] WHERE TemplateDetails.[Product Description] = """ & Me![Product Description] & """
 
dbsDatabase.Execute StrSQL
 
End Sub



0
 
yballanAuthor Commented:
Thank you for a quick response, but it still does not append to FormulaDetails.'
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
hnasrCommented:
Try to use a select query to check for intended output.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Actually, there appears to be an extra paren  

 StrSQL = "INSERT INTO FormulaDetails ([Ingredient Information], Prefix) " & _
 "SELECT TemplateDetails.[Product Description], TemplateDetails.Prefix " & _
 "FROM [TemplateDetails] WHERE (TemplateDetails.[Product Description] = " & Chr(34) & Value & Chr(34)

and add the dbFailOnError
dbsDatabase.Execute StrSQL , dbFailOnError
0
 
dqmqCommented:
Try this:

 StrSQL = "INSERT INTO FormulaDetails ([Ingredient Information], Prefix) " & _
 "SELECT TemplateDetails.[Product Description], TemplateDetails.Prefix " & _
 "FROM [TemplateDetails] WHERE TemplateDetails.[Product Description] = """ & _
Me![Product Description] & """"
 
dbsDatabase.Execute StrSQL, dbFailOnError

0
 
yballanAuthor Commented:
Thank you, this worked!!
0
 
yballanAuthor Commented:
Dear dqmq,

Just to understand this better, could I ask a question?
why does it seem to be an extra pair of quotes around
& Me![Product Description] &   ?
I took one set off and got an error, so I realize that they are necessary but cannot see why.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
yballan:
I'm just curious.  Not to take anything away from dqmq, but he posted the exact same thing I did, except he use multiple physical double quotes and I used Chr(34) to represent double quotes ... easier to read.  Also, I used the Variable per "Setting a variable and using it in SQL" and he did not.

?
0
 
yballanAuthor Commented:
Dear DatabaseMX,

I am sorry, your code appears a little differently, and when I tried it, it gave me an error.
It appears as
 StrSQL = "INSERT INTO FormulaDetails ([Ingredient Information], Prefix) " & _
 "SELECT TemplateDetails.[Product Description], TemplateDetails.Prefix " & _
 "FROM [TemplateDetails] WHERE (TemplateDetails.[Product Description] = " & Chr(34) & Value & Chr(34)

dbsDatabase.Execute StrSQL , dbFailOnError

in my browser.
If it is the problem with my browser settings, I apologize sincerely.  
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Yeah ... there is still an extra paren ...

 "FROM [TemplateDetails] WHERE (TemplateDetails.[Product Description] = " & Chr(34) & Value & Chr(34)
                                                     ^^

oh well ...
0
 
dqmqCommented:
Your error was caused by mishandling of quotes.  First, you singled quoted 'value', which makes it behave as a literal.  The solution is to move the value outside the quoted string and to supply quote marks on either side of it.    

The extra quotes are an age-old method for indicating quote  marks inside a string that is delimited by quote marks: Two quotes inside a quoted string become one quote.

DatabaseMx tried to do the same thing using chr(34), so his approach was equivalent after syntax errors were cleaned up.
0
 
yballanAuthor Commented:
Thank you, this was very educational!!!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now