Use Form button to move current record to another table

I have a database which consists of computer names and model numbers.
As time goes on the computers get old and are no longer in use. I also
have an identical table that I have been copying the old computers to.
Once copied to the old computer database I delete the computer record
from the current computer database. Is it possible to accomplish this
task with one button?
0
JMageeirbc
11/15/2007 5:19:14 PM
access 16762 articles. 3 followers. Follow

4 Replies
2346 Views

Similar Articles

[PageSpeed] 21

   Say you had a button on each record called cmdArchive, and it's purpose 
is to move this record to an archive table, and delete that same entry from 
your current table.
   Each record should have a unique identifying key value. (ex. EquipmentID)
   Use that value to run an Append query from the active table to the 
archive table for just the record with that EquipmentID.
   Then, run a Delete query against your active database, to remove the 
record with that EquipmentID.

Private Sub cmdArchive_Click()
    DoCmd.RunSQL 'your Append query SQL here
    DoCmd.RunSQL 'your Delete query SQL here
End Sub

When every thing works correctly, you can add...

Private Sub cmdArchive_Click()
    DoCmd.SetWarnings False
    DoCmd.RunSQL 'your Append query SQL here
    DoCmd.RunSQL 'your Delete query SQL here
    DoCmd.SetWarnings True
End Sub

to defeat the action query dialog boxes when the queries run..
-- 
    hth
    Al Campagna
    Microsoft Access MVP
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

<JMageeirbc@gmail.com> wrote in message 
news:b5015280-ac82-4d41-b82e-473a99ee6a7e@b32g2000hsa.googlegroups.com...
>I have a database which consists of computer names and model numbers.
> As time goes on the computers get old and are no longer in use. I also
> have an identical table that I have been copying the old computers to.
> Once copied to the old computer database I delete the computer record
> from the current computer database. Is it possible to accomplish this
> task with one button? 


0
Al
11/15/2007 6:10:22 PM
You should only have one table. In it put a Yes/No field named something like 
"In_Use". When no longer in use, set the field to No. In all your queries 
where you want to see only in use computers put Yes in the criteria for the 
In_Use field.

That way you don't need to move records between tables or do things like 
Union queries when you need to see both in use and not in use computers.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"JMageeirbc@gmail.com" wrote:

> I have a database which consists of computer names and model numbers.
> As time goes on the computers get old and are no longer in use. I also
> have an identical table that I have been copying the old computers to.
> Once copied to the old computer database I delete the computer record
> from the current computer database. Is it possible to accomplish this
> task with one button?
> 
0
Utf
11/15/2007 6:48:02 PM
Hi Al,

I am trying to do this exact same thing.  My problem is that my form is 
based directly from the table and not a query.  How to I attach the query to 
my form?  Thanks.  Happy Holidays!

Ruth,


"Al Campagna" wrote:

>    Say you had a button on each record called cmdArchive, and it's purpose 
> is to move this record to an archive table, and delete that same entry from 
> your current table.
>    Each record should have a unique identifying key value. (ex. EquipmentID)
>    Use that value to run an Append query from the active table to the 
> archive table for just the record with that EquipmentID.
>    Then, run a Delete query against your active database, to remove the 
> record with that EquipmentID.
> 
> Private Sub cmdArchive_Click()
>     DoCmd.RunSQL 'your Append query SQL here
>     DoCmd.RunSQL 'your Delete query SQL here
> End Sub
> 
> When every thing works correctly, you can add...
> 
> Private Sub cmdArchive_Click()
>     DoCmd.SetWarnings False
>     DoCmd.RunSQL 'your Append query SQL here
>     DoCmd.RunSQL 'your Delete query SQL here
>     DoCmd.SetWarnings True
> End Sub
> 
> to defeat the action query dialog boxes when the queries run..
> -- 
>     hth
>     Al Campagna
>     Microsoft Access MVP
>     http://home.comcast.net/~cccsolutions/index.html
> 
>     "Find a job that you love... and you'll never work a day in your life."
> 
> <JMageeirbc@gmail.com> wrote in message 
> news:b5015280-ac82-4d41-b82e-473a99ee6a7e@b32g2000hsa.googlegroups.com...
> >I have a database which consists of computer names and model numbers.
> > As time goes on the computers get old and are no longer in use. I also
> > have an identical table that I have been copying the old computers to.
> > Once copied to the old computer database I delete the computer record
> > from the current computer database. Is it possible to accomplish this
> > task with one button? 
> 
> 
> 
0
Utf
12/13/2007 7:58:00 PM
You should only have one table. In it put a Yes/No field named something like 
"In_Use". When no longer in use, set the field to No. In all your queries 
where you want to see only in use computers put Yes in the criteria for the 
In_Use field.

That way you don't need to move records between tables or do things like 
Union queries when you need to see both in use and not in use computers.

As far as attaching a query to a form, first create the query will all the 
fields needed by the form and give it a unique name. Next open the form in 
design view and go to its properties. Go to the Data tab and type in the name 
of the query or go to the drop down where the query should be listed.

