Solved Where Is Syntax Error In My Code - Run-time Error 3075 (1 Viewer)

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
Hello,
For some reason, I would like to change company name (in 2 languages) and CR (Certificated of Registraton) in tble T_JobOffer with condtion if a particular CNo (candidate number) is what is at present on the form.

In compilation no error found but at execution it produces Error 3075.

Code:
Dim strSQL As String
strSQL = "INSERT INTO T_JobOffer " & _
"(CECompany, CACompany, CCrNumber) VALUES(" & Me!CboSelectNewCo.Column(0) & ", " & Me!CboSelectNewCo.Column(1) & ", " & Me!CboSelectNewCo.Column(2) & _
" Where (T_JobOffer.CNO)= " & Forms!AA!CNo & ")"
DoCmd.RunSQL strSQL

Please help.
 

oleronesoftwares

Passionate Learner
Local time
Today, 04:19
Joined
Sep 22, 2014
Messages
1,159
It is likely there is a missing operator in the query, check the query or copy and paste in query design view to run it.
 

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
It is vba code which placed onclick event of a command btn on a continues form. I want to shift selected employees from that continues form and and store in relevent tble T_JobOffer.

How to copy it in query n run?
 

oleronesoftwares

Passionate Learner
Local time
Today, 04:19
Joined
Sep 22, 2014
Messages
1,159
I want to shift selected employees from that continues form and and store in relevent tble T_JobOffer.
By shift, do you mean move/delete from the continuous forms data source and insert into tble T_jobOffer?
 

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
By shift, do you mean move/delete from the continuous forms data source and insert into tble T_jobOffer?
The Continuous form is just to bring the data on screen and from there it will shift to tble T_JobOffer.

The data source of this continuous form is a query that is based on tble T_JobOffer.
 

Attachments

  • AA.jpg
    AA.jpg
    68.3 KB · Views: 261

oleronesoftwares

Passionate Learner
Local time
Today, 04:19
Joined
Sep 22, 2014
Messages
1,159
The Continuous form is just to bring the data on screen and from there it will shift to tble T_JobOffer.

The data source of this continuous form is a query that is based on tble T_JobOffer.
Am a bit lost here, the continous form's record source is a query based on table T_JobOffer, which means its still getting data from table T_JobOffer, so how do u move data from same table to same table?
 

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
Am a bit lost here, the continous form's record source is a query based on table T_JobOffer, which means its still getting data from table T_JobOffer, so how do u move data from same table to same table?
Yes, you are correct but the limited data appearing on this continuous form is thru query and I am just updating 3 fields data in the tble where the CNo number is same as that appears on the form. I am replacing name of company and its CR number of the same record. Isnt it possible?

Or it would be better to call the data in unbound text boxes along with CNo and then replace the company name etc ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:19
Joined
May 7, 2009
Messages
19,169
i think you misplaced the last ")" in your query string:
Code:
Dim strSQL As String
strSQL = "INSERT INTO T_JobOffer " & _
"(CECompany, CACompany, CCrNumber) VALUES(" & Me!CboSelectNewCo.Column(0) & ", " & Me!CboSelectNewCo.Column(1) & ", " & Me!CboSelectNewCo.Column(2) & ") " & _
"Where (T_JobOffer.CNO)= " & Forms!AA!CNo
DoCmd.RunSQL strSQL
 

oleronesoftwares

Passionate Learner
Local time
Today, 04:19
Joined
Sep 22, 2014
Messages
1,159
Please share the following syntax

1. The select query
2. The update query
 

ysdai

Member
Local time
Today, 04:19
Joined
Nov 28, 2019
Messages
46
Just a guess, but if CECompany and CACompany are string types, you need to enclose them in single quotes, like this (in red):

