Auto Create Records

[CanWest]

I have read a number of posts regarding this and most people suggest that this is a bad idea. Having said that I can not think of another way to do what I need done. I will explain this as best I can but please bare with me as I am still learning this stuff.

The purpose of the DB is to track all activities of a client as they go through the process of finding employment.

There are two tables involved
tbl_Placement used to track the fact that the placement process has begun
tbl_FollowUps used to track follow ups that are required at specific intervals

When a client enters into the placement phase of his/her activities a record is created in tbl_Placement via a form. At this point a Start Date is picked, usually the day the record is created.

At this point I need to create in tbl_FollowUps seven records for the subsequent followups that must happen on specific dates. The first is 7 days from the start date and the rest are at one month intervals from the start date.

I know how to calculate those dates, I know how to get the 'Assigned Case Manager' for each record. What I can not figure out is how to create the seven records in the first place.

Any suggestions or ideas will be greatly appreciated.

 

[Poppa Smurf]

You could use 7 queries to append the records for the specific time intervals or use a few lines of code to append the records for the time interval.

 

[pbaldy]

I'd use a For/Next loop and the AddNew method of a recordset.

 

[CanWest]

I would prefer to use code. I simply have no idea where to begin. Can someone sugesst a resource where I can read up on this.

Again many thanks

 

[Poppa Smurf]

Post a sanitised version (i.e. No personal data etc.) and I will give you assistance.

 

[CanWest]

Post a sanitised version (i.e. No personal data etc.) and I will give you assistance.

Thank you. This db was a split database but I joined it for simplicity. It is also locked down somewhat so I created an admin user called papa smurf with the password of password

Using Shift double click will bypass startup options in this version

 

[pbaldy]

I simply have no idea where to begin.

Did you see post 3?

 

[CanWest]

Did you see post 3?

I did. As I mentioned I have no idea how to do that hence the reason I asked for some reading material.

 

[Poppa Smurf]

Look up the following in Access
- DateAdd function to add days and months.
- For .. next as suggested in message 3
- AddNew method

This is the work flow that follows this line of code
Me.ChangeDate = Now()
You will need an AddNew statement to add the first followup date which will be 7 days after the create date.

Then you will need a For... Next statement, inside this For.. Next statement you will need another AddNew statement to add the next 6 followup dates using the DateAdd function. In your DateAdd function the interval will be the counter used in the For .. Next statement.

If after reading the above functions etc. you are still having problems please contact the forum with your code etc. I have a solution using the above functions etc. but I would like you make an attempt.

 

[CanWest]

I have a question or two. I just spent several hours researching this and watching some amazing tutorial videos.

When working with AddNew I understand how to add a new record within a recordset and assign values to fields.

My first question is can I pull the field values from an open form

I have also got a basic handle on the for / next function and have discovered a whole new way to do a lot of the things I already do with a lot more code than needed.

My second question is this. Is there a way to specify different values for the fields on each loop. The info I found so far has not gotten into that.

 

[Poppa Smurf]

My first question is can I pull the field values from an open form

Yes, you can pull the field values from an open form e.g.![your table field name]=me.(your form control name). Using your Placement form as an example you add the value of the start date in the AddNew to the CreateDate in the followup table e.g. ![CreateDate] = DateAdd("d", 7, Me.StartDate) this will add 7 days to the start date on your form and store it in the CreateDate of your followup table. Note this is not the full code only a snippet.

My second question is this. Is there a way to specify different values for the fields on each loop. The info I found so far has not gotten into that.

Yes, have a look at the SELECT CASE .... END SELECT, this statement will be inside the AddNew and each cycle of the FOR ... Next will trigger the relevant Case value for you to assign the different values.

 

[CanWest]

So I have been hard at work. I am taking this a step at a time and have code that successfully creates seven new records in tbl_FollowUp. It also populates the fields I need populated.

Dim i As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_FollowUp")

rs.MoveLast
rs.MoveFirst

For i = 1 To 7

    rs.AddNew
    rs.Fields("Period") = "0"
    rs.Fields("StaffCode") = "SD"
    rs.Fields("ClientID") = Me.ClientId
    rs.Fields("Level") = Me.Level
    rs.Fields("PlacementID") = Me.PlacementID
    rs.Fields("ExpectedContactDate") = DateAdd("d", 7, Me.StartDate)
    rs.Fields("CreateChangeBy") = "Forms!frm_LogonStorage!FullUserName"
    rs.Fields("LastUpdatedBy") = "Forms!frm_LogonStorage!FnameLname"
    rs.Fields("CreateDate") = Now()
    rs.Fields("ChangeDate") = Now()
    rs.Update
    rs.MoveNext
Next i

rs.Close
Set rs = Nothing
db.Close

I have used select case before. I am not sure how that is going to help me here.
Each new record needs to have different data in Period, StaffCode and ExpectedContactDate and I am not quite sure how to do that.

I 'could' abandon the For / Next method and simply have this code for every record with the correct data in other words have very similar code seven times over. But I am here to learn and I am sure there is an easier way.
Any suggestions

 

[CanWest]

So I have been hard at work. I am taking this a step at a time and have code that successfully creates seven new records in tbl_FollowUp. It also populates the fields I need populated.

Dim i As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_FollowUp")

rs.MoveLast
rs.MoveFirst

