I am working with an Access 2003 inventory database. When a piece of equipment goes bad we junk it and delete it from the database. We have been copying the record to an excel form for storage just so we have a record of the former piece of equipment. I am trying to make the database more user friendly and keep all of the data in Access so have created another table called JunkedEquipment . I would like to be able to use a command button on a form to copy the record from the Equipment table to the JunkedEquipment table and then delete the record from the Equipment table and in the process give a confirmation message box to confirm the copy and delete.
I am not very familiar with VBA and have been fumbling over this the past 2 days. I have tried accomplishing this with a macro as well as a query but so far have fallen short of my goal.
Any Ideas or ways I can make this happen?
I appreciate any help given.
15 17958 Rabbit 12,516
Recognized Expert Moderator MVP
As I am not very knowledgable about setting up a query so I didn't try too much with that. I did try a macro and had it bring up a message box with a confirmation. It properly cancelled but would not run. I thought I could use the CopyObject and DeleteObject action to make this work but haven't had any luck with those as well. I am not sure if these actions will work with a record in a form or not.
Here is how I setup the Macro to date.
Action: Hourglass
Action: StopMacro w/ Condition: MsgBox("Are you sure?",289,"Jun k Equipment")<>1
Action: CopyObject (Not sure how to direct record to another table.)
Action: DeleteObject
I have a feeling that I am somehow way off base with this.
Rabbit 12,516
Recognized Expert Moderator MVP
Objects in this context refer to tables/forms/reports. What you're looking to do is to run an Append query and a Delete query. Get those two queries set up and you can call them from a macro or VBA.
Ok, I'll try that. Thanks for your help so far.
Rabbit 12,516
Recognized Expert Moderator MVP
Not a problem. Let us know how that goes.
I got the append query up and running but I can't figure out what criteria to set in the query so that it just selects the current selected record in the form. Right now if I use a command button on my form to run this query it wants to append the entire table. I just want to append the current record that is being viewed in the form.
Rabbit 12,516
Recognized Expert Moderator MVP
I got the append query up and running but I can't figure out what criteria to set in the query so that it just selects the current selected record in the form. Right now if I use a command button on my form to run this query it wants to append the entire table. I just want to append the current record that is being viewed in the form.
Have the query call the ID field on the form using the syntax Forms!FormName! ControlName.
I originally couldn't get this to work so I took a break from this problem and worked on a few other things. When I came back it was no problem and I got it up and working right away. I guess I just had to take a break from it. Thanks for all of your help Rabbit!
I do have one other question for you, kinda dealing with the same thing, I am appending data to a different table in my database from a form to track equipment history everytime a change is made to the inventory. I can get it to work but only with two command buttons. Once the new information is entered in the form the user needs to use a command button to save the information in the form and then use a separate command button to append the data.
How can I get one command button to do both?
I would like one command button to save the data and then append it. Like I've said before I am not very good with VB so haven't been able to figure anything out with that and I can't find a way to do this with a macro. I know there is a save function when building a macro but that is for objects and not for data.
Rabbit 12,516
Recognized Expert Moderator MVP
Well, I don't use macros but can't I'm guessing you have two macros set up? One for each button? Can't you just combine the actions of both macros?
If not, you can convert macros to VBA and then you just need to copy the contents of one into the other.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Hasanain F. Esmail |
last post by:
Hi,
Your help will be greatly appriciated.
I have a table with following fields. Table's name is tblTicketNumers
TicketNumberID (It is a key field)
TicketNumber (Text field)
AgentsName (Text field)
I have a form which is NOT BOUND to this table with following controls
|
by: Sean Howard |
last post by:
Dear All,
As is my want I need to do something in Access that seems simple but
cannot fathom out.
I have main form with two subforms, both datasheets with an almost
identical table structure.
I want to add the functionality to copy records from subformA to
subformB WITHOUT USING COPY/PASTE. The reasonfor this is that I must
|
by: davidgordon |
last post by:
Hi,
I need some pointers/help on how to do the following if it possible:
In my access db, I have the following:
Tables: Products, Sub-Assembly, Product-Pack
Table, Products
|
by: lgbjr |
last post by:
Hello All,
I¡¯m using a context menu associated with some pictureboxes to provide
copy/paste functionality. Copying the image to the clipboard was easy. But
pasting an image from the clipboard is proving to be more difficult. These
pictureboxes are bound to an AccessDB. If the user wants to add an image,
they select an image using an OpenFileDialog:
Dim result As DialogResult = Pic_Sel.ShowDialog()
If (result = DialogResult.OK) Then
|
by: Kevin Murphy |
last post by:
This is a tip for the record in case it helps somebody else in the
future.
I have an import script that relies on a stored procedure that runs as
a trigger on inserts into a temporary table. The script looks like
this:
-- create table
-- ...
-- define procedure and trigger
| |
by: Rachel McConnell |
last post by:
Hi,
I am trying to import data using COPY, from a file containing thirty or
so COPY commands each with 0 or more rows of data. Reason, I have a
small data set I want to include into a database with an identical
schema, with existing data. I figured a good way to do this would be to
use pg_dump on the small dataset, trim out the table definitions and
constraints, and run the resulting file. (I also reordered the COPY
statements to...
|
by: alanchinese |
last post by:
i am a db2 newbie. we have a server hosting a db2/6000 database that
restricts the use of backup, generate ddl. i wonder if there is a
simple way to transfer the database structure and data into my laptop,
on which db2/nt was installed.
i had tried to use DTS service from microsoft sql server. it didn't
work.
any suggestion?
any help would be appreciated.
alan.
|
by: bmallett |
last post by:
First off, i would like to thank everyone for any and all help with this. That being said, I am having a problem retrieving/posting my dynamic form data. I have a form that has multiple options within options. I have everything being dynamically named from the previously dynamically named element. (I hope this makes sense.) I am not able to retrieve any of the dynamically created values. I can view them on the source page but can't pull them...
|
by: Kevin |
last post by:
I need a little guidance here. I have set up a table for job estimates with
a large number of data fields. On the estimate form is a command button to
convert the estimate to a scheduled job. When the user clicks the button I
would like to copy certain fields and paste them to corresponding fields in
a projects table. Could someone point me in the right direction on where to
start this process?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |