update access from module

[michelle]

Hi folks!

I have written a module which is calling from a form.

Function RunTheChangeBox()

Const myPicture = "SELECT DISTINCT
[Photos].[boxId],[Photos].[magasinId]," & _
" [Photos].[pictureId], [Photos].[magasineName],[Photos].[date],
[Photos].[rowId] " & _
" FROM Photos"

Dim aSql As String
Dim newBox As Variant
Dim oldBox As Variant
Dim myMagsineName As String
Dim myMagsineId As String
Dim i As Integer

Set db = CurrentDb()

newBox = Forms![boxes]![boxId]
oldBox = Forms![boxes]![oldBoxId]
myMagsineName = Forms![boxes]![magasineName]
myMagsineId = Forms![boxes]![magasineId]


maxPicureId = DMax("pictureId", "Photos", "[magasineId] = '" &
myMagsineId & "' AND [boxId] = " & oldBox & "")
minPicureId = DMin("pictureId", "Photos", "[magasineId] = '" &
myMagsineId & "' AND [boxId] = " & oldBox & "")
i = minPicureId

For i = minPicureId To maxPicureId
sSQL = "Update [Photos] set [boxId] = " & newBox & " where
[boxId] = " & oldBox & " AND [magasineId] = " & myMagsineId & " AND
[pictureId] = " & i & " ;"
CurrentDb.Execute sSQL
i = i + 1
Next i

Exit_RunTheChangeBox:
MsgBox "Box ändrad!!!"
Exit Function

Err_RunTheChangeBox:
MsgBox Err.description
Resume Exit_RunTheChangeBox

End Function

When I come to ' CurrentDb.Execute sSQL
' it all crashes. Can someone pease what I've missed?

Thanks in advance
/Michelle
A pretty desparate one after one week of struggeling and searchin for
an answer

 

[John Vinson]

When I come to ' CurrentDb.Execute sSQL
' it all crashes. Can someone pease what I've missed?

Try stepping through the code and stopping after sSQL has been set; in
the Immediate pane type

?sSQL

What is the actual SQL string that you get?

I did notice that you have a FOR loop which increments i, and you're
ALSO explicitly setting i to i+1. I suspect you DON'T want to do both!

Rather than running multiple Update queries, you might want to
consider combining them into one. Without knowing your table structure
or exactly what you're trying to do I can't be sure, but might you be
able to construct an IN clause like:

sSQL = "Update [Photos] set [boxId] = " & newBox & " where
[boxId] = " & oldBox & " AND [magasineId] = " & myMagsineId & " AND
[pictureId] >= " & minPicureID & " AND [PictureID] <= " & maxPictureID
& ";"
Currentdb.Execute sSQL

Or, for better error trapping, use the Querydef Execute method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error

<your existing code building sSQL>

Set db = CurrentDb
Set qd = db.CreateQuerydef("", sSQL)
qd.Execute, dbFailOnError

Proc_Exit:
On Error Resume Next
Set qd = Nothing

Proc_Error:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbOKOnly
Resume Proc_Exit
End Sub


John W. Vinson[MVP]