How to "Cancel the record", Please help!

[dbnewbie2555]

Hi,

Can anyone please help me for my problem.

I want to make a button to cancel the record that has been made. However, I do not want to delete that record, still want to keep that record as for reference later when I pull the data to a report view.

For example, I have a record 1, record 2, and record 3, entered in the database, then customer for record 3 just want to cancel the job. So, I need to cancel the record 3 as customer wish. But instead of Deleting that record. I want to just cancel that transaction record, and want Access still keep the data of record 3. Later when I come to check in a report, I can see how many records have been cancelled.

Please give me advise.

Thank you very much in advance.

Best regards,

Karin

 

[GHolden]

Hi, one way to do this is to have a Cancelled field in the record and set this to True for cancelled jobs. So when looking for 'live' records you can SELECT X,Y,Z WHERE Cancelled = False, or for Cancelled records you can use Cancelled = True.

Or you can have a cancelled table and copy cancelled records to this table and delete from the original (I wouldn't do it this way myself).



There are two ways to write error-free programs; only the third one works.

 

[Andrzejek]

I would expend on GHolden's idea:
Add a field to your table called "Status" where you can keep, well... the status of your record. Most of the time they will be 'A' (Active), but you can have 'C' (Cancelled), or On-Hold ('H'), Archived ('Z') or whatever.

Have another table "Status" where you can have:[tt]
StatusID Desc
A........Active
C........Cancelled
H........On-Hold
Z........Archived
[/tt]

Have fun.

---- Andy

 

[GHolden]

Good Point Andrzejek, will save problems later if you need more status.

There are two ways to write error-free programs; only the third one works.

 

[dbnewbie2555]

Dear GHolden and Andrzejek,

Thank you sooo much for your kind help.

I think I have the idea of what you have suggested, unfortunately, I do not have enough skill to complete this work.

Would you guys mind giving me kind of a steps by steps tutorial please. Then I will try to follow your steps. Or, if you can create the sample in the file I attached here, and then I will try to backward-learning from what you have done on top of my existing db file.

http://www.mediafire.com/?9ze740ku916dnse

Thank you very much in advance once again for your kind help.

Best regards,

Karin

 

[GHolden]

Hi,

You need to do the following:

Create a table tblStatus with two columns

StatusID
StatusText

Add two Records ID=1 Text = 'Active' and ID=2 Text = 'Cancelled'

In your tblDetails add a column called Status as Text (in design view)

In the Lookup tab at the bottom for this column

Set

Display Control to ComboBox
Row Source Type to Table/Query
Row Source to tblStatus
Bound Column to 1
Column Count to 2
Column Heads No
Column Widths 0;2cm

This basically tells Access to look up the value for this field from the tblStatus table, store column 1 (StatusID) in the field, show both fields in the combobox, but hide the first (setting column width to 0)

Once you've done this, if you open the details table in datasheet view when you click on the Status column you should see a dropdown list with Active and Cancelled in the list, when you select one the value 1 or 2 will be saved in the Status Column.

When you get this far post again and I'll tell you how to add to your form.


There are two ways to write error-free programs; only the third one works.

 

[dbnewbie2555]

Dear GHolden,

Thank you very much for your kindness. Spending your precious time giving me the steps by steps tutorial. I will try to follow that. Any further issue, will let you know.

I am really appreciate you help.

Best regards,

Karin

 

[Andrzejek]

GHolden,

Don't you think it would be better to have StatusID as Number instead of Text?

Since you proposed:
Create a table tblStatus with two columns

StatusID [blue](Number)[/blue]
StatusText [blue](Text)[/blue]

Add two Records ID=1 [blue](<- Number)[/blue] Text = 'Active' and ID=2 [blue](<- Number)[/blue]
Text = 'Cancelled'

In your tblDetails add a column called Status as Text [blue]Number[/blue] (in design view)

It may be a small difference but I always prefer to join tables by the number field instead of text (or date).

Have fun.

---- Andy

 

[GHolden]

Hi, sorry typo, Status in the Details should be a number not text.

Thanks for pointing out.

There are two ways to write error-free programs; only the third one works.

 

[VicM]

Hadn't seen this post before I entered mine.

I do this all the time. I never allow the user to delete records from a table. I just include a boolean field in the table titled 'Hidden' or 'Archived'. On the form I supply a 'Delete' button which just sets the boolean 'Hidden/Archived' field for the record to True.

Then all my queries, forms, or reports will select all records where the 'Hidden/Archived' field is false. One can then easily create queries, forms & reports showing the 'Hidden/Archived' records. Or for that matter, all records by not filtering on the 'Hidden/Archived' field.

Vic

 

[dbnewbie2555]

Hi GHolden, Andrzejek and VicM,

Thank you very much for your post. I am very appreciate that.

Sorry for very late response to your post, I have been very busy with other jobs.

I try to understand what you guys trying to suggest me, but I don't get the result as expected. Maybe I have very little knowledge about Access. I will need to learn a lot more.

For this question, I think I better close the case first for now, and if I have any issue in the future I will make a new thread then.

Thank you so much for all your kind support, GHolden, Andrzejek and VicM.

All the best to everyone.

Best regards,

Karin