Solved Runtime error '3061' too few parameters .expected 1

[mansied]

Hello
I have this small VBA code which is appending data from one table( that is selected in the list ) to other table with the same fields and name.

Private Sub Command2_Click()
Dim sVal$

sVal = Me.Combo0
sVal = "INSERT INTO OSFinal " & _
"( HDR_OPR,AID_AMC,AID_AIN,BOEV_IODBOEV_BOTM,BOEV_BOTM,BOEV_TMT,BOEV_TMD,DHRS,BOEV_BOSU,ECL, " & _
" PREV,BOEV_REP,EVCT,EVCD,BOEV_ATA,BOEV_BATA,EVT_DCT,EVT_MNT,MULTI,FILE_KEY,BOEV_OOI, " & _
" BOEV_BFOD,BOEV_BOSN,BOEV_BFRY,BOEV_MNC,BOEV_BTSH,BOEV_FLT,BOEV_REM,LNK_OEI,TASK_ID,GUARANTEE_CHARGEABILITY_INDICATOR, " & _
" CHARGEABILITY_CODE,TASK_TYPE_CODE,JIC_CODE,REPAIR_STATION_NAME,[NOTE],Num ) " & _
"SELECT HDR_OPR,AID_AMC,AID_AIN,BOEV_IODBOEV_BOTM,BOEV_BOTM,BOEV_TMT,BOEV_TMD,DHRS,BOEV_BOSU,ECL, " & _
" PREV,BOEV_REP,EVCT,EVCD,BOEV_ATA,BOEV_BATA,EVT_DCT,EVT_MNT,MULTI,FILE_KEY,BOEV_OOI, " & _
" BOEV_BFOD,BOEV_BOSN,BOEV_BFRY,BOEV_MNC,BOEV_BTSH,BOEV_FLT,BOEV_REM,LNK_OEI,TASK_ID,GUARANTEE_CHARGEABILITY_INDICATOR, " & _
" CHARGEABILITY_CODE,TASK_TYPE_CODE,JIC_CODE,REPAIR_STATION_NAME,[NOTE],Num " & _
"FROM " & sVal

CurrentDb.Execute sVal

End Sub

I have This error

I have copied each field one by one and the same name but...
Do you see any problem here?
Thank you in advance for your help

 

[The_Doc_Man]

In this context, what is in the bound column of Me.Combo0 ??? Because unless the bound column of Combo0 resolves to the name of a table or query (which ALSO implies that a selection has been made or a default value exists), you have a FROM clause seeking to append to an arbitrary value that, for all we can tell from the context, could even be a number. But syntactically, it HAS to be the name of a table or non-action query.

Then, there is the implications of the semantics ... that you could have multiple tables with identical structures including identical field names, all of which can be selected based on a combo box. Which screams "non-normalized structure."

Try to tell us in WORDS, not code, what it is you are trying to do.

 

[Gasman]

Hello
I have this small VBA code which is appending data from one table( that is selected in the list ) to other table with the same fields and name.

Private Sub Command2_Click()
Dim sVal$

sVal = Me.Combo0
sVal = "INSERT INTO OSFinal " & _
"( HDR_OPR,AID_AMC,AID_AIN,BOEV_IODBOEV_BOTM,BOEV_BOTM,BOEV_TMT,BOEV_TMD,DHRS,BOEV_BOSU,ECL, " & _
" PREV,BOEV_REP,EVCT,EVCD,BOEV_ATA,BOEV_BATA,EVT_DCT,EVT_MNT,MULTI,FILE_KEY,BOEV_OOI, " & _
" BOEV_BFOD,BOEV_BOSN,BOEV_BFRY,BOEV_MNC,BOEV_BTSH,BOEV_FLT,BOEV_REM,LNK_OEI,TASK_ID,GUARANTEE_CHARGEABILITY_INDICATOR, " & _
" CHARGEABILITY_CODE,TASK_TYPE_CODE,JIC_CODE,REPAIR_STATION_NAME,[NOTE],Num ) " & _
"SELECT HDR_OPR,AID_AMC,AID_AIN,BOEV_IODBOEV_BOTM,BOEV_BOTM,BOEV_TMT,BOEV_TMD,DHRS,BOEV_BOSU,ECL, " & _
" PREV,BOEV_REP,EVCT,EVCD,BOEV_ATA,BOEV_BATA,EVT_DCT,EVT_MNT,MULTI,FILE_KEY,BOEV_OOI, " & _
" BOEV_BFOD,BOEV_BOSN,BOEV_BFRY,BOEV_MNC,BOEV_BTSH,BOEV_FLT,BOEV_REM,LNK_OEI,TASK_ID,GUARANTEE_CHARGEABILITY_INDICATOR, " & _
" CHARGEABILITY_CODE,TASK_TYPE_CODE,JIC_CODE,REPAIR_STATION_NAME,[NOTE],Num " & _
"FROM " & sVal

