move row from one table to another

[domyboy]

Hi All,

I'm having some problems trying to move a row from on table to another using a button.

Basically I have two tables. One has names on it(called Customers) the other is blank(called DeletedCustomers).

I have a Form which has a drop down combo box and three text boxes. If you click on the combo box and select a customer; the firstnames, lastnames and ID number will be shown in the three text boxes. There is also a button which will add names to the customer table if you have typed names in the text boxes.

What I would like is to have another button which when pressed, depending on which customer name is selected in the text boxes, will move the name from the Customer table to the DeletedCustomer table.

This is the code I have, which is more than likely wrong..

Private Sub Command38_Click()
On Error GoTo Err_Command38_Click
If MsgBox("Are you sure you want to delete this record?", vbExclamation + vbYesNo, "") = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
INSERT Into DeletedCustomers(Column1, Column2)
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Else 'user clicked no
MsgBox "Deletion was aborted.", vbInformation
End If

Exit_Command38_Click:
Exit Sub
Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command36_Click

End Sub


Any help on this would be greatly appreciated
Cheers
Dom

 

[Alisa]

WHy not just have one customers table, with a field for deleted? Then when you click the button, you can just update that one field instead of moving the entire record.

 

[The_Doc_Man]

Take Alisa's answer one step farther.

In your primary table, add a Yes/No that says "CurrentUser" (or says "DeletedUser", you choose the polarity.) Have a checkbox or other mechanism on your form to mark the user for deleted. Add a date with that to show when the deletion occurred.

Now, phase 2. If the form was based on the table, instead base it on a query based on that table, but for which there is a filtering criterion "Where DeletedUser = FALSE" - if you chose the polarity such that TRUE means Deleted. Then the next time you open the form, you don't see deleted entries. And in fact, if you delete an entry, then navigate to another entry and try to navigate back, it won't be there due to the automatic REQUERY that occurs with navigation.

Now, phase 3. You really don't need to move records unless your business model requires it. From the Access/SQL viewpoint, the records can stay where they are, marked for delete but not truly deleted. But it sometimes happens that your business rule says to clean out that table for real. OK, to do that, every so often you can run an append query from the source table to your history table with a filtering criterion "Where DeletedUser = TRUE" (again, assuming you chose the polarity where TRUE = deleted.)

