Autoupdate all records in a table using VBA and SQL

[Parsa]

Hello everyone,

I have been looking on the net to solve my problem for several days now, but i couldn't find what I was looking for.

I'm designing a database for a university and in one of the forms I have a text field called "Thesis Plan Due Date". User will enter the due date for submission of student's thesis in this text field.

What i want to do is that when the user has specified the due date for the first student, I want the vba code to copy that due date to the "Thesis Plan Due Date" fields of all the student in the "Thesis Applicant Information" table. I know that this code should go in the AfterUpdate event of my text field, but I'm not sure how it should be written.

I made the following SQL statemtn but I'm not sure if it's suppose to do what I want :D

 
Dim date As Variant
date = Me.[Thesis Plan Due Date].value
CurrentDb.Execute ("INSERT INTO [Thesis Applicant Information]( [Thesis Plan Due Date])" _
 & "SELECT " & date ";")
 

What i found on the net was how to copy the fields in one table to another table but how should I write the VBA or SQL statement do what I want.


Thanks in advance for your time.

P.S. I recently found this site and this is my first post :D

[Parsa]

I have come up with this code so far, can anyone tell me why it is not working?

Private Sub cbo_ThesisPlanDueDate_AfterUpdate()
    Dim strDueDate As String
   'I use vbNullstring just in case there isn't anything in the textbox.
    strDueDate = Me.[Thesis Plan Due Date].Value & vbNullString
    If IsDate(strDueDate) = True Then
 
        Dim rst As DAO.Recordset
 
        Set rst = CurrentDb![Thesis Applicant Information].OpenRecordset
 
        Do While Not rst.EOF
        CurrentDb.Execute Query:="INSERT INTO [Thesis Applicant Information] ([Thesis Plan Due Date]) VALUES ('" & strDueDate & "')"
          rst.MoveNext
        Loop
 
 
    End If
End Sub

[Rabbit]

An INSERT INTO query adds records, you're probably looking for an UPDATE query.

Also, if it is a date, you shouldn't store it as a text. It screws up sorting, filtering, and calculations that you can perform on a date.

[Parsa]

Thanks Rabbit,

I changed it to UPDATE and it's fine now... thanks :d

[Rabbit]

Not a problem. Good Luck.