Commiting changes in past records

[spleewars]

Got a complicated challenge (better define my last challenge) but don’t have a clue where to start: I’ll try to be accurate as possible. Apologies I might be asking for a lot of coding but this has been bothering my for a lot of weeks trying to do it myself the ‘simple’ way but failed all over every time. This is all about automatic changes in particular field of a past payment.



Got form “Report” and subform “Bank”. The purpose of this form is to input payments made that day. In report form, the user set 1 daily record to save day details and the subform is filled with tens of records about customers and payment amounts.

In”Report” I got 2 particular fields:

Date: is set daily by the user
Dailyid: (as autonumber related to the subform)

In “Data” I got 2 important fields:

OrderID (order id inputted by the user which is the identity of the client)
Status: (combo box with option1, option2 and option3)

Example: on 18-Feb-2012 customer came to process a payment. A record in “Report” is open with the daily date and another record in the subform is set to mark OrderID ‘4512452’ and ‘Status’ field, which is set by the user as default to‘option1’. On 23-Mar-2012, the same customer is about to process another payment. Apart the same procedure is performed, when the user is about to save the new payment details, the field ‘status’ of the payment before (which in this case made on 18-Feb-2012), from opion1 is set automatically set to option3…

Thanks everyone for help
Clara

[R_Badger]

A couple of points of confusion.

You mention data, (I assume this is part of the datasource for the subform "Bank" but I could well be wrong), but don't mention where it ties in

If I am reading this correctly, what you want is for the Status field to default to the same value used for the last occurrence of the same OrderID (being the client) ? is this correct? If so how do you want it to handle new clients?

[spleewars]

Hi Badger, thanks for your reply hope you will be better with your writing

(added 2 pics of the form describing better my idea)

actually, you're not entirely accurate but i have to blame myself as well coz i might had to explain a little better. To picture my real scenario, in "Status" i got 3 options as a combo box (instead of option1, option 2, option3):

- waiting
- paid
- archive

Going to my previous example, when the user inputed data in 28-Feb-2012, he set the "status" as PAID (or can even set it as waiting). When the customer returns On 23-Mar-2012, as the user is going to make a new record, the previous's record "status" (in this case the record made on 28-feb), from PAID or WAITING goes automatically to ARCHIVE...

Please let me know if i fail to make my point clear
Thanks

- Report form with "bank" subform / status is set to paid

- This is log of the payment of the client order no 82948. When new payment entered on 23-Mar- . 2012, the record's STATUS of the previous record (blue field) went from Paid to Archive.

[R_Badger]

Here's hoping this doesn't come out as a wall of gibberish!

We need to know a bit more about your table structure to be entirely certain, but it should be entirely do-able.

My initial approach to this would be an update query something along the lines of:

UPDATE [table_name] SET [Status]='Archive' WHERE [ORDERID]=" & Me!report.form!bank.Form!orderid & " and [date]<" & date() and [status]<>'Archive'"

The idea is that using the orderID referenced in the subform the update will only affect entries regarding the current orderID, earlier to today with a status that hasn't already been archived.

You might find the reference to your subform slightly dodgy (I freely admit that isn't one of my strongest areas, I don't tend to use subforms) and I would test the update query on sample data first before using it on live data to make sure it functions 100% as intended.

You can test the where clause by using it as a select query:

SELECT * FROM [table_name] WHERE [ORDERID]=" & Me!report.form!bank.Form!orderid & " and [date]<" & date() and [status]<>'Archive'"