simple syntax question

[markjkubicki]

i've got this simple sub that updates a field is a set of records to be a clean sequence of numbers; but I've got "something" wrong. All of the cycling thru number and updatinng of variable values foot correctly, it's the ".update" and ".edit" parts that aren't working (possibly the ".findfirst" also ?)

any suggestionns would be a great help
many thanks in advance, mark

-------------------------------------
Set Db = CurrentDb()
Set rst = Db.OpenRecordset("SELECT tbeAdditionalPages.Printorder, tbeAdditionalPages.BaseCatalogSheet " & _
"FROM tbeAdditionalPages " & _
"WHERE tbeAdditionalPages.type = '" & Forms![frmSpec].[Type] & "';")

With rst
vCnt = DCount("[Type]", "tbeAdditionalPages", "[Type] = '" & Forms![frmSpec].Type & "'")
vCntLoop = 1
vCntPrintOrder = vCnt

Do While vCntLoop <= vCnt
Dim strSearch As String
strSearch = DMax("[PrintOrder]", "tbeAdditionalPages", "[Type] = '" & Forms![frmSpec].Type & "'")
.FindFirst strSearch
.edit
![Printorder] = vCntPrintOrder
vCntPrintOrder = vCntPrintOrder - 1
.Update
vCntLoop = vCntLoop + 1


MsgBox vCntLoop & " - " & vCntPrintOrder
Loop
End With

[weekend00]

you'd better step into the code to see what happened.

[markjkubicki]

2 notes:

I've revised the lines in the middle of the code from:

Dim strSearch As String
strSearch = DMax("[PrintOrder]", "tbeAdditionalPages", "[Type] = '" & Forms![frmSpec].Type & "'")
.FindFirst strSearch
.edit

to be:

Dim strSearch As String
.FindFirst DMax("[PrintOrder]", "tbeAdditionalPages", "[Type] = '" & Forms![frmSpec].Type & "'")
.edit

(it's definetly the findfirst line that is giving me trouble - at least I think it is...)


second:
I'm not sure how to step-thru
I opened both the form and the VBA editor (in seperate windows);
I put a break point in the code above the line I'd like to start tracking;
yet when I run the code (thru an event on the form), it seems to run thru to the end -without breaking so that I can step thru...
Do you have any source that would be a good debugging tutorial for me?

[weekend00]

set break point outside the loop. or on first line of you code.

it is not syntax error. is it something in you logic, we can not check inside without data.

[markjkubicki]

I moved the break to the very first line of code in the subroute ("Public Sub subname); it still did not "break"; it ran thru tot he end of the proceedure...

I'm surprised that you think it's the logic since all of the code except for the record .findfirst/.edit/.update runs fine. If I comment those lines, and put watches in for all of the other variables... they run in the manner that I anticipated. Never-the-lass, I'm the newbie, and suprises can be a good thing (and a learning experience too)

[ajetrumpet]

here may be some additional help:

"WHERE tbeAdditionalPages.type = '" & Forms![frmSpec].[Type] & "';"

**if TYPE is anything other than a string, try this instead:
"WHERE tbeAdditionalPages.type = " & Forms![frmSpec].[Type] & ";"


strSearch = DMax("[PrintOrder]", "tbeAdditionalPages", "[Type] = '" & Forms![frmSpec].Type & "'")

DMax does not return a string. Does your code error here? If not, it should. Try:
strSearch = CStr(DMax("[PrintOrder]", "tbeAdditionalPages", "[Type] = '" & Forms![frmSpec].Type & "'"))


.FindFirst strSearch

This syntax is incorrect. You need an exact substitute for a WHERE clause, not just a value:
Instead of .FindFirst(strSearch), try this: .FindFirst "[fieldname] = '" & strSearch & "'"



I'm almost certain your firstfirst is not working because the syntax is incorrect. If you want to know for sure though, add

if .nomatch=true then
msgbox "this syntax is incorrect"
'close rs
exit sub
end if

break the code after the NOMATCH property and hover over the mouse over "NOMATCH" to see the result of your findfirst code.

[markjkubicki]

lesson learned... (THANKS - you can be my hero for the day !)
-m.