Update a date and subsequent dates will be automatically updated.

[perryc]

All,


Probably need a Script for this... not sure how to write it. Any help appreciate it.

1. Is there a way to change the first date on this query (3/14) and subsequent dates can advance accordingly? e.g. 3/14 becomes 5/10 and 3/15 will automatically becomes 5/11 and 3/16 becomes 5/12?

[Welshgasman]

Calculate the difference between first date and new date.
Then update dates to date + difference

[perryc]

That's sound good. But how do I write a script to update these dates? I am a very novice Access user that really don't know how to write these. Ty in advance if you or anyone can help.

[June7]

So you want to pull the minimum date and increment from there? How does user decide on the date to change to? Enter into a textbox?

CurrentDb.Execute "UPDATE tablename SET DateAssigned = DateAssigned + " & Me.tbxNewDate - DMin("DateAssigned", "tablename")

Include filter criteria in the DMin() as well as the UPDATE if necessary.

[perryc]

1. So you want to pull the minimum date and increment from there? (yes. However, some dates are duplicated because there are different time)
2. How does user decide on the date to change to? Enter into a textbox? Enter from a text box (date).

[Welshgasman]

So if you have a form, perhaps allow them to select a date from a combo list, a textbox holding the new validated date, then a button to update using June7 code.

[perryc]

Your formula works perfectly. Ty soooooo much.

I also want to update the student ID. What did I do wrong here?

CurrentDb.Execute "UPDATE tblJtnStudentsCourses SET StudentID = me.tbxNewID"

[Micron]

You need to concatenate references. This = me.tbxNewID" is within the quotes so it's trying to literally update StudentID to "me.tbxNewID". It might even work if that field was a text field.

CurrentDb.Execute "UPDATE tblJtnStudentsCourses SET StudentID = " & me.tbxNewID
If the id field is a string, more (single) quotes will be needed to encapsulate the value in me.txtNewID

CurrentDb.Execute "UPDATE tblJtnStudentsCourses SET StudentID = ' " & me.tbxNewID & " ' "
I added spaces so you could see them - you would not include them in your final expression.

[perryc]

I am so bad at this. So, how do you combine the two into just one instead?

CurrentDb.Execute "UPDATE tblJtnStudentsCourses SET DateAssigned = DateAssigned + " & Me.tbxNewDate - DMin("DateAssigned", "tblJtnStudentsCourses")

CurrentDb.Execute "UPDATE tblJtnStudentsCourses SET StudentID = " & Me.tbxNewID

[June7]

CurrentDb.Execute "UPDATE tblJtnStudentsCourses SET DateAssigned = DateAssigned + " & Me.tbxNewDate - DMin("DateAssigned, "tblJtnStudentsCourses") & ", StudentID = " & Me.tbxNewID

That will update EVERY record with the same StudentID.

I expect you need some filter criteria.

Why would you want to change student ID? How does this impact historical records?

I don't even see a StudentID field in that table.

If you want to provide db for analysis, follow instructions at bottom of my post.

[orange]

@perryc

Make sure you have a backup before doing UPDATE queries===especially if you are not familiar with SQL and/or Update.
Update queries can be very unforgiving.

You might consider - do a SELECT query (ReadOnly) until you confirm your criteria.
A SELECT query with appropriate criteria should display the result you want to achieve. If you get that result, you can then switch the SELECT to an UPDATE query.

[perryc]

All, ty so much for your help. I have a major problem with this update. Perhaps I should give you a background first.
What I try to do is to duplicate the template "StudentID 1" and create a new template for every new student with ID 10...11...12... (update those dates to the begin orientation date). (Student ID = Orientee ID)

This script below changed all existing ID and dates to the new dates, which is not what I want. (I do have a backup of the backend DB).

CurrentDb.Execute "UPDATE tblJtnStudentsCourses SET DateAssigned = DateAssigned + " & Me.tbxNewDate - DMin("DateAssigned, "tblJtnStudentsCourses") & ", StudentID = " & Me.tbxNewID

Please help. Ty.

Sincerely,
Perry

[perryc]

Orange, I think you are absolutely correct. The query we had updated every record. I only want to update one certain StudentID for his entire schedule. I am not sure where to add the SELECT statement. Would you help?

Background: What I try to do is to duplicate the template "StudentID 1" and create a new template for every new student with ID 10...11...12... (update those dates to the begin orientation date). (Student ID = Orientee ID)

This script below changed all existing ID and dates to the new dates, which is not what I want. (I do have a backup of the backend DB).

CurrentDb.Execute "UPDATE tblJtnStudentsCourses SET DateAssigned = DateAssigned + " & Me.tbxNewDate - DMin("DateAssigned, "tblJtnStudentsCourses") & ", StudentID = " & Me.tbxNewID

Please help. Ty.

Sincerely,
Perry

[June7]

So you don't really want to change existing records. Sounds like you want to 'batch create' new set of course records for all students.

Consider:

Dim intDays As Integer
intDays = Me.tbxNewDate - Me.tbxOldDate
CurrentDb.Execute "INSERT INTO tablename(StudentID, CourseDetails, DateAssigned, TimeBegins, TimeEnds, Instructor) " & _
                "SELECT StudentID, CourseDetails,  DateAssigned + " & intDays & ", TimeBegins, TimeEnds, Instructor FROM tablename WHERE DateAssigned >=#" & Me.tbxOldDate & "#"

[perryc]

Ty so much June7. I will try your codes after this meeting.