Access code error - Too few parametres. Expect 1.

G

Guest

Hello,

I have a VBA code for clicking a button called "Duplicate Record" and it is
as follows:

Private Sub command17_click()
On Error GoTo Err_Command17_Click
Dim NewAssemblyPartID As String

'Method 3: use DAO Object and two append queries
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef

Set db = CurrentDb

' clone the AssemblyPart table first
strSQL = "INSERT INTO [AssemblyParts] Select
AssemblyPartID,Name,Description,InputDate,CompleteBy,Notes,Lock FROM
[AssemblyParts]" & " WHERE [AssemblyPartID] = " & Me![AssemblyPartID] & ";"

' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)

' and run it; any errors will jump to err_command17_click
qd.Execute dbFailOnError
Set qd = Nothing

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

I always get error message when I click the button in form view, "Too few
parametres. Expect 1." And I am not very knowledgable on VBA coding and I am
still in the process of learning. However this error drove me crazy. I have
no clues which line has too few parametres... Any advice will be appreciated!
Thanks for your time!!!
 
N

Nikos Yannacopoulos

Jessica,

Kind of an overkill... you don't need to mess with querydefs. Try this:

Dim strSQL As String
strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name,Description,InputDate,CompleteBy,Notes,Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = " _
& Me![AssemblyPartID]
Currentdb.Execute strSQL, dbFailOnError

Note: your syntax assumes field AssemblyPartID is numeric; otherwise,
change the strSQL expression to:

strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"

Also, check to make sure the fields and controls' names are accurate.
Where does the Dim NewAssemblyPartID As String apply in your original code?

HTH,
Nikos
 
G

Guest

Nikos,

I could not thank you enough for modifying my code and made it work! And it
is geting closer to the destination!!!

I put "Dim NewAssemblyPartID As String " in the code becuase
"AssemblyPartID"(text) is the primary key of the table "AssemblyParts". And I
want to prompt a window to input a new AssemblyPartID before the insert into
happening because the primary key is not allow to be duplicate in the
database(actually I got this kind of error messages after the code is
modifies as you replied and the duplicated record can not be inserted
successfully even not allowing me to view/modify it, it looks like the SQL
statement "Insert Into..." will try to save the duplicated record
automatically into the database and of course it conflicting...) So that I
tried to modify the code like the follows:

...
NewAssemblyPartID = InputBox("input the new Assembly Part Number", _
"Input New Assembly Part Number")
strSQL = "INSERT INTO [AssemblyParts] Select " _
& NewAssemblyPartID & "AS AssemblyPartID" _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
CurrentDb.Execute strSQL, dbFailOnError
...

Unfortunatelly I got error like:
Syntax error(missing operator) in query expression 'testAS
AssemblyPartIDName'.
(test is the value I input in the Inputbox)

It looks like I made a stupid thing but I got stuck with it. Can you help?
By the way, is there any other alternative way to allow me to view/modify the
newly duplicated record after the query is excuted? Then I may not need the
inputbox to prompted than direct modifying it , thanks a lot, a lot....


Nikos Yannacopoulos said:
Jessica,

Kind of an overkill... you don't need to mess with querydefs. Try this:

Dim strSQL As String
strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name,Description,InputDate,CompleteBy,Notes,Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = " _
& Me![AssemblyPartID]
Currentdb.Execute strSQL, dbFailOnError

Note: your syntax assumes field AssemblyPartID is numeric; otherwise,
change the strSQL expression to:

strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"

Also, check to make sure the fields and controls' names are accurate.
Where does the Dim NewAssemblyPartID As String apply in your original code?

HTH,
Nikos

Hello,

I have a VBA code for clicking a button called "Duplicate Record" and it is
as follows:

Private Sub command17_click()
On Error GoTo Err_Command17_Click
Dim NewAssemblyPartID As String

'Method 3: use DAO Object and two append queries
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef

Set db = CurrentDb

' clone the AssemblyPart table first
strSQL = "INSERT INTO [AssemblyParts] Select
AssemblyPartID,Name,Description,InputDate,CompleteBy,Notes,Lock FROM
[AssemblyParts]" & " WHERE [AssemblyPartID] = " & Me![AssemblyPartID] & ";"

' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)

' and run it; any errors will jump to err_command17_click
qd.Execute dbFailOnError
Set qd = Nothing

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

I always get error message when I click the button in form view, "Too few
parametres. Expect 1." And I am not very knowledgable on VBA coding and I am
still in the process of learning. However this error drove me crazy. I have
no clues which line has too few parametres... Any advice will be appreciated!
Thanks for your time!!!
Click to expand...
Click to expand...
 
G

Guest

I am so sorry, I think I need put things like:
.....
strSQL = "INSERT INTO [AssemblyParts] Select " _
& "'" & NewAssemblyPartID & "'" & "AS AssemblyPartID," _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
.....

Then it works. Nikos, can you follow up my previous question regarding the
alternative solution? Thanks for your time!


jessica said:
Nikos,

I could not thank you enough for modifying my code and made it work! And it
is geting closer to the destination!!!

I put "Dim NewAssemblyPartID As String " in the code becuase
"AssemblyPartID"(text) is the primary key of the table "AssemblyParts". And I
want to prompt a window to input a new AssemblyPartID before the insert into
happening because the primary key is not allow to be duplicate in the
database(actually I got this kind of error messages after the code is
modifies as you replied and the duplicated record can not be inserted
successfully even not allowing me to view/modify it, it looks like the SQL
statement "Insert Into..." will try to save the duplicated record
automatically into the database and of course it conflicting...) So that I
tried to modify the code like the follows:

...
NewAssemblyPartID = InputBox("input the new Assembly Part Number", _
"Input New Assembly Part Number")
strSQL = "INSERT INTO [AssemblyParts] Select " _
& NewAssemblyPartID & "AS AssemblyPartID" _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
CurrentDb.Execute strSQL, dbFailOnError
...

Unfortunatelly I got error like:
Syntax error(missing operator) in query expression 'testAS
AssemblyPartIDName'.
(test is the value I input in the Inputbox)

It looks like I made a stupid thing but I got stuck with it. Can you help?
By the way, is there any other alternative way to allow me to view/modify the
newly duplicated record after the query is excuted? Then I may not need the
inputbox to prompted than direct modifying it , thanks a lot, a lot....


Nikos Yannacopoulos said:
Jessica,

Kind of an overkill... you don't need to mess with querydefs. Try this:

Dim strSQL As String
strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name,Description,InputDate,CompleteBy,Notes,Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = " _
& Me![AssemblyPartID]
Currentdb.Execute strSQL, dbFailOnError

Note: your syntax assumes field AssemblyPartID is numeric; otherwise,
change the strSQL expression to:

strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"

Also, check to make sure the fields and controls' names are accurate.
Where does the Dim NewAssemblyPartID As String apply in your original code?

HTH,
Nikos

Hello,

I have a VBA code for clicking a button called "Duplicate Record" and it is
as follows:

Private Sub command17_click()
On Error GoTo Err_Command17_Click
Dim NewAssemblyPartID As String

'Method 3: use DAO Object and two append queries
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef

Set db = CurrentDb

' clone the AssemblyPart table first
strSQL = "INSERT INTO [AssemblyParts] Select
AssemblyPartID,Name,Description,InputDate,CompleteBy,Notes,Lock FROM
[AssemblyParts]" & " WHERE [AssemblyPartID] = " & Me![AssemblyPartID] & ";"

' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)

' and run it; any errors will jump to err_command17_click
qd.Execute dbFailOnError
Set qd = Nothing

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

I always get error message when I click the button in form view, "Too few
parametres. Expect 1." And I am not very knowledgable on VBA coding and I am
still in the process of learning. However this error drove me crazy. I have
no clues which line has too few parametres... Any advice will be appreciated!
Thanks for your time!!!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I got another problem on the form, after I input the new AssemblyPartID in
the input window. It looks like that the new record has been inserted but the
form does not get refreshed and I was not brought to the new inserted record
of course. I input the Me.requery at the end of the code and the new showed
up finally via find it. But I want to be in the new inserted record
automatically after the "Insert into" happened becuase I need to modify some
fields before the record is saved. How can I achieve that?

