UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Vba Using SQL With Embedded Variables Generating Error, Access 2013    
 
   
ScottyBee
post Jul 2 2019, 08:37 PM
Post#1



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Hello Everyone,

I have an option group (frame) on a from named fraMechCondP5 . There are five choices for values 1 to 5. When the user makes a selection, I have code that fires in the After_Update event of the control. This code has embedded SQL using a variable based on a value pulled off of the form. I am able to make the code work for one criteria in the WHERE clause using the variable but when I add a second criteria using AND, I get an error.

I have made notes in the code below which blocks run and those that don't. For now, I am only focusing on the three SQL code blocks for "Case 5." The first code block worked just fine and has two hard coded values in the WHERE clause.

In the second code block for "Case 5", I am referencing a variable in the Where clause that is based on a form value. This is an improvement as there are hundreds of subsystems and I need the code to grab the current one. I got this one to work just fine with a lot of research and experimentation. However, I need to limit the query to a second criteria.

In the third code block for "Case 5", I attempted to use a variable in place of the code for "Ratings.[fk_CategoryID]=10" but could not get it to work.

Any help would be greatly appreciated.


CODE
Private Sub fraMechCondP5_AfterUpdate()
    
    Dim dbs As Database
    Dim SubSystemIDValue As String
    Dim frmCategory As String
    SubSystemIDValue = [Forms]![EER Form]![SubSystemID]
    frmCategory = 10
    Set dbs = CurrentDb
    
    Select Case fraMechCondP5.Value
        
        ' This SQL Statement works
        ' Case 5
            ' dbs.Execute "Update Ratings " _
            ' & "Set Ratings.Rating = 5 " _
            ' & "WHERE (Ratings.[fk_SubSystemID]=1 AND Ratings.[fk_CategoryID]=10);"
            ' dbs.Close
        
        ' This SQL Statement works
        ' Case 5
            ' dbs.Execute "Update Ratings " & _
            ' "Set Ratings.Rating = 5 " & _
            ' "WHERE (((Ratings.fk_SubSystemID)=" & SubSystemIDValue & "));"
            ' dbs.Close
        
        ' This SQL Statement DOES NOT work
        Case 5
             dbs.Execute "Update Ratings " & _
             "Set Ratings.Rating = 5 " & _
             "WHERE (((Ratings.fk_SubSystemID)=" & SubSystemIDValue And Ratings.fk_CategoryID = frmCategory & "));"
             dbs.Close
        
        ' This SQL Statement works
        Case 4
            dbs.Execute "Update Ratings " _
            & "Set Ratings.Rating = 4 " _
            & "WHERE (Ratings.[fk_SubSystemID]=1 AND Ratings.[fk_CategoryID]=10);"
            dbs.Close
        
        ' This SQL Statement works
        Case 3
            dbs.Execute "Update Ratings " _
            & "Set Ratings.Rating = 3 " _
            & "WHERE (Ratings.[fk_SubSystemID]=1 AND Ratings.[fk_CategoryID]=10);"
            dbs.Close
        
        ' This SQL Statement works
        Case 2
            dbs.Execute "Update Ratings " _
            & "Set Ratings.Rating = 2 " _
            & "WHERE (Ratings.[fk_SubSystemID]=1 AND Ratings.[fk_CategoryID]=10);"
            dbs.Close
        
        ' This SQL Statement works
        Case 1
            dbs.Execute "Update Ratings " _
            & "Set Ratings.Rating = 1 " _
            & "WHERE (Ratings.[fk_SubSystemID]=1 AND Ratings.[fk_CategoryID]=10);"
            dbs.Close
    End Select
End Sub
Go to the top of the page
 
ITguaranteed
post Jul 2 2019, 08:46 PM
Post#2



Posts: 32
Joined: 19-June 19
From: Tasmania, Australia


The problem is with your quotes

Not tested

CODE
"WHERE (((Ratings.fk_SubSystemID)=" & SubSystemIDValue & " And Ratings.fk_CategoryID = " & frmCategory & "));"


When doing this I like to put the SQL statement into a string so that you can debug.print the string to see what is actually in there.

eg.
mysql= "select * from staff"
debug.print mysql

Go to the top of the page
 
RJD
post Jul 2 2019, 09:28 PM
Post#3


UtterAccess VIP
Posts: 9,923
Joined: 25-October 10
From: Gulf South USA


Hi: You Dim-ed the variables as ...

CODE
Dim SubSystemIDValue As String
Dim frmCategory As String

Plus, after defining frmCategory as String, you set the value as numeric ...

CODE
frmCategory = 10

...then treated them both as numeric in the SQL, along with not getting the other quotes right as well. Using ITguaranteed's correction to your SQL, I would add some single quotes like this ...

"WHERE (((Ratings.fk_SubSystemID)='" & SubSystemIDValue & "' And Ratings.fk_CategoryID = '" & frmCategory & "'));"

If the variables are actually numeric, then the previous suggestion should work, and you should change the Dim types to something like Long.

Like ITguaranteed, this is also not tested. You can test this - and possibly post a db for us to look at if still not working (no sensitive data, of course, relevant objects, C&R and zipped).

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
ScottyBee
post Jul 3 2019, 05:36 PM
Post#4



Posts: 38
Joined: 6-June 05
From: Portland Oregon


Thanks IT, your solution worked great. Also, thanks RJB, I changed by Dim statement to frmCategory = "10" even though leaving the quotes off still worked, it is better to not leave things to chance.

I now have another issue very similar to this but will create another Post to keep things organized. Thanks both of you for your help smile.gif
Go to the top of the page
 
ITguaranteed
post Jul 3 2019, 06:45 PM
Post#5



Posts: 32
Joined: 19-June 19
From: Tasmania, Australia


Hi Scotty,

What RJD was trying to say was that at one point in your original code you treated the variable as a string and at another you treated it as a number.

so either

Dim frmCategory As String
and
frmCategory = "ABC"

or

Dim frmCategory as Integer
and
frmCategory = 1

As you are using it for a option group I would opt for treating it a a number.
This post has been edited by ITguaranteed: Jul 3 2019, 06:45 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 11:47 AM