Why my code is not work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I copy code from query, It work normal.

INSERT INTO A
SELECT TX.*
FROM TX
WHERE ((("maintid")=[forms]![compareresult].[maintid1]));

After that I try to copy code to form [compareresult] on command_click event.
It does not works Why? Can you help me please.
 
What did you copy to the Command_Click event?

If all you copied was that SQL statement, no wonder it didn't work. You need
to tell Access to run the query:

Dim strSQL As String

strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE (((""maintid"")=[forms]![compareresult].[maintid1]))"

CurrentDb.Execute strSQL, dbFailOnError

Of course, I'm not sure what that SQL supposed to be doing. It's comparing
the literal string maintid to whatever's in the control named maintid1 on
form compareresult. If that control contains the text "maintid", it's going
to put everything from table TX into table A. If that control doesn't
contain the text "maintid", it's not going to put anything in table A.

I suspect you want to select those rows in table TX where field maintid is
equal to what's in the control on the form. That means your SQL should be:

strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE maintid=" & [forms]![compareresult].[maintid1]

assuming maintid is a numeric field, or the following if it's text:


strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE maintid=" & Chr(34) & [forms]![compareresult].[maintid1] &
Chr(34)
 
I try to follow your code but I receive error code 3061 "Too few parameters.
exepect 1.

I confirm, in query it works normal but I would like to insert many
condition to sql statement, that is reason why I need to write code on form
(I have many procedure on my forms but all of sql statement they just
retrieve data from tables which end procedure by
Me!subformname.form.recordsource=Strsql)

Please help me again. What should I do ?. I see help but it does not show
example about dBfilaOnerror.

"Douglas J Steele" เขียน:
What did you copy to the Command_Click event?

If all you copied was that SQL statement, no wonder it didn't work. You need
to tell Access to run the query:

Dim strSQL As String

strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE (((""maintid"")=[forms]![compareresult].[maintid1]))"

CurrentDb.Execute strSQL, dbFailOnError

Of course, I'm not sure what that SQL supposed to be doing. It's comparing
the literal string maintid to whatever's in the control named maintid1 on
form compareresult. If that control contains the text "maintid", it's going
to put everything from table TX into table A. If that control doesn't
contain the text "maintid", it's not going to put anything in table A.

I suspect you want to select those rows in table TX where field maintid is
equal to what's in the control on the form. That means your SQL should be:

strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE maintid=" & [forms]![compareresult].[maintid1]

assuming maintid is a numeric field, or the following if it's text:


strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE maintid=" & Chr(34) & [forms]![compareresult].[maintid1] &
Chr(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Nova said:
I copy code from query, It work normal.

INSERT INTO A
SELECT TX.*
FROM TX
WHERE ((("maintid")=[forms]![compareresult].[maintid1]));

After that I try to copy code to form [compareresult] on command_click event.
It does not works Why? Can you help me please.
Click to expand...
Click to expand...
 
What's the exact code you tried (and what data type is maintid)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Nova said:
I try to follow your code but I receive error code 3061 "Too few parameters.
exepect 1.

I confirm, in query it works normal but I would like to insert many
condition to sql statement, that is reason why I need to write code on form
(I have many procedure on my forms but all of sql statement they just
retrieve data from tables which end procedure by
Me!subformname.form.recordsource=Strsql)

Please help me again. What should I do ?. I see help but it does not show
example about dBfilaOnerror.

"Douglas J Steele" ?????:
What did you copy to the Command_Click event?

If all you copied was that SQL statement, no wonder it didn't work. You need
to tell Access to run the query:

Dim strSQL As String

strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE (((""maintid"")=[forms]![compareresult].[maintid1]))"

CurrentDb.Execute strSQL, dbFailOnError

Of course, I'm not sure what that SQL supposed to be doing. It's comparing
the literal string maintid to whatever's in the control named maintid1 on
form compareresult. If that control contains the text "maintid", it's going
to put everything from table TX into table A. If that control doesn't
contain the text "maintid", it's not going to put anything in table A.

I suspect you want to select those rows in table TX where field maintid is
equal to what's in the control on the form. That means your SQL should be:

strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE maintid=" & [forms]![compareresult].[maintid1]

assuming maintid is a numeric field, or the following if it's text:


strSQL = "INSERT INTO A " & _
"SELECT TX.* " & _
"FROM TX " & _
"WHERE maintid=" & Chr(34) & [forms]![compareresult].[maintid1] &
Chr(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Nova said:
I copy code from query, It work normal.

INSERT INTO A
SELECT TX.*
FROM TX
WHERE ((("maintid")=[forms]![compareresult].[maintid1]));

After that I try to copy code to form [compareresult] on command_click event.
It does not works Why? Can you help me please.
Click to expand...
Click to expand...
Click to expand...
 
I try many times but it still not work and send me an error message " Run
time error 3061 Too few parameters. Expected 1" Finally I try to use
docmd.Runsql,It works I don't understand why?

PLease see my form code below

Private Sub Text1_AfterUpdate()
Dim strSQL As String
strSQL = "INSERT INTO CATIS ( MaintID, AssetNo )" & _
"SELECT CATIS1.MaintID,CATIS1.AssetNo " & _
"FROM CATIS1 " & _
"WHERE (((CATIS1.MaintID)=[forms]![FormX].[Text1]));"
CurrentDb.Execute strSQL, dbFailOnError
'DoCmd.RunSQL strSQL
End Sub

If I use Current....... It send me error message like upper
If I use Docmd.RunSQL..... It works
Now I can use Docmd..... But If you don't remind, Could you explain me why
your code does not work, It will be my new knowledge. Thank you very much.
 
Basically,
DoCmd RunSQL is able to use the Access(Jet) interpretation to get the value
of Forms!FormX!Text1. CurrentDb.Executer doesn't have the Jet Expression
Service available to it. You can fix the problem by concatening the VALUE
of Forms!FormX!Text1 to your SQl string instead of the reference to the
control.

Private Sub Text1_AfterUpdate()
Dim strSQL As String
strSQL = "INSERT INTO CATIS ( MaintID, AssetNo )" & _
"SELECT CATIS1.MaintID,CATIS1.AssetNo " & _
"FROM CATIS1 " & _
"WHERE (((CATIS1.MaintID)=""" & [forms]![FormX]![Text1] & """));"
CurrentDb.Execute strSQL, dbFailOnError
'DoCmd.RunSQL strSQL
End Sub

If the field MaintID is a number field then turn the triple quote marks into
single quote marks. The triple quote marks will surround the value of the
control with a quote mark in the string.
 
Thankyou Very much John
It works
I don't know about any jat engine because I never seen the book (my country
language) in my country before, That why it very difficult for me but easy
for you. Thankyou
 
Back
Top