Access code error - Too few parametres. Expect 1.

  • Thread starter Thread starter Guest
  • Start date Start date
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!!!
 
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
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top