For i = 1 To 7
    rs.AddNew
    rs.Fields("Period") = "0"
    rs.Fields("StaffCode") = "SD"
    rs.Fields("ClientID") = Me.ClientId
    rs.Fields("Level") = Me.Level
    rs.Fields("PlacementID") = Me.PlacementID
    rs.Fields("ExpectedContactDate") = DateAdd("d", 7, Me.StartDate)
    rs.Fields("CreateChangeBy") = "Forms!frm_LogonStorage!FullUserName"
    rs.Fields("LastUpdatedBy") = "Forms!frm_LogonStorage!FnameLname"
    rs.Fields("CreateDate") = Now()
    rs.Fields("ChangeDate") = Now()
    rs.Update
    rs.MoveNext
Next i

rs.Close
Set rs = Nothing
db.Close

I have used select case before. I am not sure how that is going to help me here.
Each new record needs to have different data in Period, StaffCode and ExpectedContactDate and I am not quite sure how to do that.

I 'could' abandon the For / Next method and simply have this code for every record with the correct data in other words have very similar code seven times over. But I am here to learn and I am sure there is an easier way.
Any suggestions

 

[pbaldy]

Correct me if I'm wrong, but if i = 1 then you want to add a week, else add i - 1 months.

 

[CanWest]

Yes
Each record added needs to have ..
One week added to the StartDate on the first record
And each consecutive record added needs 1 month added totalling 7 records and 6 months
In addition each record added has a different StaffCode and a Different Period

 

[pbaldy]

I hoped that would lead you to an If/Then/Else block to populate the date.

 

[Poppa Smurf]

This is the code to add 1 day for the first record and then add 1 month for the next 6 records using your Save Placement button.

As PBaldy stated you are adding 7 days. Have a look at your FollowUp table and you will notice that you have 7 records all with the same date.

I am using counter_followup as the interval in the second Dateadd statement to the correct number of months after the CreateDate.

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
Dim db As DAO.Database
Dim rst_output As DAO.Recordset
Dim counter_followup As Integer
Set db = CurrentDb()
Set rst_output = db.OpenRecordset("tbl_Followup")

Me.ClientID = Forms![frm_MainMenu]![sfrm_Clients].Form![ClientID]
Me.Level = Forms![frm_MainMenu]![sfrm_Clients].Form![CurrentLevel]

Me.CreateChangeBy = Forms!frm_LogonStorage!FullUserName
Me.LastUpdatedBy = Forms!frm_LogonStorage!FNameLName
Me.CreateDate = Now()
Me.ChangeDate = Now()

With rst_output
' add first followup 7 days after the create date
rst_output.AddNew
![CreateDate] = DateAdd("d", 7, Me.StartDate)
' add other fields here as required
rst_output.Update

' now add the next followup dates
For counter_followup = 1 To 6
rst_output.AddNew
![CreateDate] = DateAdd("m", counter_followup, Me.StartDate)
' add other fields here as required
rst_output.Update
Next counter_followup
End With
rst_output.Close

DoCmd.Close acForm, "pfrm_AddClientPlacement", acSaveNo
Forms![frm_MainMenu]![sfrm_Clients].Form![sfrm_ClientPlacement].Requery
Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End Sub

When you are happy with this code then we will move onto using the Select Case End Select statement to add.

Is period the only value that will be different?

 

[CanWest]

This is the code to add 1 day for the first record and then add 1 month for the next 6 records using your Save Placement button.

I placed this code where you suggested. I had to make one minor change in two places, Below is a section of the code where I made the changes and where there seems to be a problem.

With rst_output
' add first followup 7 days after the create date
rst_output.AddNew
[COLOR="Red"][B]![ExpectedContactDate] = DateAdd("d", 7, Me.StartDate)[/B][/COLOR]
' add other fields here as required
rst_output.Update

' now add the next followup dates
For counter_followup = 1 To 6
rst_output.AddNew
[COLOR="red"][B]![ExpectedContactDate] = DateAdd("m", counter_followup, Me.StartDate)[/B][/COLOR]
' add other fields here as required
rst_output.Update
Next counter_followup
End With
rst_output.Close

I had to change the output field as CreateDate was not the one i needed. ExpectedContactDate is.

I get the following error when this code is run

Runtime error 3625
Item not found in this collection

The line of code that seems to be causing this error is the first Red/Bold line above. If I comment that line out then the second Red / Bold line gives the same error.

Is this error saying that the field is not in the recordset? I tried a couple of the other fields that need to be updated, one with static data and one with a form reference and they all give the same error.

I have read this article and it does not seem to be the same
http://www.access-programmers.co.uk/forums/showthread.php?t=107275

 

[Poppa Smurf]

The error means as you stated the field name ExpectContactDate is not as field in your table. Is the spelt correctly and are you using the correct table? You should have a line of code similar to this
Set rst_output = db.OpenRecordset("tbl_Followup")

I changed the code in my copy of your database and it works. I have attached my modified copy that just has your placement form pfrm_AddClientPlacement I have commented out any reference to the login form etc.

 

[CanWest]

The error means as you stated the field name ExpectContactDate is not as field in your table. Is the spelt correctly and are you using the correct table? You should have a line of code similar to this
Set rst_output = db.OpenRecordset("tbl_Followup")

Turns out I had "" around my field names like this

[COLOR="Red"][B]!["ExpectedContactDate"] = DateAdd("d", 7, Me.StartDate)[/B][/COLOR]

So the only thing that remains is the period. The first one is easy because it is simply a 0 (zero)

records 2 through 7 are the numbers 1 through 6

I am going to look at this in the AM as my eyes are bugging out of my head