Update Query

[stryder09]

Is it possible to run an update query from a form? I have a form that brings up a drop down list of peoples names that are sorted by date of last work. When the person has worked the new date is entered. Is there a way that I can then push a button and a query would run that would update the table with the new date and only the employee that worked. So only one line on the table?



What would the query look like?

In the table there are multiple fields but I only need to update [Last Worked] with the coresponding [Employee Name]

Any help would be very much appreciated.

Brad

[pbaldy]

Sure; start creating an update query, and in the spots where you want to refer to the form, right-click and select Build to have the expression builder help you with the syntax.

[stryder09]

But how do I set the criteria to select the individualy name that was chosen in the drop down list?

My Update Query at the moment

Field: LastCalled
Table:tblEmployee
Update To: Now()
Criteria:
or:

I want the criteria to tell it only to update [tblEmployee].[LastCalled] by using [frmOvertimeCallIn].[EmployeeNames]

How do I create that criteria?

[pbaldy]

Like I said, right-click in the criteria area, select Build, and point to the combo.

[stryder09]

So this is what I have and I get an error which says Data type mismatch in criteria expression.

Field: LastCalled
Table:tblEmployee
Update To: Now()
Criteria:[Forms]![frmOvertimeCallIn]![EmployeeNames]
or:

[pbaldy]

It would help to see the SQL, but it appears you may have the criteria on the date field instead of the employee field?

[stryder09]

ok I'm lost, I am trying to do this as a query? Should I be doing this a different way? Could you give me an example on how it should be written? Everything I showed you is how it is on the query.

[pbaldy]

What I'm describing is a query. In design view, you'd see 2 fields, the date field you want to update and the employee field you want the update restricted to. The date field would have an entry in the Update To area, the employee would have that form reference in the criteria.

[orange]

check out the functions and syntax

http://www.techonthenet.com/sql/update.php

queries generally at http://www.techonthenet.com/access/queries/
Good luck.

[stryder09]

Thank you I might have got it.. Thanks for your patience.

[stryder09]

So I am getting the following errors now. I think its because the names I am using is coming from the same table that I am trying to update with a date with my query. Any help would be appreciated.

[pbaldy]

That can be problematic. You might try explicitly saving the current record before running the query. I don't use macros, but I think it's under RunCommand.

[stryder09]

ok, is there a piece of code I could write that would run a query when the form closes that could look at the last record [Specific Form Feild] and then update a table. My issue is the table is open that I'm trying to write too. so if I close the record,mu query won't work because it looks at the record open and runs the query for the name chosen and a date entered. if the record is closed it can't run that query, to update the table?

Make sense??

[pbaldy]

Did you try saving the record before running the query? It could simply be that the form is dirty, which will lock the record.