Expression builder in Update Queries...

[Jewellissa]

Hi-

I am an intern with Verizon and I have a pretty big project in Access 2000 that I dont have much techinical help with. I have run into a major problem though, so in surfing the great garbage heap of an Internet I found this site.

I am trying to build a program that helps schedule employees due to union rules. I have run into a major problem with some of my update queries.

The user first is presented with a form and is requested to enter Emp ID, name, etc. then they press a button (which calls the macro "Continue") to return to the main form. "Continue" first tries to run an update query which (tries to) update the values of another table to the enetered Emp ID. So the form is still technically open so I can get to the information in it, but nothing happens. It doesn't update.

What am I doing wrong?

Any help would be gratefully appreciated! Thank you all so much!

Jewellissa

 

[FoFa]

Do you get an error, or just no update?

I also assum you are not closing the form.
The query is pulling the data correctly and is formatted correctly.
Post the SQl from the query here.

 

[Jewellissa]

Here...

UPDATE [DMS Employee Info] INNER JOIN [Sample Enter New Employee] ON [DMS Employee Info].EC = [Sample Enter New Employee].[Employee ID] SET [Sample Enter New Employee].[Employee ID] = [Forms]![Add Employee]![EC].[AfterUpdate], [Sample Enter New Employee].[Specific Entry Num] = (([Forms]![Add Employee]![EC])*100)+([Sample Enter New Employee]![Num of Week])
WHERE ((([Sample Enter New Employee].[Employee ID])>"0"));


I have just started Access on this level 6 weeks ago.. so the SQL is pretty out of my depth... But here it is... =)

Thanks...
Jewelz

 

[Jewellissa]

Oh yeah...

YEah.. There is no error nothing.. I have the Emp ID default set as 100 and it just doesn't change from 100. I also do not have a primary key set but there is a distinct record number.

 

[FoFa]

UPDATE [DMS Employee Info]
INNER JOIN [Sample Enter New Employee]
ON [DMS Employee Info].EC = [Sample Enter New Employee].[Employee ID]
SET [Sample Enter New Employee].[Employee ID] = [Forms]![Add Employee]![EC].[AfterUpdate],
[Sample Enter New Employee].[Specific Entry Num] = (([Forms]![Add Employee]![EC])*100)+([Sample Enter New Employee]![Num of Week])
WHERE ((([Sample Enter New Employee].[Employee ID])>"0"));

Why is there a property value there (AfterUpdate)? Just reference the default value of the control.
Your WHERE clause will update every record in [Sample Enter New Employee] table with an Employee ID greatter than zero, and of course if there are none greater than zero, it will make no updates.

 

[Jewellissa]

Well I tried that...

I have tried it without the AfterUpdate option. It still didn't work. That was an attempt to make it work...

=/

Thank you for that help though!
Jewelz

 

[FoFa]

One other thing I noticed, you are updating ine table but setting the value of the other tables column to something. I think it needs to be this way:

UPDATE [Sample Enter New Employee] SET [Sample Enter New Employee].[Employee ID] = [Forms]![Add Employee]![EC], [Sample Enter New Employee].[Specific Entry Num] = (([Forms]![Add Employee]![EC])*100)+([Sample Enter New Employee]![Num of Week])
WHERE ((([Sample Enter New Employee].[Employee ID])>"0"));

I do not know why you have [DMS Employee Info] included since you are not using it in the update anywhere.

 

[Jewellissa]

I was so aggravated at this yesterday because it was 4pm and I wanted out of this Cubicle. So today I sat down.. felt my temperature rising.. sat down posted.. then actually thought about it... turns out..

You know that >"0" condition? If I converted this update to a Search Query it didnt return anything... Funny thought I...should it return the whole Database? Since the default Emp ID is 100?

Well no.. because Emp ID is text not number because some Emp ID's are letters. So you cant really have "greater thans" in text.. except in C++ ish programming languages... but that is pretty ghey anyway (I mean that would mean Z is > all other letters and come on.. Z is totally uncool!!!) so I had to do some pretty awful gerrymandering... and finally rigged this totallly uncool way to take care of this fact since Emp ID has to be text...

In short... I am so stupid!!!!!.. Thank you sooooo much FoFa for all your help. I felt totally inadequate and you help made me stop and think about it.. thanks!!!!

Jewelz