I am thinking maybe "Select newassemblypartid as assemblypartid" is wrong
way to go and cause the problem. Any clues? Your help is appreciated again!

jessica said:
I am so sorry, I think I need put things like:
....
strSQL = "INSERT INTO [AssemblyParts] Select " _
& "'" & NewAssemblyPartID & "'" & "AS AssemblyPartID," _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
....

Then it works. Nikos, can you follow up my previous question regarding the
alternative solution? Thanks for your time!


jessica said:
Nikos,

I could not thank you enough for modifying my code and made it work! And it
is geting closer to the destination!!!

I put "Dim NewAssemblyPartID As String " in the code becuase
"AssemblyPartID"(text) is the primary key of the table "AssemblyParts". And I
want to prompt a window to input a new AssemblyPartID before the insert into
happening because the primary key is not allow to be duplicate in the
database(actually I got this kind of error messages after the code is
modifies as you replied and the duplicated record can not be inserted
successfully even not allowing me to view/modify it, it looks like the SQL
statement "Insert Into..." will try to save the duplicated record
automatically into the database and of course it conflicting...) So that I
tried to modify the code like the follows:

...
NewAssemblyPartID = InputBox("input the new Assembly Part Number", _
"Input New Assembly Part Number")
strSQL = "INSERT INTO [AssemblyParts] Select " _
& NewAssemblyPartID & "AS AssemblyPartID" _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
CurrentDb.Execute strSQL, dbFailOnError
...

Unfortunatelly I got error like:
Syntax error(missing operator) in query expression 'testAS
AssemblyPartIDName'.
(test is the value I input in the Inputbox)

It looks like I made a stupid thing but I got stuck with it. Can you help?
By the way, is there any other alternative way to allow me to view/modify the
newly duplicated record after the query is excuted? Then I may not need the
inputbox to prompted than direct modifying it , thanks a lot, a lot....


Nikos Yannacopoulos said:
Jessica,

Kind of an overkill... you don't need to mess with querydefs. Try this:

Dim strSQL As String
strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name,Description,InputDate,CompleteBy,Notes,Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = " _
& Me![AssemblyPartID]
Currentdb.Execute strSQL, dbFailOnError

Note: your syntax assumes field AssemblyPartID is numeric; otherwise,
change the strSQL expression to:

strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"

Also, check to make sure the fields and controls' names are accurate.
Where does the Dim NewAssemblyPartID As String apply in your original code?

HTH,
Nikos


jessica wrote:
Hello,

I have a VBA code for clicking a button called "Duplicate Record" and it is
as follows:

Private Sub command17_click()
On Error GoTo Err_Command17_Click
Dim NewAssemblyPartID As String

'Method 3: use DAO Object and two append queries
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef

Set db = CurrentDb

' clone the AssemblyPart table first
strSQL = "INSERT INTO [AssemblyParts] Select
AssemblyPartID,Name,Description,InputDate,CompleteBy,Notes,Lock FROM
[AssemblyParts]" & " WHERE [AssemblyPartID] = " & Me![AssemblyPartID] & ";"

' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)

' and run it; any errors will jump to err_command17_click
qd.Execute dbFailOnError
Set qd = Nothing

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

I always get error message when I click the button in form view, "Too few
parametres. Expect 1." And I am not very knowledgable on VBA coding and I am
still in the process of learning. However this error drove me crazy. I have
no clues which line has too few parametres... Any advice will be appreciated!
Thanks for your time!!!
Click to expand...
Click to expand...
Click to expand...
 
N

Nikos Yannacopoulos

Jessica,

Is this code in the form's own module? If yes, add the following after
the CurrentDb.Execute:

Me.Requery
Me.txtAssemblyPartID.SetFocus
DoCmd.FindRecord NewAssemblyPartID

where I have assumed the control on the form to be named
txtAssemblyPartID; change as appropriate.

HTH,
Nikos
I got another problem on the form, after I input the new AssemblyPartID in
the input window. It looks like that the new record has been inserted but the
form does not get refreshed and I was not brought to the new inserted record
of course. I input the Me.requery at the end of the code and the new showed
up finally via find it. But I want to be in the new inserted record
automatically after the "Insert into" happened becuase I need to modify some
fields before the record is saved. How can I achieve that?

