Insert query using values and query

[Marco van der M]

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:

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!

[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.:

Dim strSQL As String
Dim lngPersonID As Long
Dim dteDateCompleted As Date
Dim strDeptCompleted As String
Dim lngID As Long
 
'Variable Assignments taken from Form (No Validation, No Data Type Checking!)
lngPersonID = Me![Person_id]
dteDateCompleted = Me![Date_completed]
strDeptCompleted = Me![Department_completed]
 
lngID = DLookup("[ID]", "QueryData", "[Object Name]='" & Me![Object_Name] & "'")
 
strSQL = "INSERT INTO DB (Person_id, Object_id, Date_completed, Department_completed) Values (" & _
          lngPersonID & "," & lngID & ",#" & dteDateCompleted & "#,'" & strDeptCompleted & "');"
 
CurrentDb.Execute strSQL, dbFailOnError