Auto-creation of new record based on Y/N entry

[vangogh228]

I have been asked if the following is possible and, if so, to implement it, but I have NO idea how to do it. Any help is GREATLY appreciated!!

The database tracks various service activities for each client for each year. Each year has a start date (contract date) that can be anytime throughout the year, though eventually they want all to be converted to Dec 31. The service activities have two dates related to them: their individual target date, which is calculated from the start date and is shown in a text box, and a completion date which is filled in by the individual who completed the task. Each set of tasks must be done in each year. The tasks are listed in a subform of the client data form. Each year's service activity is a single record in a related table, so that you can scroll in the subform from year to year and see that the activities were completed.

The subform also contains an "Old Data" Y/N field. The form is based on a query that shows ONLY those records that do not have that Y/N checked. That way, they see only current data. If they need to view the "Old Data," they do that in the table itself.

What is being asked is this: When a service rep completes the final task for a year's service for a client, he checks the "Old Data" box and then manually enters the next year's start date to create a new record. They would like to have it so that, when they check the "Old Data" box and close the form, a new record with the next year's date is already created.

Again, I have NO idea how to do this. Thanks for any help!!

Tom

 

[WayneRyan]

Tom,

After the user has entered the date field you
can try one of the following:

' ***********************************************
Dim dbs As Database
Dim rst As Recordset
Dim sql As String

Set dbs = CurrentDb
sql = "Insert Into YourTable (Field1, Field2) " & _
"values('" & Me.Field1 & "', #" & Me.EndDate & "#)"
dbs.Execute(sql)

' ***********************************************

Or


' ***********************************************
Dim dbs As Database
Dim rst As Recordset
Dim sql As String

Set dbs = CurrentDb
sql = "Select * from YourTable"
Set rst = dbs.OpenRecordset(sql)
rst.AddNew
rst!Field1 = Me.Field1
rst!Field2 = Me.Field2
rst.Update
' ***********************************************


hth,
Wayne

 

[vangogh228]

Wayne: Thanks so much. I will try that. I don't understand it fully, but I will mock up a dummy DB and give it a try. Thanks again.

Tom