I am thinking maybe "Select newassemblypartid as assemblypartid" is wrong
way to go and cause the problem. Any clues? Your help is appreciated again!

:

I am so sorry, I think I need put things like:
....
strSQL = "INSERT INTO [AssemblyParts] Select " _
& "'" & NewAssemblyPartID & "'" & "AS AssemblyPartID," _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
....

Then it works. Nikos, can you follow up my previous question regarding the
alternative solution? Thanks for your time!


:

Nikos,

I could not thank you enough for modifying my code and made it work! And it
is geting closer to the destination!!!

I put "Dim NewAssemblyPartID As String " in the code becuase
"AssemblyPartID"(text) is the primary key of the table "AssemblyParts". And I
want to prompt a window to input a new AssemblyPartID before the insert into
happening because the primary key is not allow to be duplicate in the
database(actually I got this kind of error messages after the code is
modifies as you replied and the duplicated record can not be inserted
successfully even not allowing me to view/modify it, it looks like the SQL
statement "Insert Into..." will try to save the duplicated record
automatically into the database and of course it conflicting...) So that I
tried to modify the code like the follows:

...
NewAssemblyPartID = InputBox("input the new Assembly Part Number", _
"Input New Assembly Part Number")
strSQL = "INSERT INTO [AssemblyParts] Select " _
& NewAssemblyPartID & "AS AssemblyPartID" _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
CurrentDb.Execute strSQL, dbFailOnError
...

Unfortunatelly I got error like:
Syntax error(missing operator) in query expression 'testAS
AssemblyPartIDName'.
(test is the value I input in the Inputbox)

It looks like I made a stupid thing but I got stuck with it. Can you help?
By the way, is there any other alternative way to allow me to view/modify the
newly duplicated record after the query is excuted? Then I may not need the
inputbox to prompted than direct modifying it , thanks a lot, a lot....


:


Jessica,

Kind of an overkill... you don't need to mess with querydefs. Try this:

Dim strSQL As String
strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name,Description,InputDate,CompleteBy,Notes,Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = " _
& Me![AssemblyPartID]
Currentdb.Execute strSQL, dbFailOnError

Note: your syntax assumes field AssemblyPartID is numeric; otherwise,
change the strSQL expression to:

strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"

Also, check to make sure the fields and controls' names are accurate.
Where does the Dim NewAssemblyPartID As String apply in your original code?

HTH,
Nikos


jessica wrote:

Hello,

I have a VBA code for clicking a button called "Duplicate Record" and it is
as follows:

Private Sub command17_click()
On Error GoTo Err_Command17_Click
Dim NewAssemblyPartID As String

'Method 3: use DAO Object and two append queries
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef

Set db = CurrentDb

' clone the AssemblyPart table first
strSQL = "INSERT INTO [AssemblyParts] Select
AssemblyPartID,Name,Description,InputDate,CompleteBy,Notes,Lock FROM
[AssemblyParts]" & " WHERE [AssemblyPartID] = " & Me![AssemblyPartID] & ";"

' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)

' and run it; any errors will jump to err_command17_click
qd.Execute dbFailOnError
Set qd = Nothing

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

I always get error message when I click the button in form view, "Too few
parametres. Expect 1." And I am not very knowledgable on VBA coding and I am
still in the process of learning. However this error drove me crazy. I have
no clues which line has too few parametres... Any advice will be appreciated!
Thanks for your time!!!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you so much! It works great!

Nikos Yannacopoulos said:
Jessica,

Is this code in the form's own module? If yes, add the following after
the CurrentDb.Execute:

Me.Requery
Me.txtAssemblyPartID.SetFocus
DoCmd.FindRecord NewAssemblyPartID

where I have assumed the control on the form to be named
txtAssemblyPartID; change as appropriate.

HTH,
Nikos
I got another problem on the form, after I input the new AssemblyPartID in
the input window. It looks like that the new record has been inserted but the
form does not get refreshed and I was not brought to the new inserted record
of course. I input the Me.requery at the end of the code and the new showed
up finally via find it. But I want to be in the new inserted record
automatically after the "Insert into" happened becuase I need to modify some
fields before the record is saved. How can I achieve that?

