Docmd Update where criteria is Numeric

[jazee007]

Hi,
I'm new to access. I'm trying to update a tables. Below is the VBA Code.

Private Sub cmdUpdateSales_Click()
 
 DoCmd.RunSQL "UPDATE Customers SET Customers.CustomersStatus = 'Closed'" & _
 "Where (Customers.CustomersStatus) = 'Invoiced' and (Customers.CustomerID) = " & Me.Invoice_ID.Value & ""

End Sub

But nothing is working. Can somebody help me to write the code.

Please note that there are two tables. One is CUSTOMERS and the other is SALES. When I click on the button, I want the field CustomersStatus be updated to "Closed" where CustomersStatus = 'Invoiced' the table Customers and where CustomerID in table Customers = the field Invoice_ID in table Sales. I know that this must be easy. Its just that I'm new and don't know how to proceed.

[Seth Schrock]

Please use code tags when posting code (the [CODE/] button).

One mistake that I can see is that once you concatenate the first two parts together you end up with

...'Closed'Where...

You need a space in there for it to work. I tend to put the space at the end of the line, so I would do

DoCmd.RunSQL "UPDATE Customers SET Customers.CustomersStatus = 'Closed' " & _
"Where (Customers.CustomersStatus) = 'Invoiced' and (Customers.CustomerID) = " & Me.Invoice_ID.Value & ""

Notice the space between the single quote and the double quote at the end of the first line.

Also, is Me.Invoice_ID bound to a text field or a number field? You have an empty set of double quotes at the end which makes me wonder if you meant to include a single quote between them. If it is a number field, then you don't need the ending single quote (which means you can also remove the double quotes that you added at the end, although this won't affect the execution of the code). If it is a text field, then you need to add a single quote after the equals sign and in the middle of the two double quotes at the end. Here is an example for a number field:

DoCmd.RunSQL "UPDATE Customers SET Customers.CustomersStatus = 'Closed'" & _
"Where (Customers.CustomersStatus) = 'Invoiced' and (Customers.CustomerID) = " & Me.Invoice_ID.Value

and for a text field

DoCmd.RunSQL "UPDATE Customers SET Customers.CustomersStatus = 'Closed'" & _
"Where (Customers.CustomersStatus) = 'Invoiced' and (Customers.CustomerID) = '" & Me.Invoice_ID.Value & "'"

[jazee007]

DoCmd.RunSQL "UPDATE Customers SET Customers.CustomersStatus = 'Closed'" & _
    "Where (Customers.CustomersStatus) = 'Invoiced' and (Customers.CustomerID) = " & Me.Invoice_ID

I got this error " Compile error Method or Data Member not found"

The CustomerID is an AutoNumber Field found in the Customer Table and the Invoice_Id is a number Field found in the Sales Table. But it didn't work even without the quote.

[Seth Schrock]

Please use code tags when posting code. This is very important!

Do you have Option Explicit set? It would be at the very top of your module, the second line. I also noticed that you still don't have the space that you need to separate the WHERE clause from the code before it.

There is another way to run queries from code that I like to use as it tells you if there is a problem with the SQL code itself. Try copying and pasting the following and see if it works:

Dim db As DAO.Database
Dim strQuery As String

Set db = CurrentDb
strQuery = "UPDATE Customers SET Customers.CustomersStatus = 'Closed' " & _
"Where (Customers.CustomersStatus) = 'Invoiced' and (Customers.CustomerID) = " & Me.Invoice_ID

DoCmd.SetWarnings False
db.Execute strQuery, dbFailOnError
DoCmd.SetWarnings True

[zmbd]

jazee007:

Hello and Welcome!

First:
Please start off with following the first few parts in the following: > Before Posting (VBA or SQL) Code Seth has started you off on this path; however, the link provides a few more pointers.

