insert record into table sql error

[hoda64]

Hi,

I am trying to move some records from one table to another using sql. When using this code:

CurrentDb.Execute "INSERT INTO LER_Digital ([Originated by], [Date Entered], [Reviewed by], [Date Reviewed], [Title], [Event Date], [Report Date], [Report Link], [LER Number], [Facility], [General Plant System], [Plant System], [Operating Mode], [Power Level], [General Description], [Abstract], [Plant Conditions], [Description of Event], [Inoperable SSCs], [Major Occurrence Time], [Secondary Functions], [Method of Discovery], [Operator Actions], [Safety System Responses], [Immediate Cause], [Root Cause], [Analysis of Event], [Contributing Factors], [Assessment of Safety Consequences], [Immediate Corrective Actions], [Corrective Actions to Prevent Recurrence], [Previous LERs], [Additional Information], [SSFF Consideration], [LNHR Consideration], [Commitments], [Lessons Learned], [Cause], [System], [Component], [Reportable to EPIX], [Manufacturer])" & _
"SELECT [Originated by], [Date Entered], [Reviewed by], [Date Reviewed], [Title], [Event Date], [Report Date], [Report Link], [LER Number], [Facility], [General Plant System], [Plant System], [Operating Mode], [Power Level], [General Description], [Abstract], [Plant Conditions], [Description of Event], [Inoperable SSCs], [Major Occurrence Time], [Secondary Functions], [Method of Discovery], [Operator Actions], [Safety System Responses], [Immediate Cause], [Root Cause], [Analysis of Event], [Contributing Factors], [Assessment of Safety Consequences], [Immediate Corrective Actions], [Corrective Actions to Prevent Recurrence], [Previous LERs], [Additional Information], [SSFF Consideration], [LNHR Consideration], [Commitments], [Lessons Learned], [Cause], [System], [Component], [Reportable to EPIX], [Manufacturer] FROM LER_analog WHERE ID =" & getID

I am getting a "too few parameters" error. both tables have the same fields and i want to move all but one field from one table to another. I am not positive if this makes a difference, but the order that i have typed those fields are not the order the columns are in the table. I have used similar code for moving around records with much fewer fields and it has worked, so I do not know where the error is. I have also double checked the spelling of the fields and they should be correct.

Can anybody spot a problem? Thanks in advance.

 

[pbaldy]

For starters, there will be no space between the ending parentheses on the first line and SELECT on the second, a common mistake. Fix that and see where we are.

 

[hoda64]

Thanks for the response. I am trying to get rid of the space, but because the first line is so long, it is not letting me delete to make it all one line so I thought that syntax is what is used to connect the lines together in a string. If I just leave the "&" and delete the "_" I get a syntax error. Do you know how I could get rid of the space? Thanks again.

 

[pbaldy]

Not get rid of, you need to add one. As is when the two lines are joined you'll get:

"...[Manufacturer])SELECT..."

but you want:

"...[Manufacturer]) SELECT..."

note the space. You can either add one to the end of the first line:

[Manufacturer]) " & _

or the beginning of the second

" SELECT..."

 

[hoda64]

Ah ok, thanks for the clarification, I misread your earlier post.