I am thinking maybe "Select newassemblypartid as assemblypartid" is wrong
way to go and cause the problem. Any clues? Your help is appreciated again!

:

I am so sorry, I think I need put things like:
....
strSQL = "INSERT INTO [AssemblyParts] Select " _
& "'" & NewAssemblyPartID & "'" & "AS AssemblyPartID," _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
....

Then it works. Nikos, can you follow up my previous question regarding the
alternative solution? Thanks for your time!


:


Nikos,

I could not thank you enough for modifying my code and made it work! And it
is geting closer to the destination!!!

I put "Dim NewAssemblyPartID As String " in the code becuase
"AssemblyPartID"(text) is the primary key of the table "AssemblyParts". And I
want to prompt a window to input a new AssemblyPartID before the insert into
happening because the primary key is not allow to be duplicate in the
database(actually I got this kind of error messages after the code is
modifies as you replied and the duplicated record can not be inserted
successfully even not allowing me to view/modify it, it looks like the SQL
statement "Insert Into..." will try to save the duplicated record
automatically into the database and of course it conflicting...) So that I
tried to modify the code like the follows:

...
NewAssemblyPartID = InputBox("input the new Assembly Part Number", _
"Input New Assembly Part Number")
strSQL = "INSERT INTO [AssemblyParts] Select " _
& NewAssemblyPartID & "AS AssemblyPartID" _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
CurrentDb.Execute strSQL, dbFailOnError
...

Unfortunatelly I got error like:
Syntax error(missing operator) in query expression 'testAS
AssemblyPartIDName'.
(test is the value I input in the Inputbox)

It looks like I made a stupid thing but I got stuck with it. Can you help?
By the way, is there any other alternative way to allow me to view/modify the
newly duplicated record after the query is excuted? Then I may not need the
inputbox to prompted than direct modifying it , thanks a lot, a lot....


:


Jessica,

Kind of an overkill... you don't need to mess with querydefs. Try this:

Dim strSQL As String
strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name,Description,InputDate,CompleteBy,Notes,Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = " _
& Me![AssemblyPartID]
Currentdb.Execute strSQL, dbFailOnError

Note: your syntax assumes field AssemblyPartID is numeric; otherwise,
change the strSQL expression to:

strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"

Also, check to make sure the fields and controls' names are accurate.
Where does the Dim NewAssemblyPartID As String apply in your original code?

HTH,
Nikos


jessica wrote:

Hello,

I have a VBA code for clicking a button called "Duplicate Record" and it is
as follows:

Private Sub command17_click()
On Error GoTo Err_Command17_Click
Dim NewAssemblyPartID As String

'Method 3: use DAO Object and two append queries
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef

Set db = CurrentDb

' clone the AssemblyPart table first
strSQL = "INSERT INTO [AssemblyParts] Select
AssemblyPartID,Name,Description,InputDate,CompleteBy,Notes,Lock FROM
[AssemblyParts]" & " WHERE [AssemblyPartID] = " & Me![AssemblyPartID] & ";"

' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)

' and run it; any errors will jump to err_command17_click
qd.Execute dbFailOnError
Set qd = Nothing

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

I always get error message when I click the button in form view, "Too few
parametres. Expect 1." And I am not very knowledgable on VBA coding and I am
still in the process of learning. However this error drove me crazy. I have
no clues which line has too few parametres... Any advice will be appreciated!
Thanks for your time!!!
Click to expand...
Click to expand...
Click to expand...
 
N

Nikos Yannacopoulos

Good!
Thank you so much! It works great!

:

Jessica,

Is this code in the form's own module? If yes, add the following after
the CurrentDb.Execute:

Me.Requery
Me.txtAssemblyPartID.SetFocus
DoCmd.FindRecord NewAssemblyPartID

where I have assumed the control on the form to be named
txtAssemblyPartID; change as appropriate.

HTH,
Nikos
I got another problem on the form, after I input the new AssemblyPartID in
the input window. It looks like that the new record has been inserted but the
form does not get refreshed and I was not brought to the new inserted record
of course. I input the Me.requery at the end of the code and the new showed
up finally via find it. But I want to be in the new inserted record
automatically after the "Insert into" happened becuase I need to modify some
fields before the record is saved. How can I achieve that?

