Difficulty using SELECT statement in VBA

Question

I'm trying to set command buttons to enter data into a table. If the record already exists I want the button to update it, and if it does not the button needs to create it. Here's a sample of what the table looks like

ID    scenarioID    reductionID    Impact Variable    Variable Impact
1         1              1          Safety             4
2         1              1          Environmental      2
3         1              1          Financial          1

In order to accurately locate records, it needs to search for the specific impact variable paired with the scenarioID. I'm trying to use a select statement, but DoCmd.RunSQL doesn't work for select statements, and I'm not sure how else to do it.

Here's the code. I left DoCmd.SQL in front of the select statement for lack of anything else to place there for now.

Private Sub Var1R1_Click() 'Stores appropriate values in tImpact upon click

'Declaring database and setting recordset
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tImpact")


'Declaring Variable as Scenario Choice combobox value
Dim Sc As Integer

Sc = Schoice.Value


'Stores impact variable 
Dim impvar1 As String


'Desired impact variable for column 1
impvar1 = DLookup("impactVariable", "tImpactVars", "ID = 1")



DoCmd.RunSQL "SELECT * FROM tImpact WHERE [Impact Variable] = " & impvar1 & " AND scenarioID = " & Sc



If rs.EOF Then

    DoCmd.RunSQL "INSERT INTO tImpact(scenarioID, [Impact Variable], [Variable Impact])" & "VALUES (" & Sc & ", " & impvar1 & ", 1)"

    MsgBox "Record Added", vbOKOnly


Else

    db.Execute "UPDATE tImpact SET [Variable Impact] = 1 WHERE [Impact Variable] = " & impvar1 & " AND scenarioID = " & Sc

    MsgBox "Record Updated", vbOKOnly


End If

End Sub

If anyone can tell me how to get that SELECT statement to run, or another way of doing this, that would be great.

Any help is greatly appreciated!

Simple syntax error - using a string means enclose with quotes or double quotes. """ & impvar1 & """ — dbmitch
– dbmitch, Commented May 31, 2018 at 14:27
Open the recordset don't run SQL . Run is only for an action query. You need Set rs2 = currentdb.openrecordset "SELECT * FROM tImpact WHERE [Impact Variable] = " & impvar1 & " AND scenarioID = " & Sc — Minty
– Minty, Commented May 31, 2018 at 14:29
@dbmitch what should I replace DoCmd.RunSQL with? using it still gives the "must contain an SQL statement" error with that change @Minty I got a compile error "Expected: end of statement". I tried moving around/adding quotes in case that was the issue but It kept returning the same error. I also tried using both of your recommendations together Set rs = . . . """ & impvar1 & """ . . . and it still wasn't working — Roland
– Roland, Commented May 31, 2018 at 14:54
@Roland - you have the same issues with your UPDATE and INSERT statements - same fix — dbmitch
– dbmitch, Commented May 31, 2018 at 15:01

Albert D. Kallal · Accepted Answer · 2018-05-31 18:35:29Z

You can use a recordset. In this case a recordset is better, since you only execute the SQL one time, if it returns a record, you "edit" and if not then you "add" with the SAME reocrdset. This approach is FAR less code, and the values you set into the reocrdset does not require messy quotes or delimiters etc.

eg:

scenaridID = 1             ' set this to any number
impvar1 = "Safety"         ' set this to any string
updateTo = "Financial"

strSQL = "select * from tImpact where [Impact Variable] = '" & impvar1 & "'" & _
        " AND scenaridID = " & scenaridID

Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
  If .RecordCount = 0 Then
     ' add the reocrd
     .AddNew
  Else
     .Edit
  End If

  !scenaridID = scenarid
  ![Impact Variable] = impvar1
  ![Variable Impact] = 1
  .Update
End With
rst.Close

So you can use the same code for the update and the edit. It just a question if you add or edit.

Dick Kusleika · Accepted Answer · 2018-05-31 15:21:12Z

Use OpenRecordset and retrieve the ID for the record if it exists.

Private Sub Command0_Click()

    Dim aLookup(1 To 3) As String
    Dim aAction(1 To 3) As String
    Dim rs As Recordset
    Dim db As Database

    'Replace these two constants with where you get the information on your form
    Const sIMPVAR As String = "Financial"
    Const lSCENID As Long = 1

    'Build the SQL to find the ID if it exists
    aLookup(1) = "SELECT ID FROM tImpact"
    aLookup(2) = "WHERE ScenarioID = " & lSCENID
    aLookup(3) = "AND ImpactVariable = """ & sIMPVAR & """"

    'Run the sql to find the id
    Set db = CurrentDb
    Set rs = db.OpenRecordset(Join(aLookup, Space(1)))


    If rs.BOF And rs.EOF Then 'it doesn't exist, so build the insert statement
        aAction(1) = "INSERT INTO tImpact"
        aAction(2) = "(ScenarioID, ImpactVariable, VariableImpact)"
        aAction(3) = "VALUES (" & lSCENID & ", '" & sIMPVAR & "', 1)"
    Else 'it does exist, so build the update statement
        aAction(1) = "UPDATE tImpact"
        aAction(2) = "SET VariableImpact = 1"
        aAction(3) = "WHERE ID = " & rs.Fields(0).Value
    End If

    'Run the action query
    db.Execute Join(aAction, Space(1))

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

Collectives™ on Stack Overflow

Difficulty using SELECT statement in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related