By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 1,045 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

Autoupdate all records in a table using VBA and SQL

P: 3
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim date As Variant
  3. date = Me.[Thesis Plan Due Date].value
  4. CurrentDb.Execute ("INSERT INTO [Thesis Applicant Information]( [Thesis Plan Due Date])" _
  5.  & "SELECT " & date ";")
  6.  
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
Jun 5 '07 #1
Share this Question
Share on Google+
4 Replies

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo_ThesisPlanDueDate_AfterUpdate()
  2.     Dim strDueDate As String
  3.    'I use vbNullstring just in case there isn't anything in the textbox.
  4.     strDueDate = Me.[Thesis Plan Due Date].Value & vbNullString
  5.     If IsDate(strDueDate) = True Then
  6.  
  7.         Dim rst As DAO.Recordset
  8.  
  9.         Set rst = CurrentDb![Thesis Applicant Information].OpenRecordset
  10.  
  11.         Do While Not rst.EOF
  12.         CurrentDb.Execute Query:="INSERT INTO [Thesis Applicant Information] ([Thesis Plan Due Date]) VALUES ('" & strDueDate & "')"
  13.           rst.MoveNext
  14.         Loop
  15.  
  16.  
  17.     End If
  18. End Sub
Jun 5 '07 #2

Rabbit
Expert Mod 10K+
P: 12,441
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.
Jun 5 '07 #3

P: 3
Thanks Rabbit,

I changed it to UPDATE and it's fine now... thanks :d
Jun 5 '07 #4

Rabbit
Expert Mod 10K+
P: 12,441
Not a problem. Good Luck.
Jun 5 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.