Move record to another table & delete from main table

[Clifford86]

Hello,



I have a table that has some records. There may be times that it will be needed to delete a record from the table. I would like to keep a history of this record and date it was deleted. There is one Piece of information I do not want saved in the 2nd table yet still need it deleted from the main table.

Example:

tblMain
ID
FName
LName
User
NoteAboutUser
PrivateInfo


tblMainSuspended
ID - (note: in this table this is only a number field and NOT an AutoNumber and also not really required to be kept).
FName
LName
User
NoteAboutUser
DateSuspended
SuspendedNote


I have a form and it has a button to be used to complete this task on click. When this button is clicked. I would like a popup asking to confirm the action (Suspend or Cancel) options.
"Cancel will return to the form."
"Suspend will in turn double check if we want to proceed "Yes or No" option.

"YES" then ask for a reason "SuspendedNote" (REQUIRED). Then it will copy the current record from tblMain to tblMainSuspended and delete the field "PrivateInfo" and add the reason to the field "SuspendedNote". Upon completion refresh and return to the form.
"NO" return to form.

Can someone please lead me in the right direction of code to use? Thanks

---
Sincerely,
Clifford86

[June7]

Strongly advise not to do this. Instead of 'moving' record, just have a field that flags it as 'suspended'. A date/time field can serve that purpose.

[Mickjav]

Strongly advise not to do this. Instead of 'moving' record, just have a field that flags it as 'suspended'. A date/time field can serve that purpose.

Agreed Dont delete data if you have to then look at the table design

[accesstos]

Hi all!

An other (more normalised) option is to keep in tblMainSuspended only the ID from the tblMain and the data for the suspend, without any deletion in tblMain.
Then, in place of tblMain, for the active users, you can use a query that pulls those records from tblMain which doesn't exists in tblMainSuspended. For the suspended users, you can use a query that pulls the data from the two tables, with an inner join on ID.

So, you have all information available using those that you want in any case.

Cheers,
John

[Clifford86]

Thanks for the information. These tables are small tables. In fact they are only used for user login information. There's no real relation to the main data tables in the database. I was wanting to move the record from tblMain to tblMainSuspended that way I wouldn't have to go and change any other code in the database that checks user access rights and mess up the whole database.

---
Sincerely,
Clifford86

[June7]

Write code to 'move' records or edit existing code. Either way, writing code.

Moving requires two SQL actions - one to copy data (possibly with INSERT SELECT) and one to delete.

[Clifford86]

Some small help needed please. I have a query and I am trying to get an expression to work, if possible.

I have a filed that I use an expression like the following.
Message: "Address" & " " & [Address] & "for" & " " & [FName] & " " & [LName] & "."

This works the way I want.

However I would like to add text from an unbound text box that is on a tab in a tab control on a form.

Is this possible and if so how should the expression be? Also is it possible to add text formatting like to bold text in an expression?


Thanks
---
Sincerely,
Clifford86

[June7]

Yes, expression in query can reference controls on form. Position on tab control is irrelevant.

However, text emphasis such as bolding gets a little tricky. Options:

1. data entered into a memo field set for Rich Text using Rich Text toolbar and displayed in textbox set for Rich Text

2. expression in textbox set for Rich Text and concatenating HTML code tags, something like:
="Address " & [Address] & " for " & [FName] & " " & [LName] & ". <b>" & [form control name] & "</b>"

[Clifford86]

Yes, expression in query can reference controls on form. Position on tab control is irrelevant.

However, text emphasis such as bolding gets a little tricky. Options:

1. data entered into a memo field set for Rich Text using Rich Text toolbar and displayed in textbox set for Rich Text

2. expression in textbox set for Rich Text and concatenating HTML code tags, something like:
="Address " & [Address] & " for " & [FName] & " " & [LName] & ". <b>" & [form control name] & "</b>"

Well Maybe it don't like me. lol I get 2 errors.

1st:
Suspend: "Reason for suspending" & " <b>" & [FName] & " " & [LName] & "</B>?"

Just shows as :: <b>FName LName</b> :: Instead of FName LName?
I think I am doing something wrong here. Not real sure.


2nd:
SuspendReason: [FName] & " " & [LName] & " " & "is being suspended because" & " " & [txtSuspendReason]

Returns a parameter value? error.

Thanks
---
Sincerely,
Clifford86

[June7]

I said HTML coded expression had to be in textbox. Won't work in query.

[Clifford86]

I said HTML coded expression had to be in textbox. Won't work in query.

Thank you very much June7. I got it all working like I wanted. Thanks