Instruction end

[TonC]

Hello,

My vba code reports to me “expect instruction end”

CurrentDb.Execute "Update tblRegister Set SongNaam = '" & SongNaam & _

  "',Bank = '" & Bank.Value & _
  "',Deel = '" & Deel.Value & _
  "Locatie" = Locatie.Value "", ;

the last line gives the error.

Any help would be appreciated.

TonC

[Gasman]

Put it into a string variable and debug.print it
Awful structure TBH.

But as a guess

CurrentDb.Execute "Update tblRegister Set SongNaam = '" & SongNaam & _

  "',Bank = '" & Bank.Value & _
  "',Deel = '" & Deel.Value & _
  "',Locatie = '" & Locatie.Value "'"

[TonC]

Hallo Gasman,
No, vba code was not accepted, still the same message.

[Gasman]

So do as I suggested with a string variable and paste the results of that back here.
Or better still construct is a lot better?

CurrentDb.Execute "Update tblRegister Set SongNaam = '" & SongNaam & "'" &  _
    ",Bank = '" & Bank.Value & "',Deel = '" & Deel.Value & "',Locatie = '" & Locatie.Value "'"

[Aussiebear]

Does this work for you?

If the values SongNaam, Bank.Value, Deel.Value, and Locatie.Value are all text fields (requiring single quotes around their values in the SQL string), the corrected code should look something like this:

CurrentDb.Execute "UPDATE tblRegister SET SongNaam = '" & SongNaam & "', " & _
    "Bank = '" & Bank.Value & "', " & "Deel ='" & Deel.Value & "', " & "Locatie = '" & Locatie.Value & "'"

However if the values Bank, Deel, or Locatie are numeric fields in the tblRegister table, they should not have single quotes around their values in the SQL string. For example, if Locatie is a number:

CurrentDb.Execute "UPDATE tblRegister SET SongNaam = '" & SongNaam & "', " & _
    "Bank = '" & Bank.Value & "', " & "Deel = '" & Deel.Value & "', " & "Locatie = " & Locatie.Value

[Gasman]

Does this work for you?

If the values SongNaam, Bank.Value, Deel.Value, and Locatie.Value are all text fields (requiring single quotes around their values in the SQL string), the corrected code should look something like this:

CurrentDb.Execute "UPDATE tblRegister SET SongNaam = '" & SongNaam & "', " & _
    "Bank = '" & Bank.Value & "', " & "Deel ='" & Deel.Value & "', " & "Locatie = '" & Locatie.Value & "'"

However if the values Bank, Deel, or Locatie are numeric fields in the tblRegister table, they should not have single quotes around their values in the SQL string. For example, if Locatie is a number:

I thought that would just give 'type mismatch'?
CurrentDb.Execute "UPDATE tblRegister SET SongNaam = '" & SongNaam & "', " & _
    "Bank = '" & Bank.Value & "', " & "Deel = '" & Deel.Value & "', " & "Locatie = " & Locatie.Value

[June7]

Your code will update EVERY record in table to the same values because there is no filter criteria provided such as record ID.

Is it possible you really want to do an INSERT action?

Why are you using code to UPDATE (or INSERT) as opposed to direct edit/entry of record on BOUND form and controls?

Original code is missing apostrophes, if, as noted, these are all text fields.

Gasman correction missed an & for concatenation of last apostrophe.

If any of these string values include apostrophe, such as in O'Neal, code will fail.

Could open recordset object and use EDIT and UPDATE methods and not deal with concatenation or apostrophes.

[TonC]

Hello
Sorry for my late reaction, but I was short sick.

I did look at the code but I think I made a mistake

Here my full procedure in VBA:

CurrentDb.Execute "Update tblSpullen Set SongNaam = '" & SongNaam.Value & _
"',Bank = '" & Bank.Value & _
"',Category = '" & Category.Value & _
"',Style = '" & Style.Value & _
"',Left = '" & Left.Value & _
"',Right1 = '" & Right1.Value & _
"',Right2 = '" & Right2.Value & _
"',Right3 = '" & Right3.Value & _
"',Tempo = '" & Tempo.Value & _
"',Maat = '" & Maat.Value & _
"',OS = '" & OS.Value & _
"',Deel = '" & Deel.Value & _
"',LocatieKB = '" & LocatieKB.Value & _
", & Aantal.Value & " & " where & Id = " & Id

Still at the last line I get a syntax error. Whatever I do “syntax error” even when I look at the code
syntax error. My wife says you are a syntax error.

Error code 3078
Declare a variabel ??


The last line is a nummeric type long integer.
Most lines are comboboxes with input

SongNaam = combobox with inputbox
Bank= combobox with inputbox
Category= combobox with inputbox
Style= combobox with inputbox
Left= combobox with inputbox
Right1 – Right3 = combobox with inputbox
Tempo = textfield
Maat= combobox with inputbox
OS= combobox with inputbox
Deel= combobox with inputbox
LocatieKB= combobox with inputbox
finally Aantal= nummeric field long integer

I hope this helps to give me tips to solve this issue.

Greet TonC,

[Aussiebear]

Okay, here my lastest effort

Dim strSQL As String
strSQL = "UPDATE tblSpullen SET " & _
         "SongNaam = '" & Replace(SongNaam.Value, "'", "''") & "', " & _
         "Bank = '" & Replace(Bank.Value, "'", "''") & "', " & _
         "Category = '" & Replace(Category.Value, "'", "''") & "', " & _
         "Style = '" & Replace(Style.Value, "'", "''") & "', " & _
         "Left = '" & Replace(Left.Value, "'", "''") & "', " & _
         "Right1 = '" & Replace(Right1.Value, "'", "''") & "', " & _
         "Right2 = '" & Replace(Right2.Value, "'", "''") & "', " & _
         "Right3 = '" & Replace(Right3.Value, "'", "''") & "', " & _
         "Tempo = '" & Replace(Tempo.Value, "'", "''") & "', " & _
         "Maat = '" & Replace(Maat.Value, "'", "''") & "', " & _
         "OS = '" & Replace(OS.Value, "'", "''") & "', " & _
         "Deel = '" & Replace(Deel.Value, "'", "''") & "', " & _
         "LocatieKB = '" & Replace(LocatieKB.Value, "'", "''") & "', " & _
         "Aantal = " & Aantal.Value & " " & _
         "WHERE Id = " & Id 
