Visual Basic Database - Need ADO Strategy help Find record, otherwise return to bookmark

Author

Need ADO Strategy help Find record, otherwise return to bookmark

Atreju

2005-03-16, 8:59 pm

This is quite long-winded, so please be patient. I appreciate it.

I have an all pure-code database application.

The way I load all the fields' data into the various controls is in
the MoveComplete event.

eg: Textbox.text = RS.Fields(textbox.tag).Value
(I use the tag, which matches the field name, so I can just use one
big control array for all fields).

If this is not the recommended way of doing it, please stop me here.

Assuming that's ok, this is my problem.
First of all, in the beginning of my MoveComplete Event procedure, I
have a standard:

If RS.BOF Then RS.MoveFirst: Exit Sub
If RS.EOF Then RS.MoveLast: Exit Sub

This insures that it will go to the first or last record when reaching
end of file. Once it does that step then the rest of the proc occurs.

There is a point in my application where the user can "goto" a record
by putting in any text from a specific field, and it works like this:

User Clicks 'goto' command button.
Form appears with Textbox, where user puts in text to find.

This is the find proc (which works perfectly).

Public Sub DoFind(ByVal sText As String, ByVal sField As String, ByVal
sOper As String)
' sText is only the actual text to be found, and contains no SQL
Dim sAdjusted As String

mbFindMode = True

sAdjusted = Replace$(sText, """", """""")
sAdjusted = Replace$(sAdjusted, "'", "''")

mvBookmark = adoPrimaryRS.Bookmark
adoPrimaryRS.MoveFirst

adoPrimaryRS.Find sField & " " & sOper & " '" & _
IIf(sOper = "Like", "%", "") & sAdjusted & IIf(sOper = "Like",
"%", "") & "'"

mbFindMode = False

If adoPrimaryRS.EOF Then adoPrimaryRS.Bookmark = mvBookmark

End Sub

Here's my problem - if the requested text is NOT found, my approach
was to say - if EOF is reached, must be that the desired text was not
found, therefore go back to the bookmark.

However, unfortunately, the EOF is already handled within the
movecomplete event, and therefore by the time this line is reached
within this Sub, we have already sent the cursor back to Last.

Ok - so to handle this, my approach (previously I had bound-controls,
not manual loading of field contents into controls) was this:
In the beginning of the MoveComplete event, I had a statement:

If mbFindMode Then Exit Sub

Simply - if we were in the middle of a Find, the movecomplete would
not handle any data loading, nor would it send the cursor to last.

If I do a 'goto' and the text is not found, it stays where it was
because the last line of the DoFind is executed:

If adoPrimaryRS.EOF Then adoPrimaryRS.Bookmark = mvBookmark

The problem is this: If I do a 'goto' and the text IS found in a
record, it does jump the cursor to that record, but unfortunately
mbFindMode is still True so the MoveComplete event does not process
the loading of field values into the controls. The beginning of the
MoveComplete event has a statement

If mbFindMode Then Exit Sub

This is turning into a "Catch-22."
On the one hand, I want NORMAL operations to stipulate that if EOF or
BOF then go to the last/first record, respectively.
However, if we are in the mbFindMode, then don't want to do that,
because I want to send the cursor back to the bookmark. The only way I
know how to check if our search text was NOT found is if I have
reached EOF - this tells me that no record matched the Find query, and
I should now return to the bookmark. However, if I want to test for
EOF, I have to get around the IF EOF clause in the movecomplete,
because if I don't, then my Find procedure will never know that EOF
was reached.

If I don't turn off mbFindMode before trying to return to bookmark,
then it returns to bookmark if the text is not found, but if it IS
found, it goes to the cursor but doesn't process the data loading.

If anyone wants the entire form's code, please let me know. It is
going to be un-testable without my database, but an expert could
probably discover a way to fix this without having to have my data.

Any help would be greatly appreciated.

Thanks.

---Atreju---

Atreju

2005-03-16, 8:59 pm

On Wed, 16 Mar 2005 22:44:35 GMT, Atreju <someone@who.hates.junkmail>
wrote:

I feel very sill, but I think I just solved the problem.

Will follow-up if I have any further need of help.

---Atreju---

Sponsored Links