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

Insert query using values and query

P: 1
Hello everyone,

I am trying to make an insert query that inserts information using form values and a query that gets the id from the name of a field in the form.

I got a form that shows object name, date completed and department completed.

I am trying to do the following:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO DB (Person_id, Object_id, Date_completed, Department_completed) Values (Forms!Form1!Person_id, Querydata, Forms!Form2!Date_completed, Forms!Form2!Department_completed);
The query that i need data from selects the ID that belongs to the object_name in the form because this is a key field in the db i want to insert.

I tried many things but none seem to work. Do you guys have an idea how i can accomplish this?

Or is there a way to use the same syntax as forms!Formname!Formfield for queries?

Thanks in advance!
Apr 23 '11 #1

✓ answered by ADezii

Here is the Logic as to how this can be accomplished, make your own substitutions where necessary. Certain assumptions were made concerning Data Types, Field Names, etc.:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim lngPersonID As Long
  3. Dim dteDateCompleted As Date
  4. Dim strDeptCompleted As String
  5. Dim lngID As Long
  6.  
  7. 'Variable Assignments taken from Form (No Validation, No Data Type Checking!)
  8. lngPersonID = Me![Person_id]
  9. dteDateCompleted = Me![Date_completed]
  10. strDeptCompleted = Me![Department_completed]
  11.  
  12. lngID = DLookup("[ID]", "QueryData", "[Object Name]='" & Me![Object_Name] & "'")
  13.  
  14. strSQL = "INSERT INTO DB (Person_id, Object_id, Date_completed, Department_completed) Values (" & _
  15.           lngPersonID & "," & lngID & ",#" & dteDateCompleted & "#,'" & strDeptCompleted & "');"
  16.  
  17. CurrentDb.Execute strSQL, dbFailOnError

Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,638
Here is the Logic as to how this can be accomplished, make your own substitutions where necessary. Certain assumptions were made concerning Data Types, Field Names, etc.:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim lngPersonID As Long
  3. Dim dteDateCompleted As Date
  4. Dim strDeptCompleted As String
  5. Dim lngID As Long
  6.  
  7. 'Variable Assignments taken from Form (No Validation, No Data Type Checking!)
  8. lngPersonID = Me![Person_id]
  9. dteDateCompleted = Me![Date_completed]
  10. strDeptCompleted = Me![Department_completed]
  11.  
  12. lngID = DLookup("[ID]", "QueryData", "[Object Name]='" & Me![Object_Name] & "'")
  13.  
  14. strSQL = "INSERT INTO DB (Person_id, Object_id, Date_completed, Department_completed) Values (" & _
  15.           lngPersonID & "," & lngID & ",#" & dteDateCompleted & "#,'" & strDeptCompleted & "');"
  16.  
  17. CurrentDb.Execute strSQL, dbFailOnError
Apr 24 '11 #2

Post your reply

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