' Assuming Id is a numeric field
' For debugging, you can print the SQL string to the Immediate Window (Ctrl+G in VBA editor)
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError 
' dbFailOnError is good practice to catch errors

And here's why I've gone with this.
Explanations:

1. Dim strSQL As String: It's good practice to build the SQL string in a variable first. This makes it much easier to read, debug (especially with Debug.Print), and manage.
2. Replace(Control.Value, "'", "''"): This is a critical addition for all text fields. If any of the comboboxes or text fields contain an apostrophe (e.g., "O'Malley's Song"), the original code would cause an SQL syntax error. The Replace function doubles any single apostrophes, which is the standard way to escape them in SQL strings.
3. Correct Comma Separation: Each FieldName = Value pair is now correctly separated by a comma.
4. Aantal = " & Aantal.Value & ": Since Aantal is a numeric (Long Integer) field, its value is concatenated without single quotes.
5. WHERE Id = " & Id: The WHERE clause is now correctly formatted. Assuming Id is also a numeric field, it is concatenated without single quotes.
   
   • Important: You need to ensure Id is correctly populated with the value of the record being updated. This Id variable or control must exist and hold the correct value. For example, if it's a textbox on the form named txtId, it would be Me.txtId.Value.
   
   
6. Debug.Print strSQL: This line is incredibly useful for debugging. Before CurrentDb.Execute, if you place a breakpoint on that line and run the code, you can open the Immediate Window (Ctrl+G in the VBA editor) and see the exact SQL string that is being sent to the database. This helps identify any remaining syntax errors.
7. dbFailOnError: Added to the CurrentDb.Execute method. This is an option that tells Access to generate a run-time error if the query fails, which makes debugging easier than if it silently fails.

Final Check:

• Confirm that Id (the variable or control used in the WHERE clause) is indeed a numeric type in tblSpullen. If it's a text type, then WHERE Id = '" & Replace(Id, "'", "''") & "'" would be needed.

This revised code should resolve the "expected instruction end" error and correctly update the tblSpullen table, while also providing basic protection against apostrophes in text fields.

Perhaps Gasman and Arnelgp will have a better solution.

[June7]

Aussie, note that the Debug and Execute lines were merged by post into one line.

[Aussiebear]

Thank you June7. I have corrected the issue.

[Gasman]

Another way to do it is

strSQL = "Update tblSpullen Set SongNaam = '" & SongNaam.Value & "'"
strSQL = strSQL & ",Bank = '" & Bank.Value & "'"
etc

Regardless a Debug.Print is needed! if you cannot see the issue.

[TonC]

Hello, before I can it workes, I need a lot of time to understand how debugging is working The function key F5 shows me a macro screen, where to put the debug print,above or below the
the currentdB execute is not clear for me. I tried both levels but it did not work. With the F9 I only get the message "SYNTAX ERROR" it tells me totally nothing what kind of syntax there is.
I looked in the internet this morning and afternoon how debugging is working, but internet shows me users with perhaps a different kind of version access. They do have the F5 function key and when they showing how it works look like simple (there is a lot of vba code needed to tell access to activate the F5 key. The break F9 seems only to work in a module and not in a sub proceddure in my version.

But, i will keep you not in suspence, the code from "Aussiebear" didn't work. When I presssed the command update nothing happens. So my efforts to the find out how debugging works where all failed.

Still, I thank you all for the help.
TonC.

[June7]

Review https://www.techonthenet.com/access/...10/debug01.php

Position Debug.Print right after code that builds the strSQL value, as Aussie shows.

Use a breakpoint and step through code. A breakpoint can be set in ANY procedure.

Clicking F5 to run code associated with a button won't work - have to click the button. Actually, cannot use F5 to trigger any procedure behind a form or report. Only for procedures in a general module and only if procedure does not have input arguments.

If clicking button does nothing then your code is likely not associated with the button.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Gasman]

Watch a few videos.
https://www.youtube.com/results?sear...ccess+vba+code

[Gasman]

Generally you would put the Debug.Print before the line where you use it. Not much point after is there? Also if it fails, as it does now, you will never get to the debug anyway.
I would comment out the Execute anyway until I know the sql is correct.

[jdelano]

If you're trying to use a field named LEFT, it will cause a syntax error when the query is run given that LEFT is a reserved word. That field name should be changed.

[June7]

Sometimes using reserved word as name won't cause issue. Name will work in spite of warning when field is created. However, Left does trigger error even though there is no warning when creating field. To prevent error, must enclose in brackets: [Left].

Always best not to use reserved words as names regardless. http://allenbrowne.com/AppIssueBadWord.html

[TonC]

Hello forumfriends,

After a long day and night (look at the time from this message) looking at advice from you all,
I succeeded to solve the problems with my DB. I have seen en learned slightly how debugging is working. So, with your tips and clues you all made me happy. The problems were caused by the keyword like “Left” and “Right1 etc etc.

Thanks for all the effort you done for me.

BTW
June7, I don’t know you, but I think your grreat !”

Greetings, till next time
TonC

[Gasman]

Unless you can create code without any mistakes every time, I cannot see how you can get by without debugging?