468,161 Members | 2,092 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,161 developers. It's quick & easy.

Insert Into - Code

I'm relatively new to VBA, and was wondering if someone would help me debug the code below. I want to put it in a form, so that when I click "OK" it inserts a new record into the ActionItems table. I only want to insert the one field "Review_ID" which is a foreign key, although the table has many other fields. The primary key in the table is ActionItem_ID, and is an Autonumber. The primary key is the only field indexed with "Yes (No Duplicates)". (Although I do have a few fields indexed with "Yes (Duplicates OK)". After the entry is added I want to open a second form "Add ActionItem Template" to edit the remaining fields in the table, and close my filtering form "Add ActionItem Form". (please ignore the blatant disregard for naming conventions, I am working in a database I did not design). Any help you could offer would be very much appreciated.

Code Start:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.   Me.Visible = False
  4.     INSERT INTO ActionItems(Review_ID)
  5.     VALUES ('" + Review_Date.Value + "')"
  6.     SELECT * FROM ActionItems"
  7.     DoCmd.OpenForm "Add ActionItem Template"
  8.     DoCmd.Close acForm, "Add Action Item Form"
  9. End Sub
Code End

Oct 4 '07 #1
2 1444
try this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2. Me.Visible = False
  4. constr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  5.         "mydb.mdb" & ";"
  7. Dim con As ADODB.Connection
  8. Dim db As Database
  9. Set con = New ADODB.Connection
  10. con.ConnectionString = constr
  11. con.Open
  13.       INSstr = "INSERT INTO" & "ActionItems Review_ID" &
  14. "VALUES"  & Review_Date.Value &";"
  15.   Debug.Print INSstr
  16. con.Execute INSstr
  18. DoCmd.OpenForm "Add ActionItem Template"
  19. DoCmd.Close acForm, "Add Action Item Form"
  21.            End If
  23. End Sub
Oct 4 '07 #2
Thanks alot, I appreciate your help
Oct 4 '07 #3

Post your reply

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

Similar topics

10 posts views Thread by shank | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
16 posts views Thread by robert | last post: by
20 posts views Thread by Mark Harrison | last post: by
9 posts views Thread by anachronic_individual | last post: by
6 posts views Thread by rn5a | last post: by
reply views Thread by kamranasdasdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.