Trouble getting SQL to run in VBA

[bhujanga]

I designed a query for appending rows to a table. The SQL view for the query looks like this:

INSERT INTO ComplienceLabelInfo ( ProductionLotID, [Best By Date] )
SELECT [forms]![Production Log (Elixirs)]![Product Lot ID] AS [Product Lot ID], [forms]![Production Log (Elixirs)]![Production Date]+90 AS [Best By Date];

I want to put it into the vba code so it happens automatically, so I tried this:

Dim strSQL2 As String
strSQL2 = "INSERT INTO ComplienceLabelInfo ( ProductionLotID, [Best By Date] ) " & _
"SELECT [forms]![Production Log (Elixirs)]![Product Lot ID] " & _
"AS [Product Lot ID], [forms]![Production Log (Elixirs)]![Production Date]+90 AS [Best By Date];"
CurrentDb.Execute strSQL2, dbFailOnError

I get the following error message:
"Too few parameters. Expected 2."
(The last line of the code is highlighted)
I don't know what parameters it's talking about.

 

[SkipVought]

hi,

you do not need the Select. You are just inserting TWO values from your FORM...

    strSQL2 = "INSERT INTO ComplienceLabelInfo "
    strSQL2 = strSQL2 & "( ProductionLotID"
    strSQL2 = strSQL2 & ", [Best By Date] ) "
    strSQL2 = strSQL2 & "  [forms]![Production Log (Elixirs)]![Product Lot ID]"
    strSQL2 = strSQL2 & ", [forms]![Production Log (Elixirs)]![Production Date]+90;"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bhujanga]

I pasted the code in so it now looks like this:

Dim strSQL2 As String
strSQL2 = "INSERT INTO ComplienceLabelInfo "
strSQL2 = strSQL2 & "( ProductionLotID"
strSQL2 = strSQL2 & ", [Best By Date] ) "
strSQL2 = strSQL2 & " [forms]![Production Log (Elixirs)]![Product Lot ID]"
strSQL2 = strSQL2 & ", [forms]![Production Log (Elixirs)]![Production Date]+90;"
CurrentDb.Execute strSQL2, dbFailOnError

I'm now getting the message:
"Run Time Error 3134:
Syntax error in INSERT INTO statement"
I've double checked all of the field and control names - any idea what could be wrong?
Thanks.

 

[SkipVought]

Do you have Update SQL that you can run in a query manually? THAT's where you need to begin to get some clean code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Put a watch on strSQL2

See what it evaluates to - that will give you you're answer

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Andrzejek]

You may try:

Dim strSQL2 As String 
strSQL2 = "INSERT INTO ComplienceLabelInfo "    
strSQL2 = strSQL2 & "( ProductionLotID"    
strSQL2 = strSQL2 & ", [Best By Date] ) "    
strSQL2 = strSQL2 & " [blue]VALUES([/blue][forms]![Production Log (Elixirs)]![Product Lot ID]"    
strSQL2 = strSQL2 & ", [forms]![Production Log (Elixirs)]![Production Date]+90[blue])[/blue];" 
[blue]Debug.Print strSQL2[/blue]
CurrentDb.Execute strSQL2, dbFailOnError

and see what you get in Debug.Print line

Have fun.

---- Andy

 

[bhujanga]

Nothing seems to be working. I can cut and paste the text for sqlStr2 into a query and the query works fine, but when the vba routine tries to run it I either get the 'expected 2 parameters' error or the other error.
I found an alternate method which is working for me - it's this:

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("ComplianceLabelInfo", dbOpenDynaset, dbAppendOnly)
If IsNull(Actual) Then LabelRows = RunSize Else LabelRows = Actual
LabelRows = Int(LabelRows / 4) + 1
With rs
For lng = 1 To LabelRows
.AddNew
![Product Lot ID] = [ProdLotID]
![Best By Date] = ProductionDate + 90
.Update
Next
End With
rs.Close
Set rs = Nothing
Set db = Nothing

I appreciate all of your help.