Planning system - Copy planning from previous week

[marcellvries]

Hi there,

Currently I'm designing a planning system in MS Access for a security company. It is going well but I got stuck at one issue.

Background
I have a table tblPlanning with the following fields:
PlanningID, LocationID, EmployeeID, Startdate, Enddate, Starttime, Endtime. I have a continuous form that shows tblPlanning for one specific LocationID (filled in already as chosen in a previous dialog form) and enables you to add new records.

Problem
The thing is that for a specific club, on average, 12 records per week are needed. The start- and endtimes, and the security officers (employeeID) are kind of fixed most of the time. Currently, it takes a lot of time to add the planning for one club for one single week. It takes probably longer than writing it down.

What I would like to do is to add a button on the form that copies last week's planning.

Private Sub cmdCopy_Click()
Dim LastWeek As Integer
LastWeek = DatePart("ww", Now(), vbMonday) - 1
End Sub

I don't know how to achieve this but here are my thoughts.

My thoughts
(1)Maybe a recordset (rstcopy) should be created:
Select * from tblPlanning where LocationID = txtLocationID and Week = Lastweek.

(2)Then all records in rstcopy should be modified like "startdate+7".

(3)Subsequently all the (adjusted) records should be added to tblPlanning with an append statement (don't know how to handle the autonumber primary key PlanningID by the way).

(4)Finally the form will be refreshed (Me.Requery).


Question
Does anyone know how could I achieve this?

Thank you in advance. I hope I have been clear in what I have and what I would like to do. If not, I'm happy to provide additional information.

With kind regards,

Marcell

 

[JBinQLD]

Too easy - you're just about there mate. To help you help yoursef with this one, break it down into these steps, some of the guys on here will help you with mega efficiency butI prefer to help you learn.

a) Create a query that selects the records your're interested in (criteria on Location and last week of course)

b) Add a calculted field to this query that uses DateAdd function to increment the date by 7 days

c) when its showing correct data turn it into an append query

d) MSAccess will take care of the autonumber for you, simply disregard that from the query.

e) If you dont want your user to see the default messages such as "Your's about to append X records to ...." then look into "docmd.setwwarnings false"

f) Consider appendin the records into a temporary table - allow the user to view and make changes if required, then press another button to atually append them to the final destinatino table - only you will know if this is likey to be useful to your client.

hope this helps mate.

 

[JBinQLD]

PS. Writing a funky function to work out last weeks dates can be useful but another option is to use criteria something like "BETWEEN Datedd("w",-7,now()) AND Now()"

Also, I'm honestly unsure whether saving the queries and executing from VBA by docmd.openquery is any faster or slower than docmd.runSQL (strSQL).

I'm sure one of the gurus can answer that but eitheer way the docmd.setwarnings false mentioned above will make it less scary for the user. Dont forget a docmd.setwarnings true when you've finished executing the queries.

Laterz.

 

[marcellvries]

Hi JBinQLD,

Thank you for your quick reply! I prefer the learning-way also because with copy&pasting I'll never learn to master Access.

I'm going to try to put the things you wrote into practise now and let you know when I succeed or when I run into any trouble.

With kind regards,

Marcell

 

[marcellvries]

Hi JBinQLD,

It works! For further reference and to help other people I will include the codes below. Again thank you for your help JBinQLD.

With kind regards,

Marcell

The commandbutton on the form:

Private Sub cmdCopy_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryCopyLastWeek", acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Requery
End Sub

The append-query:

INSERT INTO tblPlanning ( EmployeeID, LocationID, Starttime, Endtime, Startdate, Enddate )
SELECT tblPlanning.EmployeeID, tblPlanning.LocationID, tblPlanning.Starttime, tblPlanning.Endtime,  DateAdd("d",7,[Startdate]) AS NewStartdate, DateAdd("d",7,[Enddate]) AS NewEnddate
FROM (tblWerknemers INNER JOIN tblPlanning ON tblWerknemers.EmployeeID = tblPlanning.EmployeeID) LEFT JOIN tblWerknemerDetails ON tblWerknemers.EmployeeID = tblWerknemerDetails.EmployeeID
WHERE (((tblPlanning.LocationID)=[Forms]![frmDialogEnterPlanning]![cboLocation]) AND ((Format([Startdate],"ww",2))=Format(Date()-7,"ww",2)))
ORDER BY tblPlanning.Startdate, tblPlanning.Starttime;

 

[JBinQLD]

No drama mate