VALUES('" & Me!CboSelectNewCo.Column(0) & "', '" & Me!CboSelectNewCo.Column(1) & "', "
 

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
i think you misplaced the last ")" in your query string:
Code:
Dim strSQL As String
strSQL = "INSERT INTO T_JobOffer " & _
"(CECompany, CACompany, CCrNumber) VALUES(" & Me!CboSelectNewCo.Column(0) & ", " & Me!CboSelectNewCo.Column(1) & ", " & Me!CboSelectNewCo.Column(2) & ") " & _
"Where (T_JobOffer.CNO)= " & Forms!AA!CNo
DoCmd.RunSQL strSQL
Still same error
 

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
Just a guess, but if CECompany and CACompany are string types, you need to enclose them in single quotes, like this (in red):

VALUES('" & Me!CboSelectNewCo.Column(0) & "', '" & Me!CboSelectNewCo.Column(1) & "', "
Yes these both are string type. and CR number is number.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:19
Joined
May 7, 2009
Messages
19,169
you can use Querydef to insert:
Code:
With Currentdb.CreateQuerydef("", "INSERT INTO T_JobOffer " & _
"(CECompany, CACompany, CCrNumber) VALUES(p1, p2, p3) Where (T_JobOffer.CNO)=p4)")
    .Parameters(0)=Me!CboSelectNewCo.Column(0)
    .Parameters(1)=Me!CboSelectNewCo.Column(1)
    .Parameters(2)=Me!CboSelectNewCo.Column(2) 
    .Parameters(3)=Forms!AA!CNo
    .Execute
End With
 

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
Also I like to know if my process is correct or not. Bcz as raised by oleronesoftwares, should I bring the data in unbound form first and then replace few fields?

Please advise
 

ysdai

Member
Local time
Today, 04:19
Joined
Nov 28, 2019
Messages
46
Yes these both are string type. and CR number is number.
Then you need to apply the single quotes (') around string data types. I didn't see them in your original code.
You can do a Debug.Print or Msgbox the strSQL to double check before execute. I make this kind of careless mistakes very often.
 

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
you can use Querydef to insert:
Code:
With Currentdb.CreateQuerydef("", "INSERT INTO T_JobOffer " & _
"(CECompany, CACompany, CCrNumber) VALUES(p1, p2, p3) Where (T_JobOffer.CNO)=p4)")
    .Parameters(0)=Me!CboSelectNewCo.Column(0)
    .Parameters(1)=Me!CboSelectNewCo.Column(1)
    .Parameters(2)=Me!CboSelectNewCo.Column(2)
    .Parameters(3)=Forms!AA!CNo
    .Execute
End With
 

Attachments

  • Error.jpg
    Error.jpg
    15.4 KB · Views: 306

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:19
Joined
May 7, 2009
Messages
19,169
sorry, you cannot Insert with Criteria, you can only do this in Update Query.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:19
Joined
Sep 21, 2011
Messages
14,038
You can do a Debug.Print or Msgbox the strSQL to double check before execute. I make this kind of careless mistakes very often.
With the O/P having over 800 posts, you'd hope they knew that by now? :(
 

Ashfaque

Student
Local time
Today, 16:49
Joined
Sep 6, 2004
Messages
894
I tried changing code with UPDATE query....

Code:
Dim strSQL As String
strSQL = "UPDATE T_JobOffer " & _
"SET [CECompany]= '" & Forms!F_SaudizationPercent!CECompany & "', [CACompany]='" & Forms!F_SaudizationPercent!CACompany & "', [CCrNumber]='" & Forms!F_SaudizationPercent!CACompany & "', " & Forms!F_SaudizationPercent!CCrNumber & ") " & _
"Where (T_JobOffer.CNO)= " & Forms!F_SaudizationPercent!CNo
DoCmd.RunSQL strSQL

It produced now Runtime error 3144...Syntax error in UPDATE statement....
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:19
Joined
May 7, 2009
Messages
19,169
remove the Last ")" and replace it with just " " (space).
Code:
strSQL = "UPDATE T_JobOffer " & _
"SET [CECompany]= '" & Forms!F_SaudizationPercent!CECompany & "', [CACompany]='" & Forms!F_SaudizationPercent!CACompany & "', [CCrNumber]='" & Forms!F_SaudizationPercent!CCrNumber & "' " & _
"Where (T_JobOffer.CNO)= " & Forms!F_SaudizationPercent!CNo
 

Users who are viewing this thread

Top Bottom