Trying to Create Query to Append Records

[ekeenan81]

I have basically a vacation tracking form.

I have on the main vacation tracking for a button you can click on to search by name, so the user can then get a table listing that they can amend.

My issue now is when they make an update to the values that are returned from the query (SQL) these attributes are not getting updated in the underlying table.

My baseline code for the SQL query is below which gets me the attributes I need based off name, but how can I then get these updated in my table without also creating duped records. (For example user changes date or time-off type (Sick vs Vacation)

SELECT TIMEOFF.NAME, TIMEOFF.Date, TIMEOFF.TYPE, TIMEOFF.COMMENTS
FROM TIMEOFF
WHERE (((TIMEOFF.NAME) Like [Enter the User to Search For]));

I think I need to use UPDATE statement, but I keep getting syntax errors.

Any help is appreciated.

Thanks

 

[jackd]

Yes. You will need an UPDATE query.

Are you working with a Test database? I'd be cautious using Update or Delete queries
against an operational database. These will change existing data.

Be careful when updating records.
<b>If you forget the WHERE clause
ALL RECORDS will be MODIFIED </b>

Syntax for UPDATE SQL

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Example with data

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'

 

[ekeenan81]

Thanks Jack.

Where it gets confusing for me is say for example I have Jack with a vacation booked for 4/1/2011. He decides today he wants to change the date or remove it.

Having two possible outcomes here I know complicates things, so let's just say he wants to change the date to 3/24/2011 from 4/1/2011.

Unless I just have a sub-form and have the user update the initial record directly, I don't see a clean way of doing this as well as accounting for the different cases.

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Example with data

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'

 

[jackd]

You would use a form. Never let users deal directly with tables or queries.

You could show the user his proposed holidays. Then give an option to change,
then a process to enter or select a calendar and select a date
then a button to Confirm then change.

When the button is clicked, your Update query would run.

The button's On Click Event