CurrentDb.Execute "INSERT INTO LER_Digital ([Originated by], [Date Entered], [Reviewed by], [Date Reviewed], [Title], [Event Date], [Report Date], [Report Link], [LER Number], [Facility], [General Plant System], [Plant System], [Operating Mode], [Power Level], [General Description], [Abstract], [Plant Conditions], [Description of Event], [Inoperable SSCs], [Major Occurrence Time], [Secondary Functions], [Method of Discovery], [Operator Actions], [Safety System Responses], [Immediate Cause], [Root Cause], [Analysis of Event], [Contributing Factors], [Assessment of Safety Consequences], [Immediate Corrective Actions], [Corrective Actions to Prevent Recurrence], [Previous LERs], [Additional Information], [SSFF Consideration], [LNHR Consideration], [Commitments], [Lessons Learned], [Cause], [System], [Component], [Reportable to EPIX], [Manufacturer]) " & _
"SELECT [Originated by], [Date Entered], [Reviewed by], [Date Reviewed], [Title], [Event Date], [Report Date], [Report Link], [LER Number], [Facility], [General Plant System], [Plant System], [Operating Mode], [Power Level], [General Description], [Abstract], [Plant Conditions], [Description of Event], [Inoperable SSCs], [Major Occurrence Time], [Secondary Functions], [Method of Discovery], [Operator Actions], [Safety System Responses], [Immediate Cause], [Root Cause], [Analysis of Event], [Contributing Factors], [Assessment of Safety Consequences], [Immediate Corrective Actions], [Corrective Actions to Prevent Recurrence], [Previous LERs], [Additional Information], [SSFF Consideration], [LNHR Consideration], [Commitments], [Lessons Learned], [Cause], [System], [Component], [Reportable to EPIX], [Manufacturer] FROM LER_analog WHERE ID =" & getID

Hmm, I still get the same error though. Any thoughts?

 

[pbaldy]

The error indicates something Access doesn't understand or can't resolve (like if the variable was within the string). By the way, the order of the fields in the query doesn't need to match the order in the table. Let's try this; change the code to:

Dim strSQL As String
strSQL = "INSERT INTO LER_Digital ([Originated by], [Date Entered], [Reviewed by], [Date Reviewed], [Title], [Event Date], [Report Date], [Report Link], [LER Number], [Facility], [General Plant System], [Plant System], [Operating Mode], [Power Level], [General Description], [Abstract], [Plant Conditions], [Description of Event], [Inoperable SSCs], [Major Occurrence Time], [Secondary Functions], [Method of Discovery], [Operator Actions], [Safety System Responses], [Immediate Cause], [Root Cause], [Analysis of Event], [Contributing Factors], [Assessment of Safety Consequences], [Immediate Corrective Actions], [Corrective Actions to Prevent Recurrence], [Previous LERs], [Additional Information], [SSFF Consideration], [LNHR Consideration], [Commitments], [Lessons Learned], [Cause], [System], [Component], [Reportable to EPIX], [Manufacturer]) " & _
"SELECT [Originated by], [Date Entered], [Reviewed by], [Date Reviewed], [Title], [Event Date], [Report Date], [Report Link], [LER Number], [Facility], [General Plant System], [Plant System], [Operating Mode], [Power Level], [General Description], [Abstract], [Plant Conditions], [Description of Event], [Inoperable SSCs], [Major Occurrence Time], [Secondary Functions], [Method of Discovery], [Operator Actions], [Safety System Responses], [Immediate Cause], [Root Cause], [Analysis of Event], [Contributing Factors], [Assessment of Safety Consequences], [Immediate Corrective Actions], [Corrective Actions to Prevent Recurrence], [Previous LERs], [Additional Information], [SSFF Consideration], [LNHR Consideration], [Commitments], [Lessons Learned], [Cause], [System], [Component], [Reportable to EPIX], [Manufacturer] FROM LER_analog WHERE ID =" & getID
Debug.Print strSQL
CurrentDb.Execute strSQL

That won't fix anything, but it will print the finished SQL string out to the VBA Immediate window. You can examine it there and perhaps spot the problem, or you can copy/paste the SQL into a blank query and try to run it. You'll often get a more descriptive error there. If that doesn't work, post the SQL here and we'll take a look at it.

 

[hoda64]

Thanks for that suggestion, it helped me pinpoint the problem. But now I have another problem. I entered the sql statement into the "sql" tab of a blank query. When I ran the query, it was have trouble finding the fields: , [Cause], [System], [Component], [Reportable to EPIX], [Manufacturer]

Turns out those are actually in another table! (A big DUH moment for me) There is a linked subform to the main form. Those fields are in the subform's table. I am going to try to figure out how to deal with it.

Thanks so much for you help, and sorry for any waste of time!

 

[pbaldy]

DUH moments are common around here. I have a big flat spot on my forehead from banging it into the desk during DUH moments.

 

[hoda64]

hahahahahaha! as long as we figure it out i suppose.