Next:
You've fallen into what I call the MicroSoft programmer's trap. What this is, is creating the parameter string directly in the command (as you've done). This makes it very difficult to troubleshoot when the command fails.

To "fix" this let's modify your First code block a tad:

'These are only at the top of each module - ONCE
Option Compare Database
Option Explicit
'

Private Sub cmdUpdateSales_Click()
    '
    'a string for your eventual command.
    Dim strSQL As String
    '
    'setup an error trap
    On Error GoTo z_error_trap
    '
    'Define the value for your SQL string
    strSQL = "UPDATE Customers " & _
            "SET Customers.CustomersStatus = 'Closed' " & _
            "WHERE ((Customers.CustomersStatus = 'Invoiced') " & _
                "AND (Customers.CustomerID = " & Me.Invoice_ID.Value & "))"
    '
    '> for debuging
    '>
    Debug.Print "strSQL = " & strSQL
    Stop
    '> Press <ctrl><G> for the Immediate window - check the resolution of the string.
    '> Now you can either [F8] to step the code, or [F5] to let it run
    '
    DoCmd.RunSQL strSQL
z_cleanup:
Exit Sub
z_error_trap:
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
        "Error Detail: " & Err.Description, vbCritical, "Oh Bother - Error Trap"
    Resume z_cleanup
End Sub

Note the stop, your code will STOP running on this line, please press <ctrl-g>. We need to see the value you get for strSQL in the immediate window - a simple cut and paste from the Immediate to a post on this site.

Please let the code finish running by either [F8] or [F5], report any errors you get. PLEASE the EXACT NUMBER and the EXACT TEXT of the error message. Please, do not shortcut the message nor leave the number out as both are importaint to understanding the issue at hand.

[jazee007]

Thanks a lot for your help. Hope to resolve it soon.

I tried both code from Seth and ZMBD. I still got the Compile Error. I don't understand why. The name of the field in Sales table is "Invoice_ID " with Data Type "Number".

When I run the code, it stop and highlight the following

.Invoice_ID

then I got a menu "Compile Error - Method or Data Member not found".

Please is it necessary to put the ".value" after the

.Invoice_ID

. In the beginning of the post I use".value" cause I have thought for number, we should put the ".value"

Moreover in the immediate window there's nothing written.

[zmbd]

As you did not provide the line, I can only guess that the point of error is & Me.Invoice_ID.V alue as given on line 18 of the code I posted?

Well,
More than likely that field is not part of the recordset or control collection of the form.

That is to say:
If the form is Bound to the table via a query, then the query doesn't have that field as part of it.
If the form is Bound to the table, then the field is not named as you have it named.
If the form is Bound and you somehow have two controls with that same name (wow... I'd like to see that) then Access is choking on the duplicate.
If the form is un-bound then there does not exist a control with the name you've called.

[Seth Schrock]

Given that the Me. expression was used instead of Me!, I would say it would have to be something with the control. Possibly the control is named something like Text1 or txtInvoice_ID but is bound to the Invoice_ID field.

[zmbd]

I know that there is alot of debate about the "bang" v "dot" and when to use them.

In either case, if the field name was available OP wouldn't get this error. Although the bang will refer to controls in normal usage, V2003 and newer - If the form is bound and one uses a bang to refer to the control and the control is not there by that name; however a record set field has that name then MSA will try to use the field value.

Caused me all sorts of headache.

Could have been do to the bound v unbound of the controls. In one case the Bang works and the other it doesn't.

Besides, it appears that MS is pressing towards all dot with a few exceptions for subforms and the new navigation control.

In this case, I think the bang/dot is a red-herring and we need for OP to take a careful look at what the form is bound to (or not) and what all of the control names are.
-

[jazee007]

ZMBD, you were right. I found the mistake. The form is Bound to the table via a query and the query doesn't have that field in it. I haven't inserted the field caused Other field have been grouped. When the field "Invoice_ID " is inserted, it expands the query causing too many records to view.

The code works now. Thanks.

But on my side I need to rewrite the code. Cause there are too many records to click on. Can you help me to write it?

The idea is as follows. "On Click" the code below will run and do the following action:

DoCmd.RunSQL "UPDATE Sales SET Sales.Action = 'On-Order'" & _
"WHERE (Sales.Action)= 'Re-Order' and (Sales.Barcode_Of_Goods)='" & Me.Barcode_Goods & "'"

Now that the field "Action" have been changed to "On-Order" can we add a code like

look for a record in table Customers 
where [Invoice_Id]=[Customers].[CustomerID] 
Then 
Edit Record 
Set the Field CustomersStatus its value to "Closed"

.

[zmbd]

Glad you found the issue.

Your subsequent request for help should be started in a new thread as we prefer to keep to the one-question/problem per thread rule.
-z

[Oralloy]

Hey, jazee007,

As a courtesy for those of us who read posts and learn from them, can you mark the "correct answer", please?

When you do, it helps other developers who are using the site to understand the core problem and ultimate solution, without having to guess.

Cheers,
Oralloy

[jazee007]

Sure... both Seth and ZMBD help to find the mistake. THe Invoice_ID field was missing from the query. Now that I inserted the "Invoice_ID " It works. I used the following code

Private Sub cmdUpdateSales_Click()

 DoCmd.RunSQL "UPDATE Customers SET Customers.CustomersStatus = 'Closed' " & _
 "Where (Customers.CustomersStatus) = 'Invoiced' and (Customers.CustomerID) = " & Me.Invoice_ID & ""

End Sub

It works nice. Thanks for all and sorry for the delay to reply. I will use another thread for the other issue. Thanks again.