I am thinking maybe "Select newassemblypartid as assemblypartid" is wrong
way to go and cause the problem. Any clues? Your help is appreciated again!

:



I am so sorry, I think I need put things like:
....
strSQL = "INSERT INTO [AssemblyParts] Select " _
& "'" & NewAssemblyPartID & "'" & "AS AssemblyPartID," _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
....

Then it works. Nikos, can you follow up my previous question regarding the
alternative solution? Thanks for your time!


:



Nikos,

I could not thank you enough for modifying my code and made it work! And it
is geting closer to the destination!!!

I put "Dim NewAssemblyPartID As String " in the code becuase
"AssemblyPartID"(text) is the primary key of the table "AssemblyParts". And I
want to prompt a window to input a new AssemblyPartID before the insert into
happening because the primary key is not allow to be duplicate in the
database(actually I got this kind of error messages after the code is
modifies as you replied and the duplicated record can not be inserted
successfully even not allowing me to view/modify it, it looks like the SQL
statement "Insert Into..." will try to save the duplicated record
automatically into the database and of course it conflicting...) So that I
tried to modify the code like the follows:

...
NewAssemblyPartID = InputBox("input the new Assembly Part Number", _
"Input New Assembly Part Number")
strSQL = "INSERT INTO [AssemblyParts] Select " _
& NewAssemblyPartID & "AS AssemblyPartID" _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"
CurrentDb.Execute strSQL, dbFailOnError
...

Unfortunatelly I got error like:
Syntax error(missing operator) in query expression 'testAS
AssemblyPartIDName'.
(test is the value I input in the Inputbox)

It looks like I made a stupid thing but I got stuck with it. Can you help?
By the way, is there any other alternative way to allow me to view/modify the
newly duplicated record after the query is excuted? Then I may not need the
inputbox to prompted than direct modifying it , thanks a lot, a lot....


:



Jessica,

Kind of an overkill... you don't need to mess with querydefs. Try this:

Dim strSQL As String
strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name,Description,InputDate,CompleteBy,Notes,Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = " _
& Me![AssemblyPartID]
Currentdb.Execute strSQL, dbFailOnError

Note: your syntax assumes field AssemblyPartID is numeric; otherwise,
change the strSQL expression to:

strSQL = "INSERT INTO [AssemblyParts] Select AssemblyPartID, " _
& "Name, Description,InputDate, CompleteBy, Notes, Lock FROM " _
& "[AssemblyParts] WHERE [AssemblyPartID] = '" _
& Me![AssemblyPartID] & "'"

Also, check to make sure the fields and controls' names are accurate.
Where does the Dim NewAssemblyPartID As String apply in your original code?

HTH,
Nikos


jessica wrote:


Hello,

I have a VBA code for clicking a button called "Duplicate Record" and it is
as follows:

Private Sub command17_click()
On Error GoTo Err_Command17_Click
Dim NewAssemblyPartID As String

'Method 3: use DAO Object and two append queries
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef

Set db = CurrentDb

' clone the AssemblyPart table first
strSQL = "INSERT INTO [AssemblyParts] Select
AssemblyPartID,Name,Description,InputDate,CompleteBy,Notes,Lock FROM
[AssemblyParts]" & " WHERE [AssemblyPartID] = " & Me![AssemblyPartID] & ";"

' Create an unnamed/unstored Query to run
Set qd = db.CreateQuerydef("", strSQL)

' and run it; any errors will jump to err_command17_click
qd.Execute dbFailOnError
Set qd = Nothing

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

I always get error message when I click the button in form view, "Too few
parametres. Expect 1." And I am not very knowledgable on VBA coding and I am
still in the process of learning. However this error drove me crazy. I have
no clues which line has too few parametres... Any advice will be appreciated!
Thanks for your time!!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

removing trailing returns 11
Error 3061 - too few parameters 2
vba 14
Something wrong with my code. 1
Run-time error "3061" Too few parameters. Expected 1. 4
SQL select statement issues 2
Too few parameters. Expected 1. 2
Run-time error '3265' 19

Top