Make multiple records based on occurance

[alnilla]

Hi guys,

I am making a database that has to do with Preventive maintenance. Everything is going good and I came to a problem. There are recurring events. Some are weekly monthly quarterly etc all the way to 5 years. When I put in the task and a start date and the occurrence. So Say I put in a date of a task for today and its set to monthly I want the next record to be 1/13/14 for the task and go all the way up to infinity until a box is checked making it inactive.

Any suggestions??


Thank you

 

[David R]

I have a suggestion... don't make infinite records! Access has a 2GB limit.

All joking aside, I'm not entirely clear on what you're asking. Are you trying to figure out how to do the 'occurrence' intervals? Look into DateAdd().

 

[alnilla]

This is the form that I have what I would like is once the completed box is checked it takes all the information the Same Equipment, Location, Task and create a new record. With the new record the Date Requested would be the Due Next Date and the new due next date would be 3 months from that current date. Does that make any sense there?? And i would put a limit on it to for occurances

 

[David R]

Did you play with dateadd? As long as you're not trying to do things like 'on the 1st Friday of each month', it's dead simple to do occurrences that are days/weeks/months/quarters/years apart.

You can do the other (I have a module that schedules neighborhood meetings), but it requires a fair amount of VBA.

 

[alnilla]

Yes I have the date add to Due Next. What im trying to do is copying the info over to a new record on complete.

 

[David R]

What's stronger for you, SQL or VBA? There are two ways to do this... (UPDATE query or Recordset).

 

[alnilla]

SQL

I am assuming I am going to have to do a

!Equipment=Me.Equipment
!Task=Me.Task
etc for all that?

 

[David R]

That would be VBA.

SQL would be an append query (not update, sorry...lunchtime) that takes the information from your current record and appends a new record with it, except swapping the calculated DueNext date into the DateRequested field.

Play with it in query design, and post back if you get trouble. You can reference fields on open forms directly in your query, so you don't have to do the occurrence calculation twice... then you just make a button or event that triggers the query to run ONCE when this record is completed. Infinity is safe!

 

[alnilla]

Thanks for your help the append qry works but if i have 6 records in there it copies all 6 records over instead of the current one I am working on. In the append qry in the Criteria I put in the "PMID= "& [PMID] and it doesnt like that at all either. So its working just copying all the records instead of the current.

Thank you for the help again

 

[David R]

Force your Append query to refer directly to the record you're in, instead of the whole table... that's what the Primary Key field is good for (you have a PK, right???)

Post your append query SQL if you like, and indicate what the PK field is.

 

[alnilla]

INSERT INTO [PM Detail] ( Equipment_ID, DateRequested, DateCompleted, Completed, CompletedBy, Task_ID, Occurance_ID, NextOccuranceDate, PMID )
SELECT FormPMDataEntryNC.Equipment_ID, FormPMDataEntryNC.DateRequested, FormPMDataEntryNC.DateCompleted, FormPMDataEntryNC.Completed, FormPMDataEntryNC.CompletedBy, FormPMDataEntryNC.Task_ID, FormPMDataEntryNC.Occurance_ID, FormPMDataEntryNC.NextOccuranceDate, FormPMDataEntryNC.PMID
FROM FormPMDataEntryNC
WHERE (((FormPMDataEntryNC.PMID)=[FormPMDataEntryNC]![PMID]));

PMID is PK

 

[David R]

Is FormPMDataEntryNC actually a form, or a table? Your query should be pulling directly from the table itself EXCEPT for that calculated NextOccurrenceDate. Unless I'm misunderstanding something about your data structure...

Also, you don't want to be copying your DateCompleted, CompletedBy, etc, do you??

 

[alnilla]

FormPMDataEntryNC is a form I used a query to make the form.

I deleted everything from the append qry only thing I have left is


Equipment_ID
Task_ID
Occurance_ID
PMID

When I run the qry it still wants to add three rows not one. It says it set the fields to Null due to a type conversion failure

Your right I dont wanna copy them over so I removed them all

 

[David R]

Okay you're trying to copy too much. Try this (not sure what TaskID is, you may need that too, but we're still testing):

INSERT INTO [PM Detail] ( Equipment_ID, DateRequested, Occurance_ID )
SELECT [PM Detail].Equipment_ID, Forms.FormPMDataEntryNC.NextOccuranceDate, [PM Detail].Occurance_ID
FROM [PM Detail]
WHERE ((([PM Detail].PMID) = Forms.FormPMDataEntryNC.PMID));

Do you see the difference? This says "go to the table record that matches the current form record, and grab these specific fields". I suspect your query was getting confused because FormPMDataEntryNC.PMID is ALWAYS the same as FormPMDataEntryNC.PMID.

PMID is a Primary Key for [PM Detail], you CAN'T copy it back into the same table! Access will create that value for you, assuming it's an Autonumber field like it should be...

 

[alnilla]

Ahhh makes sense why it didn't work. Thank you for your time!