Automatically adding a new record

[scubadiver]

Brief summary:

I have a main form to hold employee info and a working hours subform. Each
employee has many records.

I would like to add a new record for each of the permanent employees while,
at the same time, add on 7 days to the date field.

Can this be done with a macro (and I am not an expert macro writer)? Or can
this be done with an update query?

cheers

 

[Joseph R. Pottschmidt]

Dear Scubadiver:

Since I'm not quite sure what you're asking, I think you mean that you
want to be able to have records displayed for a week and have each
employee enter there data into the MS Access database?

If that is the case, what I would suggest is to put in a Password for
each employee and a User number or employee number. Have then log in and
then put there respective information into the database and then close
the form.

In regards to having it display the dates for the work week, I need a
little more information has to how you want this information display and
entered into the system.

Joe P.


-----Original Message-----
From: scubadiver [mailto:[email protected]]
Posted At: Thursday, June 22, 2006 6:42 AM
Posted To: microsoft.public.access.macros
Conversation: Automatically adding a new record
Subject: Automatically adding a new record

Brief summary:

I have a main form to hold employee info and a working hours subform.
Each
employee has many records.

I would like to add a new record for each of the permanent employees
while,
at the same time, add on 7 days to the date field.

Can this be done with a macro (and I am not an expert macro writer)? Or
can
this be done with an update query?

cheers

 

[Steve Schapel]

Scubadiver,

You can do this with an Append Query. In the query, yu will probably be
able to use an expression like this...
DMax("[YourDateField]","WorkingHoursTable")+7
.... to give you the value to put into the date field. So the SQL view
of the Append, depending on what else you want there, may be as simple
as something like this...
INSERT INTO WorkingHoursTable ( EmployeeID, YourDateField )
SELECT EmployeeID, DMax("[YourDateField]","WorkingHoursTable")+7
FROM Employees
WHERE Status = "Permanent"

If you want to do this process every week or whatever, and you want to
automate it using a macro, you can use an OpenQuery action in the macro
to run the append. Once you have it working sweet, you may want to put
a SetWarnings/No action in the macro before the OpenQuery, to suppress
the display of the action query confirmation prompts.

 

[scubadiver]

That is a very good start for me! I will give it a go!!!!

Scubadiver,

You can do this with an Append Query. In the query, yu will probably be
able to use an expression like this...
DMax("[YourDateField]","WorkingHoursTable")+7
.... to give you the value to put into the date field. So the SQL view
of the Append, depending on what else you want there, may be as simple
as something like this...
INSERT INTO WorkingHoursTable ( EmployeeID, YourDateField )
SELECT EmployeeID, DMax("[YourDateField]","WorkingHoursTable")+7
FROM Employees
WHERE Status = "Permanent"

If you want to do this process every week or whatever, and you want to
automate it using a macro, you can use an OpenQuery action in the macro
to run the append. Once you have it working sweet, you may want to put
a SetWarnings/No action in the macro before the OpenQuery, to suppress
the display of the action query confirmation prompts.

--
Steve Schapel, Microsoft Access MVP

Brief summary:

I have a main form to hold employee info and a working hours subform. Each
employee has many records.

I would like to add a new record for each of the permanent employees while,
at the same time, add on 7 days to the date field.

Can this be done with a macro (and I am not an expert macro writer)? Or can
this be done with an update query?

cheers

 

[scubadiver]

Thanks for the message

I created a very simple copy of my database to test this. I have three
employees of whom one is a temp and two are permanent. I gave each employee
one dummy record with the same date (02/06/2006).

This is what I have in the SQL

INSERT INTO Table2 ( EmployeeID, [Date], standardhrs, timehalf, doubletime )
SELECT [Table2].[EmployeeID], DMax("[Date]","Table2")+7,
[Table2].[standardhrs], [Table2].[timehalf], [Table2].[doubletime]
FROM Table1 INNER JOIN Table2 ON [Table1].[EmployeeID]=[Table2].[EmployeeID]
WHERE status="Perm";

When I run the append query, it adds records only to the permanent employees.

When I run the query every time it doubles the number of records. So when I
run it the first time the date is 09/06/2006, the second time it adds two
records for 16/06/2006 and four records for 23/06/2006.

Can you explain why this might be happening?

Cheers

Scubadiver,

You can do this with an Append Query. In the query, yu will probably be
able to use an expression like this...
DMax("[YourDateField]","WorkingHoursTable")+7
.... to give you the value to put into the date field. So the SQL view
of the Append, depending on what else you want there, may be as simple
as something like this...
INSERT INTO WorkingHoursTable ( EmployeeID, YourDateField )
SELECT EmployeeID, DMax("[YourDateField]","WorkingHoursTable")+7
FROM Employees
WHERE Status = "Permanent"

If you want to do this process every week or whatever, and you want to
automate it using a macro, you can use an OpenQuery action in the macro
to run the append. Once you have it working sweet, you may want to put
a SetWarnings/No action in the macro before the OpenQuery, to suppress
the display of the action query confirmation prompts.

--
Steve Schapel, Microsoft Access MVP

Brief summary:

I have a main form to hold employee info and a working hours subform. Each
employee has many records.

I would like to add a new record for each of the permanent employees while,
at the same time, add on 7 days to the date field.

Can this be done with a macro (and I am not an expert macro writer)? Or can
this be done with an update query?

cheers

 

[Steve Schapel]

Scuba,

My apologies, in my haste....
Try it with this:
SELECT DISTINCT [Table2].[Empl...