Move Records Between Tables
Nov 22, 2005
hiya, im new to this sql scene..!
i have an MS Access db containing a few tables of 'calls recorded' and 'finished calls'.
(i want to move all 'Finished calls' to a seperate 'Finished Calls' table)
anyway, the thing i want to do is to move records from Table1 to Table2, i am able to do this using the following code...
SELECT * INTO Table2
FROM Table1
WHERE Finished='Yes';
this code does work, however it does not delete the records that were moved out of Table1 - they are still there, its more or less copying and pasting them..
any help would be much appreciated!
View Replies
ADVERTISEMENT
May 6, 2005
is there a way by which i can move one or more records from one form to another when clicked on the 'YES/NO' button ??? :confused:
thanks in advance.
View 5 Replies
View Related
Mar 29, 2006
Here's a tough one that has been driving me crazy! (Probably easy, but I don't want to admit it!)
I have a form with a listbox called "lst_exclist". The recordsource for this listbox is the following query:
SELECT tbl_collexcludereasons.priority, tbl_collexcludereasons.excname, IIf(tbl_collexcludereasons.enabled=-1,'Enabled','Disabled') AS enabled, tbl_collexcludereasons.priority FROM tbl_collexcludereasons ORDER BY IIf(tbl_collexcludereasons.enabled=-1,'Enabled','Disabled') DESC , tbl_collexcludereasons.priority;
Which basically gives me this with dummy data:
2 PIPELINE ENABLED
3 HELLO ENABLED
5 GOODBYE ENABLED
1 BAD DISABLED
4 GOOD DISABLED
6 LAST DISABLED
I had two command buttons, one up arrow and one down arrow. The up arrow is supposed to move the selected record on the listbox up by exchanging the next lesser priority number with itself. The down arrow does the same. The DISABLED records are supposed to be ignored on the move up and move down procedures, meaning only ENABLED records are allowed to exchange priority numbers, and thus move up or down on the list. I tried the following code:
Me!lst_exclisthidden = Me!lst_exclist
DoCmd.SetWarnings False
Dim startingnumber As Integer
Dim endingnumber As Integer
Dim nametochange As String
Dim getchangerst As DAO.Recordset
If IsNull(Me!lst_exclist.Column(0)) = True Then
MsgBox "Please choose an entry on the above list to move.", vbCritical, "Error"
Exit Sub
Else
End If
If Me!lst_exclist.Column(2) = "Disabled" Then
MsgBox "There is no need to move a disabled selection, please enable the selection to change it's priority.", vbCritical, "Error"
Exit Sub
Else
End If
startingnumber = Me!lst_exclist.Column(0)
nametochange = Me!lst_exclist.Column(1)
endingnumber = startingnumber + 1
If Me!lst_exclisthidden = acLast Then
MsgBox "You cannot move the bottom selection on the list down, please choose another one.", vbCritical, "Error"
Exit Sub
Else
End If
While DCount("*", "tbl_collexcludereasons", "Priority = " & endingnumber & " and Enabled = -1") = 0
endingnumber = endingnumber + 1
Wend
DoCmd.RunSQL "Update tbl_collexcludereasons set tbl_collexcludereasons.priority = tbl_collexcludereasons.priority - 1 " & _
"Where tbl_collexcludereasons.priority <= " & endingnumber & " and tbl_collexcludereasons.priority > " & startingnumber
DoCmd.RunSQL "Update tbl_collexcludereasons set tbl_collexcludereasons.priority = " & endingnumber & " " & _
"Where tbl_collexcludereasons.excname = '" & nametochange & "'"
Me.Refresh
Me!lst_exclist = endingnumber
Me!lst_exclisthidden = Null
DoCmd.SetWarnings True
But it seems to loop when it gets the the area it is checking for numbers because the highest records is disabled, so it's ignored. How can I make this work? Please help!
Thanks
Vassago
View 1 Replies
View Related
Jun 27, 2005
Hello. I have a question that seems simple but I can't get it to work. Any help would be appresiated.
I need a form that will pull the oldest record from one table, allow the user to add a few fields to the record, then paste the record to a different table. when it is done the original record has to be removed from the original table.
I have tried using a append Query, but it moves all of the records at once and doesn't allow data entry on each record. I'm not sure if this would be done by a Query, Form or Macro. I have laid out the nessacary data flow below incase there is an easier way to do this.
Persons A,B, and C enters data into table 1.
later person D pulls the oldest record from table 1, reads the data then adds fields to the record and records it to table 2. The information is then removed from table 1. Person D then goes onto the next record from table 1. When person D is done, there will be no data left in table 1.
Any Ideas? or can someone point me in the right direction for help?
View 3 Replies
View Related
Apr 26, 2014
I have an Equipment Form with a Supplier subform. The Supplier subform also has a subform, Equipment Pricing Records.
I have the ability to change the Supplier via a combo box, but Equipment Pricing Records become orphaned.
What I would like to do is when a Supplier is about to be changed, for the user to have the option to either a) delete the sub records, or b) the sub records to move across to the new Supplier.
I know I will need to use the after update event once the delete or move decision is made, but I can�t think how to identify the records and then either delete or move them to the new Supplier ID.
The EquipmentID will never change.
Fields are;
EQUIPMENT form;
EquipmentID
SUPPLIER form;
SupplierID
EquipmentIDFK
PRICING RECORD form;
PricingRecordID
SupplierIDFK
EquipmentIDFK
View 14 Replies
View Related
Aug 11, 2014
i have a form, where a user inputs a account number inputting the account number, then populates all the relevant table fields, into the form for the user to edit.however if there is more than one account, with that account number, how do i get it so they can cycle through on the form?
View 2 Replies
View Related
Mar 10, 2008
I have an order system whereby there is a "basket" table and an order detail table.
I want to use an append query to move all the records from the basket table to the order detail table.
However, I also need to mark each record that gets appended to the order detail table with an "Order Id" that has already been saved in an Order table.
I.e. I save the main details of an order into the order table then copy records from the basket table into the order detail table along with an order ID that comes from a form.
The order ID is an autonumber.
Thank you all so much in advance =]
View 3 Replies
View Related
May 8, 2013
I'm having trouble with one of my reports. attached is a screencap of my report. The second name which is highlighted i need to either nudge it to the right so it is not directly under the first name OR remove it completely but keep the times and a gap for the next record.
I am creating is a sign in database where staff sign in/out for each day of a week. the issue is when the staff member signs in/out in the morning and then does the same when they get back from lunch it is difficult to read. This report is only printed once a week and will not show after a time frame of 5 days.
So, i still need that name to show up for the next day.
View 1 Replies
View Related
Nov 29, 2013
I'm using a form with next record and previous record buttons that works fine but as the size of the database has grown it's clear I need a faster way to move through records.
My solution was to have an advance 10 records at a time button but I'm having problems when reaching the far sides of the records.
For example: If I'm on record 9 and I press go back 10 button then access tries to go to record 0 (which doesn't exist) and brings up and error. The same happens if I try to advance more than total number of records.
I found a solution to this which was to disable the button when the recordcount was less than 10. This works except now if I'm on say record 14 and want to go back to 4, i press the button, i goes back to record 10, the button is disabled so it can't go back further and i get an error.
View 3 Replies
View Related
Jun 28, 2013
Is it possible to move down records with the down button in a tabular form to the same field in the next record.
View 11 Replies
View Related
Aug 25, 2014
I have two listboxes on a form listbox1 and listbox2. listbox1 is populated by table1 which has 40 fields and 1000 records. listbox2 is populated by table2 which has identical structure to table1(same fields) but has no records.
For both listboxes Multi Select is set to simple. Rowsource is Select * statement from tables. Row Source Type is set to Table/Query.
I would like to select multiple records from listbox1 and add them to listbox2 and table2 or just table2. After selecting the records from listbox1 the user will click a button to add the records to table2. Also need to delete the values selected from table1 on same button click.
The database is saved with a .mdb file extension.
View 2 Replies
View Related
Oct 19, 2005
Hi all,
title says it all!
I've got a form with numerous controls and labels. When i first open the form, all looks fine - all labels are Black Arial 8 Bold. But when i start scrolling through the records, the text on three of my text labels gets bolder. I haven't a clue what is causing this - the labels are just standard - two are associated with controls and one is all on its own.
I have no code written to do this, and in fact i'm not sure you could write code to do this. I've tried renaming the labels, changing the font, deleting and recreating them, but it's always the same three that get darker.
Ideas?
P.
View 1 Replies
View Related
Sep 15, 2013
Is there a way in which someone can select a table from a drop down list and then search that table for a record then move that record to another table.
For example.
User selects 'Mikey's_table' searchs for a record then move this selected record to 'Mandy's_table'
(all the tables have the same structure etc. identical apart from the name of the table and records within)
I have the list of tables that all the records will be on and the users will know which table the record is in, i basically need to know if there is an ability to search for a record over multiple tables then edit that record and move it to another Table.
I have tried to use a Union Query which works when searching but i cannot edit or move the record ...
View 1 Replies
View Related
Mar 17, 2014
I have a question that I have a Microsoft Access database (.accdb) front-end/backend split and I want to give the database to my company client. As we have the different path for the backend/frontend linked. I want some code that will popup if the database location is not found and popup with the dialog so the user then select the backend and it would be ready and there is no need to popup each time the database open, it would run once it did not find the last linked path.
Also I have tried the code of Dev Ashish URL.... but unfortunately it would ask everytime to refresh the table links and I only want to run the process of linking tables when the database start and the linked path not found.
View 1 Replies
View Related
Aug 12, 2013
I'm trying to get a DB to move records simultaneously using a command button. The records are setup with check boxes for example: one column has a check box for 1 Year Ratio the next column has a check box for 2 Year Ratio and the third column has a check box for 3 Year Ratio.
How can i move the information out from 3 Year Ratio into another table and then move 2 Year Ratio check into 3 Year Ratio check box and 1 Year Ratio check into 2 Year Ratio check box by using a command button.
View 14 Replies
View Related
Nov 21, 2012
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
View 3 Replies
View Related
Jul 30, 2013
I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.
View 3 Replies
View Related
Dec 5, 2012
I have two tables "Master List" and "Audit". I want to delete the records from the "Master" that match those in the "Audit". I am using Access 2010 and have used it often but have built very few queries.
View 3 Replies
View Related
Sep 5, 2013
I'm trying to create a table for every record I have in a field. I have two tables, one titled "Experiments" and another titled "Students". I want the new tables generated from the field EXP_NAME from Experiments. I need it to have seven fields, one copied from Students, five labeled Trial1 thru Trial5, and a final for a grade.
View 10 Replies
View Related
Apr 25, 2014
My db includes two tables, Employees and Users, both with a pk of EmpNum (Autonumber in Employees; Number in Users). Users is a subset of Employees, and in the Users table, the row source for EmpNum is a query of the Employees table. I had a relationship defined with a one-to-one between the two tables, but did not have Enforce Referential Integrity or Cascade selected.
The problem is, I deleted a record out of Users, but it also automatically deleted the related Employees record. Re-creating the employee was quite the task because all related records in several other tables also disappeared. I experimented by deleting the relationship between the two tables and then deleting another user, but it still deleted the related employee. How can this be? The two tables are no longer linked, other than the Users EmpNum lookup.
View 6 Replies
View Related
May 7, 2014
I have been tasked with creating a simple database for work.
Basically the table tracks the progress of various employment checks. So example headings are Name, CRB, Occupational Health etc. Now basically, once all the checks are completed, I need the record to be moved to another table named 'Live'.
Is there any easy way to achieve this, or will i have to manually move each record once the checks have been completed.
View 3 Replies
View Related
Apr 8, 2015
There is one main policy table, and a collection of related tables that store information associated with each of the policies (coverage amounts, agents, etc). What I'm struggling with though is where they renew every so often (most times it's each year, but some go three or more years). Because I need the database to show everything EXACTLY as it was on each policy term, even expired ones, I don't want to just edit the existing records when policies renew; I need to create a copy of the ENTIRE record (across all related tables), but with a new PK, and the ability for users to change a few items, such as the policy number and effective dates.
There are two keys that I use to identify policies: one is the PK, which is an auto# that will need to change, and the other is a small text ID set when the policy is first issued, in order to make sure that even when policy numbers change from year to year, we can always track the history, so of course that one will need to remain the same. If I have the user click a "Renew" button on the policy view form that goes to a small form that takes what changes I want to allow and converts those into variables, could I then use those variables in a query, and have it copy all of these related records, with the other tables taking the new PK from the main table to keep the overall policy record linked?
View 4 Replies
View Related
Aug 21, 2007
I need to move my database from one computer to another and will need to put it on disk first. I have user serurity on the DB. Should it be ok as long as I put both files (the Db and the security) onto the disk and transfer them both?
Just asking as I cannot copy to CD from my computer and will have to put a request in so dont want to have to keep going back.
View 2 Replies
View Related
Jan 10, 2006
hiya all! i need some help!
i want to make a button on my "Calls" database form, to move all "Finished" calls (calls that have been declared as completed), into a "Finished Calls" table.
I have half of it working - i have a button that DELETES all 'Finished' calls. However i now need them to COPY into my "Finished Calls" table... is it an Append Query i need?
the code i have now is as follows...
SELECT * INTO Table2
FROM Table1
WHERE Finished='Yes';
thanks in advance!
johnny
View 5 Replies
View Related
Nov 16, 2006
Hello all
Got a question that I've never seen done in Access....
I have a DB I'm creating that creates pickup runs....(guy goes out and collects stuff) I'll need to get a subform showing the run items and be able to move them up or down according to the way in which the driver is going to do the run
Any ideas guys?
I've got no idea where to even start
View 4 Replies
View Related
Apr 19, 2005
Is there a way that you can take information from multiple forms to create one record in a table?
View 1 Replies
View Related
