Need to close Data in Table "A" and export it to table "B"

[kiask2343]

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.

[akhenaton42]

try this idea:

supposing you are viewing your workorders in a form called frmOpenWorkOrde r

build two queries one append query and one delete query

Append query: qMove2ClosedWor kOrder
INSERT INTO tableC
SELECT *
FROM tableB
where tableB.id = [forms].[frmOpenWorkOrde r].[id]

Delete query: qRemoveFromOpen WorkOrder
DELETE [id]
FROM tableB
where tableB.id = [forms].[frmOpenWorkOrde r].[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 "qMove2ClosedWo rkOrder"
docmd.openquery "qRemoveFromOpe nWorkOrder"

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.

[kiask2343]

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.

[akhenaton42]

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]![nameoftextboxdi splayingid].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.

[kiask2343]

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.

[akhenaton42]

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.