Sorry if this has been asked before I searched these forums and found nothing so hopefully I am not wasting anyones time!
I am using Access 2007 and my problem is that I am trying to insert data into a table from both another table and a form using VBA. The Table in question is a link or reference table and I want to create a reference for every record in another table and link it to a record you create yourself via a form.
The code is below and while it complies and executes it does nothing. I think its because I enforce referential integrity in the reference table so two SQL statements wont work, however I cant for the life of me work out how to combine them into one single statement.



strSQL = " INSERT INTO Production_Run_Greenhouses (Greenhouse) " _
& "SELECT (Greenhouse_ID) " _
& "FROM [Greenhouses];"
CurrentDb.Execute strSQL
strSQL = " INSERT INTO Production_Run_Greenhouses (Production_Run,Qty) VALUES ('" & Me.Text11 & "'," & 0 & ")"
CurrentDb.Execute strSQL



Can anyone help?

How about:

strSQL = " INSERT INTO Production_Run_Greenhouses (Greenhouse, Production_Run, Qty) " _
& "SELECT Greenhouse_ID, '" & Me.Text11 & "', 0 " _
& " FROM [Greenhouses];"
CurrentDb.Execute strSQL

Of course, this won't work if there are duplicates that are constrained by a UK or PK.

The "other" solution:
strSQL = " UPDATE Production_Run_Greenhouses SET Production_Run = '" & Me.Text11 & "', Qty = 0;"
CurrentDb.Execute strSQL

This will update all rows in the table, regardless of whether that's what you want or not...so beware.

Also, this is air code! I didn't test it and didn't even really evaluate the syntax. It is for demonstration purposes only and you should validate it before running it in your system.

First block works a treat! Exactly what I wanted thanks!

Excellent! Glad we could help.

strSQL = "INSERT INTO Inspections([IDCode], [Route], [Gap],[Date_Done], [Due After], [Due Before],[Days_Gap],[Month],[Date_Last_Inspected]) " _
& "SELECT main_table.[IDCode], main_table.[Area], main_table.[Gap], main_table.[Date], me.DONE_AFTER, me.Done_BEFORE,[Days_Gap],me.txt_month,me.olddate " _
& "FROM [main_table]" _
& "WHERE (((main_table.[IDCode])=[Forms]![FRM_Inspection]![IDCode]));"


hi,

im trying to do the same as the other forum go'er, with no sucess - the sql staement is left looking for data

Ive got my

Main_table
&
Inspection

I also have my FRM_Inspection where i call the sql statment - i need some text boxs on the form also tranfered into the inspection table

I think my differs from above due to me where statement

This really has very little to do with the original post. But you realize you don't have enough spaces in your query, right? Specifically, you need a space between your from clause and "WHERE".

It's safest to put a space before and after each line continuation character.

Also, you can easily figure stuff like this out by putting:
Debug.Print strSQL

right after you build the string. Look in the immediate window to see "how it went".