CurrentDb.Execute sVal

End Sub

I have This error
View attachment 95445

I have copied each field one by one and the same name but...
Do you see any problem here?
Thank you in advance for your help

 

[Gasman]

Sval appears to have to be 3 different objects All at the same time???
A string for a value, a string for an insert sql and an object to extract from???

 

[theDBguy]

Take a look at this generic function from Leigh Purvis.

http://www.accessmpv.com/thedbguy/codes.php?title=execqry

 

[pbaldy]

Here's a common debugging method:

Debugging

 

[mansied]

Here's a common debugging method:

Debugging

Thank you ..I ll test it now to see

 

[mansied]

Take a look at this generic function from Leigh Purvis.

http://www.accessmpv.com/thedbguy/codes.php?title=execqry

Thanks
I cannot open the link

 

[Gasman]

Code Snippets

 

[theDBguy]

Thanks
I cannot open the link

Sorry about that. Try this one.

Code Snippets

 

[mansied]

In this context, what is in the bound column of Me.Combo0 ??? Because unless the bound column of Combo0 resolves to the name of a table or query (which ALSO implies that a selection has been made or a default value exists), you have a FROM clause seeking to append to an arbitrary value that, for all we can tell from the context, could even be a number. But syntactically, it HAS to be the name of a table or non-action query.

Then, there is the implications of the semantics ... that you could have multiple tables with identical structures including identical field names, all of which can be selected based on a combo box. Which screams "non-normalized structure."

Try to tell us in WORDS, not code, what it is you are trying to do.

I have a drop list in my from which shows a list of tables .when user chooses one by clicking append wants to append data to a main table(OSFinal)
sval refers to the name of selected table.

 

[mansied]

Sval appears to have to be 3 different objects All at the same time???
A string for a value, a string for an insert sql and an object to extract from???

I am confused .In Fact , Sval is the name of table which is chosen in a drop list and wants to append its data to a main table .
shouldn't it be a string ? the name of the selected table ?

 

[pbaldy]

I think the way you're using sVal is okay, as in it will work, but it's unusual. I'd probably do something like:

sVal = "INSERT INTO OSFinal " & _
...
"FROM " & Me.Combo0

 

[Gasman]

Well debug.print sVal and post back the result.?
Just looked weird to me, first it is a combo value, then a sql string where that sval is appended to?

 

[theDBguy]

Hi. Just curious, did the new link work for you?

 

[mansied]

Hi. Just curious, did the new link work for you?

yes it worked .thanks

 

[theDBguy]

yes it worked .thanks

And were you able to try it out?

 

[mansied]

Well debug.print sVal and post back the result.?
Just looked weird to me, first it is a combo value, then a sql string where that sval is appended to?

This is the sample database .it seems work here ,but when I add the code to my file which has the same structure tables ,I have a syntax error (3131)????
why ????? same code ,same tables??? I lost ....

 

[mansied]

And were you able to try it out?

no, Unfortunately . I have to run my project and stuck to this little part ...

 

[pbaldy]

Did you try the Debug.Print method? If so, what's the SQL of the failing query? You don't by chance have spaces in table names do you? If so you'll need to surround the table name in square brackets.:

"FROM [" & sVal & "]"