Ok before I get started let it be known I am the definition of a newbie when it comes to access and VBA. I know very little about access and nothing about VBA. I am in the Army and am tasked with making a maintenance program for medical equipment. I have the database, the query, the forms, and the reports. I was able to figure those out, but there is one thing that is beyond me.
Ok I have three tables call them table "A" table "B" and table "C". Table A holds all my equipment information, table B is for workorders, and table C is for closed or completed workorders. The three are independent of one another. I have a form built to open new workorders, what I need is some way to place a command button in that form that will cut all the data from the currently viewed work order paste it in table C and input the current date in a yet to be determined column.
Now I know what I want to do very clearly but three 5 hour days of google searching and reading Access for dummies has only confused me more, and help will save my sanity.
5 1821
try this idea:
supposing you are viewing your workorders in a form called frmOpenWorkOrder
build two queries one append query and one delete query
Append query: qMove2ClosedWorkOrder
INSERT INTO tableC
SELECT *
FROM tableB
where tableB.id = [forms].[frmOpenWorkOrder].[id]
Delete query: qRemoveFromOpenWorkOrder
DELETE [id]
FROM tableB
where tableB.id = [forms].[frmOpenWorkOrder].[id]
add a command button in your form, right click on the button and go to its properties and select the on click event.
click on the triple dots(...) next to it and select code builder.
in the code builder write something like this:
docmd.openquery "qMove2ClosedWorkOrder"
docmd.openquery "qRemoveFromOpenWorkOrder"
to add a timestamp, add a column to tableC and update the field to Now() in the append query
hope this helps
Ok before I get started let it be known I am the definition of a newbie when it comes to access and VBA. I know very little about access and nothing about VBA. I am in the Army and am tasked with making a maintenance program for medical equipment. I have the database, the query, the forms, and the reports. I was able to figure those out, but there is one thing that is beyond me.
Ok I have three tables call them table "A" table "B" and table "C". Table A holds all my equipment information, table B is for workorders, and table C is for closed or completed workorders. The three are independent of one another. I have a form built to open new workorders, what I need is some way to place a command button in that form that will cut all the data from the currently viewed work order paste it in table C and input the current date in a yet to be determined column.
Now I know what I want to do very clearly but three 5 hour days of google searching and reading Access for dummies has only confused me more, and help will save my sanity.
When I run this query what are my parameters? No matter what I type it does nothing. Access takes the code but either I'm doing it wrong or it doesn't work.
in the feild area - Expr1: "Append query: qMove2ClosedWorkOrder INSERT INTO Closed WO's SELECT * FROM WO REG where WO REG.id"
In the criteria area this is what it says. - [forms].[New Workorder].[id]
then when I run the code it askes for a parameter, and I don't know what to put, I tired different things and it all came back empty.
If some one is willing I can e-mail what I have.
What does your form display? The idea behind this was that if your form displays a unique id for the workorder you can pick it out by doing [forms]![nameofyourform]![nameoftextboxdisplayingid].value
When I run this query what are my parameters? No matter what I type it does nothing. Access takes the code but either I'm doing it wrong or it doesn't work.
in the feild area - Expr1: "Append query: qMove2ClosedWorkOrder INSERT INTO Closed WO's SELECT * FROM WO REG where WO REG.id"
In the criteria area this is what it says. - [forms].[New Workorder].[id]
then when I run the code it askes for a parameter, and I don't know what to put, I tired different things and it all came back empty.
If some one is willing I can e-mail what I have.
I really am grateful for your help, but when I say I know very little I mean it. I have no idea how to write code or even where to write, I opened up the query in sql view and put in what you suggetsted and access beeped at me. So I tried using the help feature and this is what it gave me. - INSERT INTO [CLOSED WO'S] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE )
-
SELECT [WO REG].WO, [WO REG].MMCN, [WO REG].TECH, [WO REG].NOMIN, [WO REG].FUALTS, [WO REG].TYPE, [WO REG].SECTION, [WO REG].CLOSEDATE, [WO REG].OPENDATE
-
FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].MMCN=[WO REG].MMCN
-
WHERE ((([WO REG].WO)=[WHAT WO TO CLOSE?]));
-
The fields are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, AND OPENDATE. The table uses the primary index key for field WO. And that is the field I would like to use for the input to "close" the workorder.
but that askes me for a prompt and I put in the WO # which is how the table is indexed and then it warns me I am about to ammend (0) rows, and the query is blank.
If this issue is still not resovled I wrote up a quick sample database to show you what I mean, hopefully it should help. if you want it give me your email address and I'll send it to you
I really am grateful for your help, but when I say I know very little I mean it. I have no idea how to write code or even where to write, I opened up the query in sql view and put in what you suggetsted and access beeped at me. So I tried using the help feature and this is what it gave me. - INSERT INTO [CLOSED WO'S] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE )
-
SELECT [WO REG].WO, [WO REG].MMCN, [WO REG].TECH, [WO REG].NOMIN, [WO REG].FUALTS, [WO REG].TYPE, [WO REG].SECTION, [WO REG].CLOSEDATE, [WO REG].OPENDATE
-
FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].MMCN=[WO REG].MMCN
-
WHERE ((([WO REG].WO)=[WHAT WO TO CLOSE?]));
-
The fields are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, AND OPENDATE. The table uses the primary index key for field WO. And that is the field I would like to use for the input to "close" the workorder.
but that askes me for a prompt and I put in the WO # which is how the table is indexed and then it warns me I am about to ammend (0) rows, and the query is blank.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: DarkSpy |
last post by:
many c++ compilers including "gcc" have not implemented the "export"
keyword, but the comeau compilers made it (just i knew).
i want to know about: is it too difficult to implement "export"...
|
by: Robin Tucker |
last post by:
I have some code that dynamically creates a database (name is @FullName) and
then creates a table within that database. Is it possible to wrap these
things into a transaction such that if any one...
|
by: Peter Pagé |
last post by:
Hi,
I've got a window with a "<body onBlur="window.close()"> tag that keeps
closing prematurely. It happens when the user clicks on text inside a table
in the same window. Apparently IE...
|
by: Mike Maddux |
last post by:
Greetings!
In the VS.Net IDE, in the Server Explorer, you can right-click on a table and
choose "Export Data" to send the table data to a .dat file which can be used in
scripts to recreate the...
|
by: Ron L |
last post by:
I have a data table that lists a series of items in my database. In my user
form, I want the user to be able to filter by a number of criteria (e.g.
location, contract, date modified, etc). Other...
|
by: Tomás |
last post by:
I have a few really long template functions. They belong in a source
file... and thus must be "exported".
Seeing as how few compilers support "export", could someone please point
me to a...
|
by: pat |
last post by:
I know this is a long shot, but is there any way of dumping a bunch of
SQL "create table" statements from an existing Access 2003 database,
to say for example, convert the database to MySQL/Oracle....
|
by: AccessIdiot |
last post by:
Argh! Just when I think everything is working and I am doing one final test before showing it to the guys I built the db for, Access throws out a weird message and won't let me add a record. But only...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |