ADODB Find Issue?

[gray]

Hi

Access 2007

Ye Gods does it never end?!

I loop thru' an ADO Rs to update certain fields. Before doing anything I store the ID of the Primary Key (autonum) of the current record. It is stored in a Long variable.

At the end of my loop I want to return to that record.... for which I do i FIND.... sometimes it works, sometimes it doesn't.... when it doesn't I loop around the Rs and msgbox the IDs.... and hey presto there is the record I'm after which the FIND says it can't find. I despair.....

'Set value 
Edit_rstADO![TBL1.List_Order] = Extg_List_Order
'Update Rs    
Edit_rstADO.Update
 
'Now move back to original record    
Edit_rstADO.MoveLast     } ' desperate efforts to clutch at straws 
Edit_rstADO.MoveFirst     }
Edit_rstADO.Find "[TBL1.Unique_No]=" & Rec_No_To_Mve_To
 
If Edit_rstADO.EOF Then
 
        Edit_rstADO.MoveFirst
        Do Until Edit_rstADO.EOF
            MsgBox Edit_rstADO![TBL1.Unique_No]
            Edit_rstADO.MoveNext
        Loop
 
        MsgBox "ERROR Can't Find Record " & Rec_No_To_Mve_To
        GoTo Err_Move_Field_Up_Button_Click
End If

Why is .EOF found but the record is clearly still in the RS? Any Ideas please?

Thanks

 

[Galaxiom]

Easier to use the Bookmark property of the recordset.
Store it then set the Bookmark property to it when you want to return.

Dim strBookmark as String
strBookmark = rs.Bookmark
'Do stuff
rs.Bookmark = strBookmark

 

[gray]

Thanks... i did give that a whirl but I think I declared it as a Variant... I shall try it again... cheers

 

[Galaxiom]

Variant would also work. Maybe there is another issue.

I would highly recommend you change your naming. Including Edit in a recordset name is very distracting as is including a tablename in the field name.

Alias the fieldname to something less distracting in the query.

Also use With Grouping.

With rsWhatever
   !myfield = somevariable
   .MoveNext
End With

 

[gray]

Hi

I've tried the Bookmark again but it when I try to reset it, it fails with 424 ... arguments are of wrong type, out of range or in conflict etc

Dim strBookmark As String
strBookmark = Edit_rstADO.Bookmark
Edit_rstADO.Bookmark = strBookmark

P.S.
Names and Tables:-

I've several Rs's floating around some of which I only want read-only... hence, to remind me, read_rstADO and edit_rstADO.

The TBL1 in the field is actually a table alias... Yet another Joy I've had with Access. When joining two tables that contain similar field names I use a prefix of TBL1, TBL2 etc (or whatever). Unfortunately Access only adds the prefixes to similarly-named columns and not all of them.... so
TBL1.Unique_No and
TBL2.Unique_No but
Field_Selected and not
TBL1.Field_Selected

because Field_Selected only appears in one of the tables. In fact the use of the prefix crashes the command.... brilliant thinking by someone somewhere...

 

[gray]

Hmm... I may have a solution....

I've added extra parameters to my FIND i.e.
, 0, adSearchForward, 1

so...

Edit_rstADO .Find "[TBL1.Unique_No]=" & Rec_No_To_Mve_To, 0, adSearchForward, 1

and it seems to be working? Fingers crossed...

Cheers

 

[Galaxiom]

TBL1.Unique_No and
TBL2.Unique_No but
Field_Selected and not
TBL1.Field_Selected

because Field_Selected only appears in one of the tables. In fact the use of the prefix crashes the command.... brilliant thinking by someone somewhere...

The dot has a syntactical definition. When you artificially add the dot to a name you are conflicting with that definition.

Most programmers would avoid the dot in any field name by aliasing it in the query because a dot in a name is very distracting when reading it as part of a larger code block.

Experienced developers avoid spaces in names too, not only because they then need square brackets around the name but because the gap artificially breaks the flow of the expression.

Likewise special characters such as # & ! ( ) % *
None of these should ever be used in an object name. They just make code harder to read.

 

[gray]

Hi NSW

Just opened my curtains to our first glimmer of frost... so I imagine NSW is starting to get even hotter?

I wonder if I've understood you correctly? The dots in the field names above, "TBL1.Unique_No" are not actually part of the field name but are the concatenators for table alias and field name... so..

SELECT * FROM Invoices AS TBL1 INNER JOIN Payments AS TBL2 ON blah blah WHERE blah blah

Thus the reference to Unique_No in Invoices is "TBL1.Unique_No" with the reference to Unique_No in Payments is "TBL2.Unique_No" ..... what's immensely irritating in Access is that a reference to a unique column, say, for example, "Payment_Recieved" fails if one uses "TBL2.Payment_Recieved" rather than "Payment_Recieved"... it's a pain.

Also I discovered that somethings in Access tend to need the square brackets around aliased field references e.g. [TBL.Unique_No] and some don't. I tend now to add these brackets around everything until it fails... in a guilty before until innocent way. I think all this depends on ADO, DAO, back-end and cnn providor. Another pain... There's enough to worry about without need to Google every single reference in Access...

Personally, I always use underscores to break field names up where I can and never spaces. But I've noticed even "_" has problems on some Internet technologies.

And I know some people like CamelCase or even camelCase but I've even been tripped up on this recently when using a PHP builder... some bright spark had decided to make this particular platform case sensitive... not in the usual "unix" sense but first-character-sensitive.... so a call to

RunOrders = RUNORDERS = RUNOrders = Runorders = RunORDers etc

but

rUNORDERS would run an entirely different command based simply on the casing of the 1st character.... I despair.....

Bring back ICL VME/B...all is forgiven...40 yrs old and still 50 years ahead of everything else I've ever seen....