INSERT INTO using data from a form AND a table

[Stormrider83]

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?

 

[georgedwilkinson]

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.

 

[Stormrider83]

Thanks!

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

 

[georgedwilkinson]

Excellent! Glad we could help.

 

[aingram]

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

 

[georgedwilkinson]

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".