Solved Where Is Syntax Error In My Code - Run-time Error 3075

[Ashfaque]

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.

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]

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]

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]

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]

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.

 

[oleronesoftwares]

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]

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]

i think you misplaced the last ")" in your query string:

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]

Please share the following syntax

1. The select query
2. The update query

 

[ysdai]

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]

i think you misplaced the last ")" in your query string:

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]

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]

you can use Querydef to insert:

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]

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]

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]

you can use Querydef to insert:

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

 

[arnelgp]

sorry, you cannot Insert with Criteria, you can only do this in Update Query.

 

[Gasman]

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]

I tried changing code with UPDATE query....

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....

 

[arnelgp]

remove the Last ")" and replace it with just " " (space).

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