ADO find function problem

[swav_one]

Hi All,

I've put together a piece of code that allows me to modyfy Access reccords from my Excel tracking sheet.

The code takes a refference number from a cell in Excel and then finds this refference no in Access to modify records linked to it.

It all works fine in my dummy database, but when I implement it to the real thing an error occurs.

The error that I am getting is related to the ADO find function and reads:

Run-time error '3021':
Either BOF or EOF is True, or the current reccord has been deleted. Requested operation requires a current reccord.


After some simple trouble shooting, I've noticed that this is caused by the fact that all refference numbers in Access are formated as text.

Here is the code:

Sub find_and_ammend()

Dim cnn1 As New Connection
Dim rsJobs As Recordset
Dim find As String

find = ActiveCell.Value


cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Fidel\My Documents\Personal\SQL" & _
"\ae.mdb;"

Set rsJobs = New ADODB.Recordset

rsJobs.Open "Jobs", cnn1, adOpenKeyset, _
adLockPessimistic

rsJobs.find "JobReference= ' " & find & "'"
rsJobs.Fields("memo") = rsJobs.Fields("memo") & "My text here"
rsJobs.Update
Debug.Print rsJobs.Fields("memo")
rsJobs.Close

End Sub

Has any body got an idea How can I work aroud this?
Changing the refference numbers or adding another collumn with seccond refference number is not an option.

Cheers,

Slawek

 

[OlcayM]

Do you think
rsJobs.find "JobReference= ' " & cstr(find) & "'"
works?

OlcayM

 

[swav_one]

Do you think
rsJobs.find "JobReference= ' " & cstr(find) & "'"
works?

OlcayM

Unfortunatelly it does not work.

Oh and by the way, the debuger highlights this line:

rsJobs.Fields("extra") = rsJobs.Fields("extra") & "My text here"

 

[GolferGuy]

Is Memo the actual name of the field? With Memo being a field type, it is best to not use a reserved word for a field name. Can you rename the field to be more descriptive, which would also mean you were not using an Access reserved word for the field name?

 

[OlcayM]

Hi,

I know approx. nothing about ADO. I work wirh DAO and in access. But in your situation, I believe you first have to EDIT before line
rsJobs.Fields("memo") = rsJobs.Fields("memo") & "My text here"

OlcayM

 

[boblarson]

Four things:

1. If you are doing this in the current database, your connection is not necessary to do like you are doing and in fact may fail. You would want

cnn1= CurrentProject.Connection

2. Memo is indeed a reserved keyword and will screw you up if used as a field name or object name. Rename the column or you will continue to have problems.

3. I believe OlcayM is correct. If you are editing a field with ADO, you should be prefacing the update with .Edit.

4. If JobReference is a number and not text, get rid of the single quotes.

 

[RoyVidar]

The message means that it doesn't find a record (which you don't check for - always check for .Eof after .Find), so it continues to try to edit a record while on .Eof or .Bof, which creates this error.

In the initial code, you are looking for a text starting with a space. Is that correct?

i e

rsJobs.find "JobReference= ' " & find & "'"

vs

rsJobs.find "JobReference= '" & find & "'"

If this code can contain single quotes, you might need to do something like

rsJobs.find "JobReference= '" & replace(find, "'", "''") & "'"

(copy/paste to notepad to see the number of/and which characters)

I don't think I'd open a whole table to do a .find, I'd rather do

rsJobs.Open "SELECT thePK, Memo FROM Jobs WHERE JobReference= '" & find & "'"

then try to edit. Just check for .Eof first, which means nothing is found.

(invoking the .Edit method is a DAO thing)

 

[swav_one]

Yessss!!

RoyVidar's suggestion worked. I had a space in

rsJobs.find "JobReference= '" & find & "'"

A small success - I wonder for how long ... Thanks for your help - really appreciated.

I did change the name of the field from "memo" to "extra".


Some of your suggestins did not work or I used them incorrectly (the second more propable):

I did put in a "If rsJobs.EOF" section and used .Edit:

If rsJobs.EOF Then
    MsgBox ("EOF True")
    Else
    rsJobs.Edit
    rsJobs.Fields("extra") = rsJobs.Fields("extra") & "My text here"
    rsJobs.Update
    Debug.Print rsJobs.Fields("extra")
    rsJobs.Close

End If

Unfortunatelly I was receiveing an Compile Error on the .Edit function which read "Method or data member not found".

When I tried the:

rsJobs.Open "SELECT thePK, Memo FROM Jobs WHERE JobReference= '" & find & "'"

I kept getting a error: '3709' - The connection cannot be used to perform the operation.

Both of the above errors ware occuring even dough DAO 3.6 object library was switched on.

I wouldn't be supprised if the error will be on my side. Would you bother to explain and correct me?

Cheers,

Slawek

 

[RoyVidar]

The code

rsJobs.Open "SELECT thePK, Memo FROM Jobs WHERE JobReference= '" & find & "'"

was meant as a bit of "pseudo" code. I will sometimes fetch both the primary key field of the table, in addition to the field(s) I wish to edit, hence "thePK" - The Primary Key, whatever it is called.

You'd need to fire it off on the same connection that you're using now, using the same lock type, cursort type etc, so, to be a bit more specific

dim SQLString               as String

SQLString = "SELECT ReplaceWithNameOfYourPKField, Memo " & _
            "FROM Jobs WHERE JobReference= '" & find & "'"

rsJobs.Open SQLString , cnn1, adOpenKeyset, _
                 adLockPessimistic, adCmdText

Using such as this, is often preferred as it will retrieve only one record, in stead of opening the whole table. Even better, would be if you could run an action query

dim TheText                 as String

TheText = "My text here"

cnn1.Execute "UPDATE Jobs SET Memo = Memo & '" & Replace(TheText, "'", "''") & _
             "' WHERE JobReference= '" & find & "'", , _
             adExecuteNoRecords + adCmdText