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

[TheSmileyCoder]

Sounds like either you have some code or macros running or you have your form setup wrong. However I dont really have enough details to go on to form a more specific hypothesis.

Could you provide a screenshot of your form, that might help?

[spleewars]

@TheSmileyCoder
Hi thanks for your reply smiley coder :)

To make myself a little more clear, 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...

http://imageshack.us/photo/my-images/862/93429301.jpg/

Here is the "Report" form with "Bank" Subform:

http://imageshack.us/photo/my-images/198/23473788.jpg/

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.

Thanks and let me know if you require any more details
Clara :)

[Rabbit]

Use the before insert event to run an update query.

Why do you have a table that is essentially a bunch of dates? Not that a table of dates has no use but your date table doesn't seem to be used for anything.

[spleewars]

Hi Rabbit, thanks for your input. Actually there might be a little misunderstandin g but the 'log' form (http://imageshack.us/photo/my-images/198/23473788.jpg/) is a simple select query, just to show record details of the payment.

Form "Report" and subform "bank" are related to each other with RECORDID as autonumber in Report form.

Never used an update query so far - will be very happy if you can help me.

[Rabbit]

If you're going to be working in databases, you should learn SQL. There are a lot of tutorials out there. I like the one from W3Schools.

[Mihail]

Use an update query in order to update the Status field (for all records, for a certain user and for all dates before to actual date) to value "Archive".