Calling a Microsoft Access Query and Passing Parameters

[pbaldy]

I didn't say it would pop up the string, I said it would print it out to the VBA Immediate window. That's normally displayed at the bottom of the screen in the VBA editor.

I just noticed you have a FROM clause, which isn't a appropriate for a VALUES clause (it would be for a SELECT clause). For the VALUES method the syntax is:

INSERT INTO TableName(Field1, Field2,...)
VALUES('ABC', 123,...)

I've also never tried the subqueries in a VALUES clause, but take everything out from the FROM and see where we are.

[Scyclone]

Yeah, I was looking at the VBA window during debug, unfortunately it didnt list any screen (nor was I able to find it on a drop down) for the debug code.

The from clause can work in SQLs case at times but it does also require a select inside of the insert. I took it out and that portion seemed to work fine, I feel kind of stupid for having left it there. Next time ill just code it in pure code as opposed to trying to get Accesses designer thing for querys to agree with me lol.

But anyway

Here is the error coming up once the from and such is removed and I make sure to close off the values appropriately.

Run-time error: '3219'
Invalid Operation

=======

Here is the whole click event

Private Sub cmdSave_Click()

Dim strSQL As String

strSQL = "INSERT INTO ProdList ( ProdName, ProdPartNo, ProdDescription, ProdBrandID, ProdCatID, StatusID ) VALUES ('" & [Forms]![Add New Product]![ProdName] & "' , '" & [Forms]![Add New Product]![ProdPartNo] & "' , '" & [Forms]![Add New Product]![ProdDesc] & "' , (SELECT ID FROM ProdBrand WHERE '" & [Forms]![Add New Product]![BrandDrop1] & "' = BrandName) , (SELECT ID FROM ProdCat WHERE '" & [Forms]![Add New Product]![CatDrop1] & "' = CatName) , '" & [Forms]![Add New Product]![optStatus] & "');"

MsgBox (strSQL)

CurrentDb.OpenRecordset strSQL

End Sub

====

Thoughts?

It looked like it would have worked.

[pbaldy]

Does this?

CurrentDb.Execute strSQL, dbFailOnError

[Scyclone]

Query input must contain at least one table or query.

Oi... :P

I also tried a few different combinations, such as using ()s around strSQL and dbFail, and even just strSQL itself. Same error... oi, we are close too

[pbaldy]

It would help to see the finished SQL, but offhand it looks like you have quotes around the field names in the subqueries, which you don't want. In other words, you don't want

WHERE 'FieldName' = 123

you want

WHERE FieldName = 123

Those quotes would be required around the value if the data type of the field was text, like

WHERE FieldName = 'ABC'

[Scyclone]

Yeah I switched it around but still gave issues. So I am breaking up the subquerys into queries running before.

I have rsBrand now housing the record set for the Select ID subquery. Now, how would I obtain the ID value as a string, is there a Cast or Parse method for it?

[TinaCa]

Suggestions; I put my create my queries in the query design grid first. Get the select part working then do the Append (Insert). If it works, I delete the data that running the insert query created in my table, and then view the query in SQL view, copy the information and paste that sql statement into my sub routine. You will need to remove/replace quotes with apostrophes ' and enclose any dates properly.

I included a copy of recent code I just did using this method. Also, you need Docmd.RunSql then the insert statement or it won't run . The " _ and & " are so you can view the data in the window without scrolling left and right .

DoCmd.RunSQL "INSERT INTO tblInvoiceDetails ( CompanyId, CompanyBillingDt, " _
& "ItemId, ItemDate, ItemDescription, ItemAmount, Category, InvoiceDate ) " _
& "SELECT CompanyInformation.CompanyAccountNo, CompanyInformation.CompanyBillingDate," _
& "CompanyInformation.CompanyAccountNo, #" & InvDt & "# AS ItmDt, 'Monthly Contact Amount' AS Descrp, " _
& "CompanyInformation.CompanyContractAmt, 'M' AS Cat, #" & InvDt & "# AS ItemDt " _
& "FROM (CompanyInformation)" _
& "WHERE (((CompanyInformation.CompanyBillingDate) = " & BillDt & "));"

[Scyclone]

Hit it with a few things and got it to work!

Double checked the table and it inserted it without an issue once I removed the subquerying.

Thanks again for your help, both of you