I am trying to create an 'Update Query' in vba but I am missing something. I am running Access 2002 XP.

I have a drop down combo box with four columns set and bound to column 1. My sql query for the row source is:

SELECT DISTINCT [OrderNumber], [OrderName], [PrimaryWorkGroup], [SecondaryWorkGroup] FROM Orders ORDER BY [OrderName], [PrimaryWorkGroup], [SecondaryWorkGroup];

After selecting the order I am trying to change the 'OrderStatus' field from Active to Inactive.

Here is my command.

db.Execute ("UPDATE Orders SET Orders.OrderStatus = Inactive " & " WHERE [OrderName] = '" & Me.cbodOrderName.Column(1) & "' AND [PrimaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & "' AND [SecondaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & "'")


It isn't working. Can anyone offer any advice?

Thank you,
Tony

Try this:

Dim sSQL As String

sSQL = "UPDATE Orders SET Orders.OrderStatus = Inactive " & " WHERE [OrderName] = '" & Me.cbodOrderName.Column(1) & "' AND [PrimaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & "' AND [SecondaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & "'"

db.Execute(sSQL)

Access doesn't like taking values at run time, so build your string first, then execute the string.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for the advice, unfortunately I am getting an error:

"Run-time error 3061" "Too few Parameters. Expected 1."

Here is what I have now:

Dim db As Database
Dim sSQL As String
Set db = CurrentDb()
sSQL = "UPDATE Orders SET Orders.OrderStatus = Inactive " & " WHERE [OrderName] = '" & Me.cbodOrderName.Column(1) & "' AND [PrimaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & "' AND [SecondaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & "'"

If MsgBox("Discontinue " & Me.cbodOrderName.Column(1) _
        & vbCrLf & vbCrLf & "Are you sure?" _
        , vbYesNo, "Continue...") = vbYes Then

        db.Execute (sSQL)
End If

Yikes. I hate to suggest it but...

Dim sMessage As String
Dim sTitle As String

sMessage = "Discontinue " & Me.cbodOrderName.Column(1) _
        & vbCrLf & vbCrLf & "Are you sure?"
sTitle = "Continue..."

If MsgBox(sMessage, vbYesNo, sTitle) = vbYes Then
        db.Execute (sSQL)
End If


Also, this is a little too obvious, but is the Orders.OrderStatus field a String or Yes/No data type?


mmcdonal

Look it up at: http://wrox.books24x7.com

The OrderStatus is a text field and Value List with either "Active" or "Inactive"

I got the same error after using the settings that you described.


Tony

In looking at the SQL string again, is this what you mean?

FROM:
WHERE [OrderName] = '" & Me.cbodOrderName.Column(1) & _
"' AND [PrimaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & _
"' AND [SecondaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & "'"

TO:
WHERE [OrderName] = '" & Me.cbodOrderName.Column(1) & _
"' AND [PrimaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & _
"' AND [SecondaryWorkGroup] = '" & Me.cbodOrderName.Column(3) & "'"

Note the column number change for the SecondaryWorkGroup field.

I would try breaking this down by adding this line:

MsgBox sSQL

before the code is run to see if your string is built properly. Can you post the results of that MsgBox?




mmcdonal

Look it up at: http://wrox.books24x7.com

UPDATE Orders SET Orders.OrderStatus =
Inactive WHERE [OrderName] = " AND [PrimaryWorkGroup] = " AND
[SecondaryWorkGroup] = "


That is verbatim.

Also the same error:
"Run-time error 3061" "Too few Parameters. Expected 1."

Well you see the problem. Your code is not taking the values from your controls. Do you have any idea why it wouldn't take those values? Has a selection been made?

Normally I would do something like this:

If IsNull(Me.cboOrderName) Or Me.cboOrderName = "" Then
   MsgBox "Plese select an order.", vbInformation
   Exit Sub
Else
   .insert code here...

End If

Me.cboOrderName can also = 0 if it is taking a number field, so in that case...

If IsNull(Me.cboOrderName) Or Me.cboOrderName = 0 Then
...



mmcdonal

Look it up at: http://wrox.books24x7.com

I have a drop box. Once I select the order, some fields get populated. Here is the code for it.
============================================
Private Sub cbodOrderName_AfterUpdate()
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[OrderNumber] = " & Str(Nz(Me![cbodOrderName], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    txtFocusBox.SetFocus
    Me.cbodOrderName = Null
    lblOrderName.Visible = True
    txtOrderName.Visible = True
    lblPrimaryWorkGroup.Visible = True
    cboPrimaryWorkGroup.Visible = True
    lblSecondaryWorkGroup.Visible = True
    cboSecondaryWorkGroup.Visible = True
    lblRequestedDate.Visible = True
    dtRequestedDate.Visible = True
    lblCompletedDate.Visible = True
    dtCompletedDate.Visible = True
    lblEnteredBy.Visible = True
    cboEnteredBy.Visible = True
    lblEnteredDate.Visible = True
    dtEnteredDate.Visible = True
    lblComments.Visible = True
    txtComments.Visible = True
    lblAdditionalComments.Visible = True
    txtAdditionalComments.Visible = True
    cmdCloseOrder.Visible = True
    cmdCloseOrder.Left = 17880.048
    cmdCloseOrder.Top = 7620.048

    Dim db As Database
    Dim strSQL As String
    Dim sMessage As String
    Set db = CurrentDb()
    Dim sTitle As String
    sSQL = "UPDATE Orders SET Orders.OrderStatus = Inactive " & " WHERE [OrderName] = '" & Me.cbodOrderName.Column(1) & "' AND [PrimaryWorkGroup] = '" & Me.cbodOrderName.Column(2) & "' AND [SecondaryWorkGroup] = '" & Me.cbodOrderName.Column(3) & "'"


    sMessage = "Discontinue " & Me.cbodOrderName.Column(1) _
        & vbCrLf & vbCrLf & "Are you sure?"
    sTitle = "Continue..."

    If MsgBox(sMessage, vbYesNo, sTitle) = vbYes Then
        MsgBox sSQL
        db.Execute(sSQL)
    End If

================================================== ==================
EDIT...I changed the sSQL and it works now. Thank you for your help with this. Here is my new sSQL string.


sSQL = "UPDATE Orders SET Orders.OrderStatus = " & "'Inactive'" & " WHERE [OrderName] = '" & Me.txtOrderName & "' AND [PrimaryWorkGroup] = '" & Me.cboPrimaryWorkGroup & "' AND [SecondaryWorkGroup] = '" & Me.cboSecondaryWorkGroup & "'"

================================================== =================
After I made the selection form the drop box the values were displayed in different fields.I just used the values displayed.