Update query help needed

[Dave]

DoCmd.RunSQl "UPDATE [tblCustomers] SET [LastOrderDate]=#" & Me.OrderDate &
"#"

How can I alter this query to include critera to only update a single
record?
I have this avaiable on the form this code is behind:
me.CustomerID

Any way to include that criteria in the above query?

Thanks
D

 

[John Spencer]

DoCmd.RunSQl "UPDATE [tblCustomers] SET [LastOrderDate]=#" &
Me.OrderDate & "# WHERE CustomerID = """ & me.CustomerID & """"

OR if customerId is a number field



DoCmd.RunSQl "UPDATE [tblCustomers] SET [LastOrderDate]=#" &
Me.OrderDate & "# WHERE CustomerID = " & me.CustomerID

Of course, the question might be, why are you doing this. You should
always be able to get the LastOrderDate for any customer with an
aggregate query.

SELECT CustomerID, Max(OrderDate)
FROM Orders
GROUP BY CustomerID

That should always give you the latest date. If you use your statement,
you have to remember to update the LastOrderDate every time and also you
need to avoid changing the date if the current LastOrderDate is after
the date you would be updating to.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Dave]

John,

That code works great and does just what I want
EXCEPT
Can I avoid the Popup warning (You are about to update 1 record)??

Now your question is worth answering because I may be going about this the
hard way (if it works it is right - right??) - However I can't seem to
explain myself so that my need is clear.

Let me try again.

"tblCustomers"
has all my customer info plus a field for "LastOrderDate" (Meaning the Last
time they placed an order)

"refLastOrderDate"
is a single row table where the admin can limit how long a customer list is
getting by entering a date that he wishes to have no customers listed who
have not ordered since that time

my query to open the form "frmCustomerList " filters great based upon the
above 2 objects

HOWEVER there is currently no way to enter the "LastOrderDate" in
"tblCustomers"

"tblOrders" has a field called "OrderDate" which when an order form
("frmOrders")opens is populated with todays date.

When I close (Go to Print Preview) "frmOrders" I want that OrderDate (Todays
date)
to populate the field "LastOrderDate" in "tblCustomers" FOR THAT CUSTOMER
so that the filter will work.

SO I just put the code you gave me in the print button ON Click event code.
Which now looks like this:
--------------------------------------------------------------
Private Sub cmdPrint_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim stDocName As String

stDocName = "Order Invoice"
DoCmd.OpenReport stDocName, acPreview
DoCmd.RunSQL "UPDATE [tblCustomers] SET [LastOrderDate]=#" &
Me.OrderDate & "# WHERE CustomerID = " & Me.CustomerID


'DoCmd.Close acForm, "frmOrders", acSaveYes


End Sub
----------------------------------------------------------------------------
It works and does what I want. (Except for the warning) so I am happy
But if there is an easier way......I am all ears

Thanks much,

Dave

DoCmd.RunSQl "UPDATE [tblCustomers] SET [LastOrderDate]=#" & Me.OrderDate
& "# WHERE CustomerID = """ & me.CustomerID & """"

OR if customerId is a number field



DoCmd.RunSQl "UPDATE [tblCustomers] SET [LastOrderDate]=#" & Me.OrderDate
& "# WHERE CustomerID = " & me.CustomerID

Of course, the question might be, why are you doing this. You should
always be able to get the LastOrderDate for any customer with an aggregate
query.

SELECT CustomerID, Max(OrderDate)
FROM Orders
GROUP BY CustomerID

That should always give you the latest date. If you use your statement,
you have to remember to update the LastOrderDate every time and also you
need to avoid changing the date if the current LastOrderDate is after the
date you would be updating to.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

DoCmd.RunSQl "UPDATE [tblCustomers] SET [LastOrderDate]=#" & Me.OrderDate
& "#"

How can I alter this query to include critera to only update a single
record?
I have this avaiable on the form this code is behind:
me.CustomerID

Any way to include that criteria in the above query?

Thanks
D

 

[John W. Vinson]

John,

That code works great and does just what I want
EXCEPT
Can I avoid the Popup warning (You are about to update 1 record)??

Now your question is worth answering because I may be going about this the
hard way (if it works it is right - right??) - However I can't seem to
explain myself so that my need is clear.

Let me try again.

"tblCustomers"
has all my customer info plus a field for "LastOrderDate" (Meaning the Last
time they placed an order)

That's exactly what John is questioning.

Many would say that the LastOrderDate field *SHOULD NOT EXIST*.

It's redundant. It can be calculated whenever it's needed from a query on
tblOrders. It could be *WRONG* in the table; it's an editable field, so
someone - intentionally or accidentally - could edit the LastOrderDate field
to something other than the actual last order date; or you could edit the
Orders table to enter a new order with a date later than the LastOrderDate
field in the customers table. If this entry bypasses your form code your
LastOrderDate is now *wrong*.

Why is it necessary to (redundantly!) store this field in the customer table?
It's NOT an attribute of a customer; it's data which can be found in a
different table... and should be!

John W. Vinson [MVP]

 

[Dave]

John,
I am not arguing the approach - I just do not know how to impliment what
John S suggested.
I am not trying to frustrate you - I just am not getting it yet.
I appreciate your patience though.

This update query (although it basically works) is even to my novice eyes a
"Hack" requiring more effort then neccessary.
I would reither get it right.

dave

 

[John W. Vinson]

John,
I am not arguing the approach - I just do not know how to impliment what
John S suggested.
I am not trying to frustrate you - I just am not getting it yet.
I appreciate your patience though.

Create a Query as suggested in your other thread in the other newsgroup.
Unfortunately I deleted the thread after seeing that you'ld gotten (albeit
apparently not tried or accepted) a good answer.

In short - create a query using

MostRecent: (SELECT Max([datefield]) FROM Orders WHERE Orders.CustID =
Customers.CustID)

in a vacant Field cell to display the most recent date.

John W. Vinson [MVP]

 

[Dave]

Yep John,
I just got it figured out? (what ALL of you were trying to tell me sunk in
while sleeping).
Had to get up in the middle of the night just to test it. lol

thanks so mush for hanging with me on this.

works great now.
Dave

John,
I am not arguing the approach - I just do not know how to impliment
what
John S suggested.
I am not trying to frustrate you - I just am not getting it yet.
I appreciate your patience though.

Create a Query as suggested in your other thread in the other newsgroup.
Unfortunately I deleted the thread after seeing that you'ld gotten (albeit
apparently not tried or accepted) a good answer.

In short - create a query using

MostRecent: (SELECT Max([datefield]) FROM Orders WHERE Orders.CustID =
Customers.CustID)

in a vacant Field cell to display the most recent date.

John W. Vinson [MVP]

 

[John W. Vinson]

Yep John,
I just got it figured out? (what ALL of you were trying to tell me sunk in
while sleeping).
Had to get up in the middle of the night just to test it. lol

So that explains the flash of light I saw over the horizon before dawn...? <g>

Glad you got it working.

John W. Vinson [MVP]