"Run the query every so often" - gives you the chance to go back and undelete the record before you commit your deleted records to the "deleted" table. And you get to do the cleanup in an orderly manner at a time of your choosing, like just before you do your regular "other" maintenance. (You DO schedule yourself some time for Repair and Compact on a regular basis... don't you?)

Do your table-shifting, then repairing, and finally compaction at a time when no one is on the database. Search this forum for methods that will allow you to "Kick out users" at chosen times. (Quoted because that would be the search topic.)

 

[domyboy]

Thanks for the replys.They were really helpfull.
I did as you said but I'm having trouble creating a button that once pressed will mark the selected person in the table as deleted. What kind of code will I insert into the button.
I created a query table called CustomerQueryTable based on CustomerTable but with the filter and the feild DeletedUser added.

I was thinking along the lines of:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Set CustomerQueryTable[DeleteUser]= True

DoCmd.ApplyFilter , DeletedUser = True

Exit_Command17_Click:
Exit Sub
Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

Am I along the right lines?
I just started using Access and VB a few days ago so my knowledge is tad limited.

Thanks Again
Dom

 

[Alisa]

You need to write an update statement. There are plenty of posts on this board, and tutorials, etc. that you can search for. To get you started, it will look something like this:

Currentdb.Execute("UPDATE yourtablename SET DELETED = -1 WHERE YourPrimaryKeyField =" & PrimaryKeyOfCurrentRecord & ";"

 

[domyboy]

Thank you very much Alisa/The Doc Man. Your help is greatly appreciated. I got it working in the end with your code after about two weeks of banging my head off my desk.

I'm actually stuck on another proplem now, , as i have developed the last program further to include a full cable schedule and project database.

Basically the idea is that i can create cables and assign it to any one on the tblEmployees. The employees have a price per hour associated with them so once i have assigned a cable with an employeee and i mark it as 'done' along with the time taken to complete it, i can calculate the total time taken to complete all the cables that have been 'done' . Also there is another field which is 'project' and this further divides the cables into whatever project. So you can calculate how much has been done on a specific project and i can also calculate how many hours an employee has done etc

The problem i have is that i dont know how to calculate the the sums of the employees or projects depending on what project or employee has completed or 'done' the cable. Im guessing the code would be something like:
For project Calculation:

If [Project] == (the projects name ie "EEEE")

do Sum (total of all values in Hours Worked field but only if they have Project field name EEEE) not sure of the code of this at all

For individual calculation:

If[Employee] == (an employee name ie "Dave Smith")

do sum(total of all values in Hours Worked field only if they have name Employee field name Dave Smith) again not sure of the code

Im also not sure where the code should go, should it be in an SQL statement in a query or should it be actual VB code but where?
also on a side step whats the code for something that is the same as something not making it equal too, im from a C++ background and it was == in that language?


Any input in this again would be greatly appreciated as i know it can be annoying when less experienced access programmers ask questions that seem simple. I have looked all over this website and many others but i cant find what im looking for, there was a few close examples but i couldnt get them to work. I have gained other knowledge from this website so i did learn something.
I was going to attach my file but its over the max size for the forum.

Thanks Again
Dom

 

[Alisa]

Dom, you should look into doing totals queries-you click the sum ("E") button at the top when you are in the query design grid. You can group by project, employee, etc., and the sum hours. There should be plenty of threads on the forum about how to do totals queries to get you going in the right direction . . .

 

[domyboy]

Ahhh. Thanks Alisa, the problem was i was searching for the wrong thing as i didnt know what the specific term was that i was looking for. All this for a little E button haha.

Thanks
Dom

 

[domyboy]

Hi again people,

Thanks Alisa For your replys. I got the sum working but now im stuck on a filter again.

Basically I have a form with a combo box on it. The combo box displays names and I want, when a name is selected from the combo box, the name selected put into the criterea box of a specific field of a query.

This will then filter the query depending on what is selected in the combo box, everytime I do this the form will not load corretly or I get errors. I was looking at similar examples on this forum but I cant follow them or get them to work. This is one example:
http://www.access-programmers.co.uk/forums/showthread.php?t=150957&highlight=variable+filter

Again any help with this would be greatly appreciated.

Cheers
Dom

 

[Alisa]

First of all, does the query work without the filter?
Second of all, does the query work with the filter if you put it in manually (i.e., by just adding a where clause in the query grid)?

Those two steps have to be working before you worry about the combo box.

 

[domyboy]

Thanks for the quick reply Alisa

Yes the query works without the filter. Also if I put "dave" into the criterea box the query will only show records with the Name field of dave. This works fine but if I try and put this in the citerea box
[Forms]![EmployeeForm]![Combo22]
it does not work.
I have not added a WHERE clause in the query grid, im not sure how to do this.


Edit:
I was just looking at where clauses. So basically a filter is a where clause, so if i put:
WHERE Name = "dave"
it should work just the same, I might try this aswell:
WHERE Name = "[Forms]![EmployeeForm]![Combo22]"

 

[Alisa]

Assuming your combo has 2 columns, Forms!EmployeeForm!Combo22 likely refers to the key field, not the name field, so you would need to place the criteria on the column that contains the key rather than the name.

 

[domyboy]

Thanks for the reply Alisa but i dont understand what you mean.
Should i change the code to
"[Forms!EmployeeForm!Combo22].[Column(1)]"

Thanks Again
Dom

 

[Alisa]

What is the record source of your combo box?

 

[domyboy]

What is the record source of your combo box?

The Form(EmployeeForm) which has combo box(Combo22) has the record source of the query(EmployeeQuery) that I am trying to filter.



Thanks
Dom

 

[domyboy]

I seems to be kind of working by using
"[Forms!EmployeeForm!Combo22].[Column(1)]"
but instead of the name being selected from the combo box it is popping up a msg box and asking the name to be typed in.

 

[Alisa]

What is the SQL of the EmployeeQuery?

 

[domyboy]

What is the SQL of the EmployeeQuery?

SELECT EmployeeQuery.Installer, EmployeeQuery.Completed, EmployeeQuery.HoursToComplete
FROM EmployeeQuery
WHERE ((( EmployeeQuery.Installer)=[Forms]![EmployeeForm]![Combo22].[Column(0)]) AND (( EmployeeQuery.HoursToComplete) Is Null));

 

[Alisa]

So if you go all the way back into the original EmployeeQuery, what is the SQL there? (What you have posted above is the SQL of a select query that is using the EmployeeQuery).

 

[domyboy]

Yes i have a main query which holds all the information and has no filters or anything in it, then there is 3 querys that have seperate filters in them for each form and task. They get there information from the main query.

The 3 querys have each got a filter for different tasks, like the one i am trying to do now. it is to filter out everyone apart from the name selected in the combo box.

I tried to make it simpler by just saying the one query. Also i shortened the query's and changed the names to make them simpler but really this is what the main query SQL looks like:

SELECT tblCables.CableNumber, tblCables.CableCompany, tblCables.CableLength, tblCables.CableSize, tblCables.FromNode, tblCables.CableCores, tblCables.ToNode, tblCables.CableType, tblCables.LabourType, tblCables.Project, tblCables.CableInstaller, tblCables.Completed, tblCables.HoursToComplete, tblCables.DeletedCable
FROM tblCables;

One of the 3 query's(the one im trying to get to work is this one which will filter the name of the CableInstaller so that if i view the query it will only view the cables installed by the CableInstaller's name that i have choosen in the combobox) This has SQL:

SELECT tblCablesQuery.CableNumber, tblCablesQuery.CableCompany, tblCablesQuery.CableLength, tblCablesQuery.CableSize, tblCablesQuery.FromNode, tblCablesQuery.CableCores, tblCablesQuery.ToNode, tblCablesQuery.CableType, tblCablesQuery.LabourType, tblCablesQuery.Project, tblCablesQuery.CableInstaller, tblCablesQuery.Completed, tblCablesQuery.HoursToComplete, tblCablesQuery.DeletedCable
FROM tblCablesQuery
WHERE (((tblCablesQuery.CableInstaller)=[Forms]![ViewCableCardForm]![Combo22].[Column(0)]) AND ((tblCablesQuery.HoursToComplete) Is Null));


I shoudl have made this clear at the start but i thought i was just doing something simple wrong.
Thanks
Dom