SQL problems

[greysquirl]

I am using the following basically as a backup. It is triggered by a macro that is set to run on the "Before Update" of the "Position" text box. When a person changes positions the information about thier old job, date they
started etc. is dumped into another table for reference purposes.

The problem I am having is that when I go into the form and change one persons job position it dumps the entire contents of the table. . .every person; even though I am only changing one person. What should happen here
is when I change a persons position the information about that one person should be stored and only that one person.

INSERT INTO [Position Change] ( Position, DateChgd, Name, Department )
SELECT Review.Position, Review.[Position Start], Review.Name,
Review.[Department]
FROM Review;

Here is an example of what it is doing. . . .
On the first section there were just 4 names. When I went to the form to add the 5th name it assumed that I was changing the position box so it dumped the whole table. And in the 3rd section you will notice I changed
the last person's "Position" from "Clerk" to "Teacher" and it produced the same reaction. Again, what it should have done was only dump the information for the person changing positions, not the entire table.

Name Department Position DateChgd
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
John Maintenance Bus Driver 1/15/01
Dave MIS Computer Tech 3/15/00
Bobby MIS Supervisor 6/15/00
Henry MIS Support 3/15/01
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
John Maintenance Bus Driver 1/15/01
Dave MIS Computer Tech 3/15/00
Bobby MIS Supervisor 6/15/00
Henry MIS Support 3/15/01
John Admin Clerk 6/15/00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
John Maintenance Bus Driver 1/15/01
Dave MIS Computer Tech 3/15/00
Bobby MIS Supervisor 6/15/00
Henry MIS Support 3/15/01
John Admin Teacher 6/15/00

 

[kathryn]

You need to add a WHERE clause to your SQL. Do you an ID field for each person? Is it part of the form's data source?

If so, your SQL should look something like:

INSERT INTO [Position Change] ( Position, DateChgd, Name, Department )
SELECT Review.Position, Review.[Position Start], Review.Name,
Review.[Department]
FROM Review
WHERE YourTableName.YourIDFieldName = Me!YourIDFieldName;

Kathryn

 

[greysquirl]

After adding an ID field as you suggested and changing my SQL per your directions; I am at a loss. . . .

It still doesn't work??? Thank you for the help though, I am one step closer to figuring it out.

Are there any other ideas?

INSERT INTO [Position Change] ( Position, DateChgd, Name, Department )
SELECT Review.Position, Review.[Position Start], Review.Name, Review.[Department]
FROM Review
WHERE Review.EmpID = Me!EmpID;

 

[kathryn]

When you say that it still doesn't work, what do you mean? What happens? Kathryn

 

[greysquirl]

I literally mean nothing happens. When I added that WHERE clause in, the EmpID in to the table, and then ran the query nothing happend. It doesn't give an error but at the same time it doesn't post an info either. Even when I go to the form and add a new employee or change the current position status of the employee nothing is added to the table.

 

[kathryn]

OK do you know how to work with breakpoints? That is my next suggestion. Go the procedure that has the SQL and click your mouse on the line that holds the SQL. Press F9. This will set up a breakpoint. When the code runs, it will stop at that line and open up the code window with that line highlighted. Then you can hold your mouse over variables and see what they equal or go into the Command Window (choose View->Command Window, or Ctrl-G) and type

?me!empID

in the bottom half of the screen to see what is happening to this variable.

Kathryn

 

[greysquirl]

This bit of code isn't running in code. I have it running in a query. It all starts when the "Position" in the form is changed, on the Before Update event. When it is changed there is a macro called Backup that is run, which runs the query.

 

[greysquirl]

I finally got this to work right. The only problem now is that when the query runs its asking which "EmpID" to move over. What I originally wanted; if possible; was to simply move the record that had been changed without it asking any questions.

INSERT INTO [Position Change] ( Position, DateStarted, Name, Department, DateChanged )
SELECT Review.Position, Review.[Position Start], Review.Name, Review.Department, Review.DateChanged
FROM Review
WHERE Review.EmpID = Me!TextEmpID
ORDER BY Review.Department;

 

[kathryn]

OK if this is running in a query, then you can't use the Me! construction. That will only work if the actual SQL is in code that is behind a form (the form is what the Me! is referring to)

You say that you have a macro running. Do you really mean a macro, or do you mean a procedure? Kathryn

 

[greysquirl]

The macro is running the query when the position changes. When you say I can run this in code. . . .explain to me how you would actually go about this?

 

[kathryn]

OK, I am assuming that you mean a real macro, that is accessed from the Macro tab. You can always convert a macro to code. Access has a built in wizard to do this; I forget where it is. You may want to check the help files.

One thing you may want to try, which won't require you to convert your macro to code is to change the query as follows. Instead of using the Me! construction, use the Forms!YourFormName!EmpID construction. Since the form is open when the query is run, the query should be able to get the value from the form.

Let me know how this goes.

Kathryn

 

[greysquirl]

Thank you Kathryn, you have been a tremendous help. It worked beautifully after I changed it to this. . .

I am adding this for anyone who has been following this post. . . .what was changed here is working great and without a hitch.

INSERT INTO [Position Change] ( Position, DateStarted, Name, Department, DateChanged )
SELECT Review.Position, Review.[Position Start], Review.Name, Review.Department, Review.DateChanged
FROM Review
WHERE Review.EmpID = Forms!Review!EmpID
ORDER BY Review.Department;

 

[kathryn]

Glad to be of help. Have a good week. Kathryn