"Ellie" wrote:

> Hi Al,
> 
> I am trying to do this exact same thing.  My problem is that my form is 
> based directly from the table and not a query.  How to I attach the query to 
> my form?  Thanks.  Happy Holidays!
> 
> Ruth,
> 
> 
> "Al Campagna" wrote:
> 
> >    Say you had a button on each record called cmdArchive, and it's purpose 
> > is to move this record to an archive table, and delete that same entry from 
> > your current table.
> >    Each record should have a unique identifying key value. (ex. EquipmentID)
> >    Use that value to run an Append query from the active table to the 
> > archive table for just the record with that EquipmentID.
> >    Then, run a Delete query against your active database, to remove the 
> > record with that EquipmentID.
> > 
> > Private Sub cmdArchive_Click()
> >     DoCmd.RunSQL 'your Append query SQL here
> >     DoCmd.RunSQL 'your Delete query SQL here
> > End Sub
> > 
> > When every thing works correctly, you can add...
> > 
> > Private Sub cmdArchive_Click()
> >     DoCmd.SetWarnings False
> >     DoCmd.RunSQL 'your Append query SQL here
> >     DoCmd.RunSQL 'your Delete query SQL here
> >     DoCmd.SetWarnings True
> > End Sub
> > 
> > to defeat the action query dialog boxes when the queries run..
> > -- 
> >     hth
> >     Al Campagna
> >     Microsoft Access MVP
> >     http://home.comcast.net/~cccsolutions/index.html
> > 
> >     "Find a job that you love... and you'll never work a day in your life."
> > 
> > <JMageeirbc@gmail.com> wrote in message 
> > news:b5015280-ac82-4d41-b82e-473a99ee6a7e@b32g2000hsa.googlegroups.com...
> > >I have a database which consists of computer names and model numbers.
> > > As time goes on the computers get old and are no longer in use. I also
> > > have an identical table that I have been copying the old computers to.
> > > Once copied to the old computer database I delete the computer record
> > > from the current computer database. Is it possible to accomplish this
> > > task with one button? 
> > 
> > 
> > 
0
Utf
12/14/2007 4:15:01 PM
Reply:

Similar Artilces:

No Form Assistant in Quote Product Screen
While entering data in the QuoteProduct screen, no Form Assistant is available. Is it the usual feature or I need to configure something to view the Form Assistant? Also is it possible to see contact hierarchy in an account in the form of a Organisational Chart? Is their any add-on available in relation to this? ...

Form for table
Here's the problem: Access 2003, XP Pro- I have three tables that are related by primary keys. I have a table[Table QC] that has the field [EmployeeID], the data type is number--that field is linked to the autonumber primary key of the Employees Table. So far so good. I am trying to make a form that has a combo box that looks to the Employee Table to get me the drop down of the employees (Last Name, First Name). The SQL statement builds out okay, and I get the correct name config., but because the data type is a number it won't accept the data. I looked at the Northwind example and i...

Cannot move emails between folders
I am the Administrator on my home machine. If I try to move an email from the Inbox to another folder I am told 'I don't have permission, check the folder properties for permission' There is nothing in the folder properties about permissions. This has only recently started happening. XP Home and Outlook XP with all updates. ...

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Moving Dynamics
Can someone direct me to updated infomation on How to move GP 10 to a new server box with SQL 2008 KB article 878449 still applies and has updated information for SQL Server 2008. -- Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "tstrop" wrote: > Can someone direct me to updated infomation on How to move GP 10 to a new > server box with SQL 2008 ...

