RunSQL coding what is wrong with it? 1

[timotai]

Hi All

What is wrong with this coding it is designed to pick up if someone is on holiday and if so assign the shift to someone on a different shift level:


Public Function Reassign()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim StrSql As String

StrSql = "SELECT TeamMembers.Employee, TeamMembers.Active, WeekLog.ShiftLevel, WeekLog.[ShiftPattern ID] as Pattern FROM TeamMembers INNER JOIN WeekLog ON TeamMembers.Employee = WeekLog.[Employee Number] WHERE (((TeamMembers.Active)=No) AND ((WeekLog.ShiftLevel)=1));"

Set db = CurrentDb
Set rst = db.OpenRecordset(StrSql, dbOpenSnapshot)

If rst.BOF And rst.EOF Then
Call MsgBox("No Team Members meet the criteria.&quot
Else
Do Until rst.EOF
DoCmd.RunSQL "UPDATE TeamMembers INNER JOIN WeekLog ON TeamMembers.Employee = WeekLog.[Employee Number] " &_
"SET TeamMembers.Standins = 1, WeekLog.ShiftLevel = 1, WeekLog.[ShiftPattern ID] = rst("Pattern&quot" &_
"WHERE (((WeekLog.ShiftLevel)=2));"

rst.MoveNext
Loop
End If

rst.close
Set rst = Nothing
db.close
Set db = Nothing

End Function

The section giving me the hassle is:

DoCmd.RunSQL "UPDATE TeamMembers INNER JOIN WeekLog ON TeamMembers.Employee = WeekLog.[Employee Number] " &_
"SET TeamMembers.Standins = 1, WeekLog.ShiftLevel = 1, WeekLog.[ShiftPattern ID] = rst("Pattern&quot" &_
"WHERE (((WeekLog.ShiftLevel)=2));"


But I can't see why?

Any ideas


all help greatly appreciated.

Many Thanks

Tim

 

[Kalin]

Hi Tim,

I think your problem is the recordset....

WeekLog.[ShiftPattern ID] = rst("Pattern&quot"

Should be ...

Weeklog.[ShiftPattern ID] = "& rst.Fields![Pattern] & " "

Grtz,

Kalin

 

[Kalin]

If Pattern is a String then it should be

Weeklog.[ShiftPattern ID] = """& rst.Fields![Pattern] & """ "


Grtz,

Kalin

 

[timotai]

Hi

Thanks for the reply

Neither of these is working but I'm also a little confused with the coding. You are saying rst.fields! but this is not directly coming from the field but from the sql code below and is tagged as pattern.

StrSql = "SELECT TeamMembers.Employee, TeamMembers.Active, WeekLog.ShiftLevel, WeekLog.[ShiftPattern ID] as Pattern FROM TeamMembers INNER JOIN WeekLog ON TeamMembers.Employee = WeekLog.[Employee Number] WHERE (((TeamMembers.Active)=No) AND ((WeekLog.ShiftLevel)=1));"

I am not experinced with doing SQL strings though so I may just be being stupid.

Many Thanks

Tim

 

[VicM]

timotai:

I think Kalin is on the right track.

Firstly, the StrSql, when used to open a recordset, creates a virtual 'table' with field names and records of data. At that point Pattern is a field name and is referenced as rst!Fields("Pattern&quot.Value. (I always include the .Value, since depending on the code, Access can interpret rst!Fields("Pattern&quot as either the value or the object: Field.)

Secondly, the way you have the SQL written with the rst("Pattern&quot", within double quotes("... WeekLog.ShiftLevel = 1, WeekLog.[ShiftPattern ID] = rst("Pattern")" & "WHERE (((WeekLog.ShiftLevel)=2));", substitutes according to the paired quotes and not a variable value.

Additionally, when needing to represent a string variable inside a SQL statement, it's always safer to use the single quote ('). Using the double quote as suggested by Kalin, can lead to confusion if the correct number and sequence are not placed properly in the syntax.

Another way to express the double quote so it can be used literally within the SQL statement, is to use Chr(34).

So the way I would write the SQL if Pattern is numeric, would be:

"... WeekLog.[ShiftPattern ID] = " & rst.Field("Pattern&quot.Value & " WHERE (((WeekLog.ShiftLevel)=2));"

And if the value of Pattern is a string, then the SQL would look like:

"... WeekLog.[ShiftPattern ID] = " & chr(34) & rst.Fields("Pattern&quot & chr(34) & " WHERE (((WeekLog.ShiftLevel)=2));"

or

"... WeekLog.[ShiftPattern ID] = '" & rst.Fields("Pattern&quot & "' WHERE (((WeekLog.ShiftLevel)=2));"

Note: I can use the double quote around Pattern because it's not within the double quotes defining the SQL statement. Also, it's best to be sure to include a space before the WHERE clause.

Hope this helps,

Vic

 

[timotai]

The second one worked like a charm. Thanks you guys. Again this site gets me out of yet another problem on my database.

Here's a star