Using Code Continuation Character 3

[rj51cxa]

The following code is an extract from a long line of code which is used to update the fields in a table.

strSQL = "UPDATE TblCompetitor SET TblCompetitor.TxtName = Null, TblCompetitor.TxtClass = 'O',TblCompetitor.TxtInitials = Null, TblCompetitor.TxtRank_Rating = Null, TblCompetitor.TxtServiceNumber = Null, TblCompetitor.fWRN = False, TblCompetitor.fWRNWinner = False, TblCompetitor.fWonPeters = False" _
[code]

I want to break this up into a series of lines but every time I insert an underscore after the comma, the code goes red, indicating that I have made an error. I know there are certain rules about using the underscore but cannot find very much about them.  I have a feeling that I cannot break up this statement without inserting certain functions and would be grateful for any advice.

Best Regards
John

 

[PHV]

I guess you forgot the concatenation operator (&):
strSQL = "UPDATE TblCompetitor SET TblCompetitor.TxtName = Null, TblCompetitor.TxtClass = 'O'" _
& ",TblCompetitor.TxtInitials = Null, TblCompetitor.TxtRank_Rating = Null, TblCompetitor.TxtServiceNumber = Null" _
& ", TblCompetitor.fWRN = False, TblCompetitor.fWRNWinner = False, TblCompetitor.fWonPeters = False" _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

HI,

Concatenate and Continue
var = "...." & _
"........"

strSQL = "UPDATE TblCompetitor SET TblCompetitor.TxtName = Null, TblCompetitor.TxtClass = 'O',TblCompetitor.TxtInitials = Null, TblCompetitor.TxtRank_Rating = Null, TblCompetitor.TxtServiceNumber = Null, TblCompetitor.fWRN = False, TblCompetitor.fWRNWinner = False, TblCompetitor.fWonPeters = False" & _
"More SQL "

I often do this...

strSQL = "UPDATE TblCompetitor "
strSQL = strSQL & "SET TblCompetitor.TxtName = Null"
strSQL = strSQL & ", TblCompetitor.TxtClass = 'O'"
strSQL = strSQL & ", TblCompetitor.TxtInitials = Null"
strSQL = strSQL & ", TblCompetitor.TxtRank_Rating = Null"
strSQL = strSQL & ", TblCompetitor.TxtServiceNumber = Null"
strSQL = strSQL & ", TblCompetitor.fWRN = False"
strSQL = strSQL & ", TblCompetitor.fWRNWinner = False"
strSQL = strSQL & ", TblCompetitor.fWonPeters = False "

Skip,

_[red][/red]

 

[MajP]

I almost always do what skip suggests. The reason is you can easily put a debug.print between lines to error check your string and make changes and fixes.

 

[rj51cxa]

Thanks everyone for your replies. I've got plenty of ideas now. A great help.

Best Regards
John

 

[rj51cxa]

Skip,

I followed your idea as it was going to be easier to check the fields to re-set. However, I hit a problem at the end as follows:

strSQL = strSQL & ", TblCompetitor.fISTeam = False"
strSQL = strSQL WHERE TblCompetitor.LngCompetitorNo = " & Me![CompetitorNumber] & "

The WHERE clause brings up the message box Compile Error and Syntax error. I've tried to sort out the sysntax but I'm stumped. Could you please advise.

Thanks very much
John

 

[PHV]

strSQL = strSQL [!]& "[/!]WHERE TblCompetitor.LngCompetitorNo = " & Me![CompetitorNumber] & "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rj51cxa]

Hi PHV,

That didn't work I'm afraid. The code keeps adding an extra " at the end but when I run it, I get the message Syntax Error. The entire code looks like this:

Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

Dim strSQL As String
    
DoCmd.SetWarnings False
strSQL = "UPDATE TblCompetitor"
strSQL = strSQL & "SET TblCompetitor.TxtName = Null"
strSQL = strSQL & ", TblCompetitor.TxtClass = 'O'"
strSQL = strSQL & ", TblCompetitor.TxtInitials = Null"
strSQL = strSQL & ", TblCompetitor.TxtRank_Rating = Null"
strSQL = strSQL & ", TblCompetitor.TxtServiceNumber = Null"
strSQL = strSQL & ", TblCompetitor.fWRN = False"
strSQL = strSQL & ", TblCompetitor.fWRNWinner = False"
strSQL = strSQL & ", TblCompetitor.fRobertsInd = True"
strSQL = strSQL & ", TblCompetitor.fRoupelInd = True"
strSQL = strSQL & ", TblCompetitor.fFibuaInd = True"
strSQL = strSQL & ", TblCompetitor.fRobertsTeam = True"
strSQL = strSQL & ", TblCompetitor.fFibuaTeam = True"
strSQL = strSQL & ", TblCompetitor.fRoupelTeam = True"
strSQL = strSQL & ", TblCompetitor.fInd = False"
strSQL = strSQL & ", TblCompetitor.fPetersPrize = False"
strSQL = strSQL & ", TblCompetitor.fWonPeters = False"
strSQL = strSQL & ", TblCompetitor.fWonTurtle = False"
strSQL = strSQL & ", TblCompetitor.fWonTyne = False"
strSQL = strSQL & ", TblCompetitor.fISTeam = False"
strSQL = strSQL & "WHERE TblCompetitor.LngCompetitorNo = " & Me![CompetitorNumber] & ""
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
DoCmd.Requery FrmCompetitorData

Exit_Command22_Click:
    Exit Sub

Err_Command22_Click:
    MsgBox Err.Description
    Resume Exit_Command22_Click
    
End Sub

I'd be grateful if you could take a look and see where I've gone wrong.

Thanks a lot
John

 

[SkipVought]

You also must put SPACES appropriately in order that the Key Words and Field Names are separated in the concatenated string.

For instance after TblCompetitor

strSQL = "UPDATE TblCompetitor "
strSQL = strSQL & "SET TblCompetitor.TxtName = Null"

otherwise you get TblCompetitorSET

Skip,

_[red][/red]

 

[rj51cxa]

Sorry Skip, I've changed the spacing after "UPDATE TblCompetitor " but I still get a Syntax error warning. The rest of the code, except for the WHERE statement is as you wrote in your earlier post, so I think it must be something to do with that.

Best Regards
John

 

[SkipVought]

Please post EXACTLY what currently errors.

Skip,

_[red][/red]

 

[mp9]

Another missed space:

strSQL = strSQL & ", TblCompetitor.fISTeam = False"
strSQL = strSQL & "WHERE TblCompetitor.LngCompetitorNo = " & Me![CompetitorNumber] & ""

Should be:

strSQL = strSQL & ", TblCompetitor.fISTeam = False "
strSQL = strSQL & "WHERE TblCompetitor.LngCompetitorNo = " & Me![CompetitorNumber]

http://www22.brinkster.com/accessory/

 

[SkipVought]

There are missed spaces all thru the code, which is why is stated, "For instance after TblCompetitor"

Skip,

_[red][/red]

 

[rj51cxa]

Thanks everyone, I put a space before the comma at the end of each line and it worked.

There are so many wrinkles in the VBA langauge, they take a long time to learn. Your help is very much appreciated.

Best Regards
John

 

[Eupher]

As suggested above, when working with a complex concatenation, it is often helpful to use the Debug.Print method so you can see what the concatenated string really looks like when all the parts are assembled.

Ken S.

 

[rj51cxa]

Thanks, Eupher, I'll remember that for the future.