Two mailboxes for one user account after mailbox moved to a new server
After moving a user's mailbox from a Exchange 2003 to a new Exchange 2003 server, Outlook has listed two mailboxes with same name in the "All Mail Folders"area. The Outlook profile only has the user mailbox and no other mailboxes added to it. The user can send and recieve email just fine. The mailboxes seem to be clones of each other. New mail show up in the mailboxes at the sametime. How can I get Outlook to only show one mailbox? Any help would be appreciated, thanks, ...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Creating a template using Publisher
I need help creating a neighborhood directory template using Publisher and merging an Excel spreadsheet into it. It keeps printing multiple pages of the first page after I merge. What version Publisher? If you have 2003 use the catalog merge for your directory. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx Otherwise you need to setup your page as though it is labels. Determine the size of the area you need for each entry, select labels in page setup, type the size you determined in the page setup. Adjust the gaps and margins when you are ready to print (2000 and bel...

Microsoft CRM using Firefox
Hello, Would anyone by chance know if CRM will work correctly on Firefox. Currently, I have employees that are using Firefox and are encountering the same issue as IE [Internet Explorer bombs out and states that it has encountered problems...]. I am just having employees use IE to prevent problem. Thank you for your comments in advance. Install a Firefox extension called IETab "Marcos" <Marcos@discussions.microsoft.com> wrote in message news:19B93E29-757D-46FF-B747-A17EB112ED94@microsoft.com... > Hello, > > Would anyone by chance know if CRM will work cor...

Sum in Form Footer
Hi Folks, I've made a the following calculation in form Detail section into text box and I've called my text box "Chaussures" =Sum([Chaussures])+([aldo])+([Feet-First])+([Transit])+([Globo]), this sum calculate the sum of Units which are into these text boxes "aldo", "Feet-First", "Transit" and "Globo" and this for each line record. Now in the Form Footwear, How can I make the total of units "Chaussures" I've tried a lot of things and nothing seems to work. Your help will be much appreciated, Thankign you in advance,...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

Unable to repy using word editor
When repyling to an e-mail I receive the following message, "This form requires Word as your e-mail editor, but Word is either busy, or cannot be found. The form will be opened in the Outlook editor instead". I found article 284900, that states you may get this message when you have office 2000 and office xp installed on the same computer. This is not the case on my computer, Office 2000 does not exist, only office xp. Can anyone help me? There appear to be a whole host of causes for this error message. Most have no solution. Here are a few that do: http://support.microsoft...

iPhone Email to Entourage Using Exchange
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When I answer an email on my iPhone, it does not register it in Entourage as answered. This function worked fine until the business I work for put everyone on Exchange. Is there a setting for this that I can change? <br><br>Thanks! ...

Is anybody using Symantec Enterprise Vault?
Hi, A vendor has recommended this solution to us, I have looked at the datasheet for the product and it speaks the right language but I was wondering if anybody on here uses it in a live environment and what their opinion of it is. Cheers Sean ...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

Operating a small passenger service
I am hoping someone has managed to customise outlook in a way that allows you to use the programe to manage bookings on a small passenger service. I.e input a new booking, number of people, location etc. Then email the days bookings to the different drivers. Maybe it could also be set up to know how many seats are avialale to reduce the risk of double booking? Im using Outlook 2003. ...

Appending in a sub form
I am creating a venue ticket tracking DB in Access '07. tblVenueEvent has info about Venue, date, time, event with a VenueEventID (key field) which is linked to tblTicketInfo which has section, seat, row, etc. I set up an entry form completing VenueEvent info with a sub form for ticket info. Some of the venues have a standard set of seats that I want to "auto populate" into the sub fom. I have set up a Venue table with the standard seats for the venue. I can set up an Append Query on the sub form, which does append the tblTicketInfo, but does not capture the VenueEven...

List Running Horizontally-Pivot Table Possible?
I just had a customer ask this one and I'm not so sure about it. 1) The customer has setup a list of information that is running horizontally instead of the typical list running vertically. 2) I just taught him how to do pivot tables and he wants to generate a pivot table with his "horizontal" list. 3) I know he can copy and paste special and use the Transpose feature to change the list from horizontally oriented to vertically oriented. 4) the question is: can he leave the table in its original orientation and still generate Pivot tables? I can't seem to make it work ...

Running an Excel Macro from an Access Command Button
Hi , Can anyone help me with this, I am trying to run an excel macro from an access command button. I want the macro to Bold the Headings on a excel spreadsheet. The code runs without any errors but the macro doesnt seem to work, I think I have got the line ExcelApp.ExecuteExcel4Macro "RUN(""PERSONAL.XLS!BoldHead"")" wrong The code: Private Sub cmdRunMac_Click() Dim ExcelApp As Excel.Application Dim ExcelBook As Excel.Workbook Set ExcelApp = Excel.Application ExcelApp.Visible = True Set ExcelBook = ExcelApp.Workbooks.Open("C:\Documents and Settings\...

Unicode "private use" glyphs in Powerpoint and Word 2008 #2
Office 12.0.0.1 on Leopard 10.5.2, all updates applied to both. A friend is migrating from Windows to Mac, and has come across something that we're trying to work around. He has some .ppt files with font glyphs in from a Unicode .ttf font (a specific purpose font for showing the LCD display characters on a piece of monitoring equipment). When we bring the font and .ppt over to the Mac, the glyphs from the normal ASCII range come over into Powerpoint 2008 fine, but the glyphs from the "private use" range from F000 upwards a way don't transfer. In Powerpoint they show as spac...

Move pages from document to document?
This is a multi-part message in MIME format. ------=_NextPart_000_0397_01C7D5D9.22350CB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am relatively new to this software. I can't seem to find how to move a = page from one document to another. Any help appreciated. Shmuel ------=_NextPart_000_0397_01C7D5D9.22350CB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <MET...

Is there a way to convert OE 03 form templates?
I have a user that has upgraded from outlook 2003 to outlook 2007 and has a form template from 2003 that is in a FDM extension. Outlook 2007 is unable to read this form. Is there a way to convert this form, or will the user just have to create a new form with their new version of outlook? Any information appreciated. Thanks, -AllenK What happens when you try to install the form? Any error message? -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.ms...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...