Changing SQL statement based on which VBA listbox is currently selected

Question

I have a userform called Attendance that has two listboxes on it. One called Seated, one called NotSeated.

In a SQL column everyone starts the day off as '1'. If someone is here today they get marked as '2', if they aren't they get marked as a '3' or a '4'.

I have a reset button that I want to have reset them to '1' when pressed. What I need to be able to do is to change my UPDATE query based on which listbox the user has currently highlighted. Here is what I have so far, but it's not working.

Sub resetagent()
database_connect
Dim AttendRecord As New ADODB.Recordset
Dim Cm As ADODB.Command
If appconn.State = 0 Then
Call database_connect
End If
Set Cm = New ADODB.Command
With Cm
    .ActiveConnection = appconn
    If attendance.Seated.value <> "" Then
    .CommandText = "UPDATE [Attendance] SET [Seated] = '1' WHERE [Agentname]= '" & attendance.Seated.value & "'"
    ElseIf attendance.NotSeated.value <> "" Then
    .CommandText = "UPDATE [Attendance] SET [Seated] = '1' WHERE [Agentname]= '" & attendance.NotSeated.value & "'"
    .CommandType = adCmdText
    .Execute
End If
End With
Set AttendRecord = Nothing
database_Disconnect
End Sub

It looks like the last End If is in the wrong spot. Move it up 2 lines, just after the second CommandText. — Brian M Stafford
– Brian M Stafford, Commented Aug 2, 2018 at 20:00

Brent · Accepted Answer · 2018-08-02 20:08:28Z

1

Your .Execute is sitting inside your if statement and can only run if you are in the Else portion of the statement. Move the End ifstatement up 2 lines.

Also if both list boxes are not blank (i.e. one is a '1' and the other is a '3') then it would not match either of the If statement conditions and do nothing here.

One you move the End If line, it would execute whatever the button's defaulted CommandText is.

answered Aug 2, 2018 at 20:08

Brent

989 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jb83 Over a year ago

That fixed it! Thank you!

Collectives™ on Stack Overflow

Changing SQL statement based on which VBA listbox is currently selected

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related