Problems updating a RecordSet!

[LucyL]

Hi,
I am allowing the user to update existing details through textboxes. They select the name of the person they wish to edit from a dropdown menu which is filled on form load. The user may edit any details, however when I try to edit the name in the actual combo box, it brings up an error. The code to date is as follows:

Dim adoconnection As ADODB.connection
Set adoconnection = New ADODB.connection

adoconnection.Open ("Provider=Microsoft.jet.oledb.4.0;" & "Data Source = h:\NewDatabase.mdb&quot

Set rs = New ADODB.Recordset
rs.Open "Select * from Landlord where Landlord_Name='" & cboName.Text & "'", adoconnection, adOpenDynamic, adLockOptimistic

rs!Landlord_Name = cboName.Text
rs!Address = txtAddr.Text
rs!Tel_No = txtNum.Text
rs!Mobile_No = txMobile.Text

rs.Update

adoconnection.Close
Set rs = Nothing

It is upon changing the name in the textbox that causes the following error...
"Either BOF or EOF is true or the current record has been deleted. Requested operation requires a current record"

Any ideas?
Thanx!

 

[kbWilliamson]

You need to include either rs.Edit or rs.AddNew before you load the fields, like this

rs.Edit

rs!Landlord_Name = cboName.Text
rs!Address = txtAddr.Text
rs!Tel_No = txtNum.Text
rs!Mobile_No = txMobile.Text

rs.Update

 

[LucyL]

Hi again,
I tried this, however the rs.Edit was not accepted and the rs.AddNew just inserted a new record, leaving the original there too..am I missing any other extras?

 

[MrEGuest]

Are you at BOF when you do the update? Haven't verified this but maybe inserting rs.Movefirst in your code before assigning all of the field values would do the trick.

Josh

 

[kbWilliamson]

Sorry for the bum steer... I use DAO most of the time.

If you have the MSDN Library, use the index to lookup
"ADO detailed programming model" and pick the "Recordset"
choice. If you don't, let me know and I'll e-mail you the page.

Your problem may be that you haven't closed the record set before you close the connection. Try this:

rs!Tel_No = txtNum.Text
rs!Mobile_No = txMobile.Text

rs.Update
rs.Close
Set rs = Nothing
adoconnection.Close

 

[LucyL]

No problem!
Well I tried that too! However, the problem is arising in this line
rs!Landlord_Name = cboName.Text
...does it have anything to do with the fact that I am opening the recordset in the DB based on the value of cboName selected, and then when I try to edit it, it doesn't know where to send the new data since I edited the original value selected in cboName?

 

[MrEGuest]

Are you saying that you've tried the movefirst?

 

[Edd]

It sounds like the SELECT isn't finding the record that you are looking for. Try:

rs.Open "Select * from Landlord where Landlord_Name='" & Trim(cboName.Text) & "'", adoconnection, adOpenDynamic, adLockOptimistic

If Not rs.EOF Then
   rs!Landlord_Name = cboName.Text
   rs!Address = txtAddr.Text
   rs!Tel_No = txtNum.Text
   rs!Mobile_No = txMobile.Text

   rs.Update
Else
   (Handle Error)
End If

Note the use of "Trim" on "cboName.Text". Extra whitespace might be throwing off the search.

 

[VBMental]

Agreeing slightly with the last post, I think the issue is your SELECT statement. Perhaps the case of the letters in the combo-box doesn't match the case of the field in the database. You could try changing the select statement "Landlord_Name=" to "Landlord_Name LIKE" so that it would find the record regardless of the case.

Either way, put a breakpoint at the line that's causing the problem and use the immediate window to print out some of the fields, especially rs!Landlord_Name, to see if you do have a current record.

Let us know what happens

 

[LucyL]

Still getting the error! I have tried the MoveFirst, Trim and the LIKE statement. I have debugged the statements and heres whats happening. The select statement is searching the database for the edited version or the new value of Landlord_name and not the original name in this statement...

"rs.Open "Select * from Landlord where Landlord_Name='" & cboName.Text & "'", adoconnection, adOpenDynamic, adLockOptimistic"

Therefore, as it does not exist it is throwing up an error.

Any more ideas out there? Maybe there is a way of getting round this?

 

[kbWilliamson]

This is dumb but are you loading the combo box from the same table that you are trying to edit? If so there may be some sort of circular reference problem.

If not, try using a variable to hold trim(comboBox.text) and use the variable to edit the table field.

When desperation(sp?) sets in..............

 

[Foada]

I am not sure where you are trying to do your editing but in the first posting you are setting your recordset to nothing which(from what I am understanding) is what you are trying to edit, which of coarse you cannot as you have set it to nothing. Am I way off on what you are trying to edit? Anything is possible, the problem is I only have one lifetime.

 

[Foada]

One other idea. Try this instead if the code posted first is for the update and not the load.

rs("Landlord_Name&quot = cboName.Text
rs("Address&quot = txtAddr.Text
rs("Tel_No&quot = txtNum.Text
rs("Mobile_No&quot = txMobile.Text

Anything is possible, the problem is I only have one lifetime.

 

[rarubio1]

Make the following changes to your code. This should do the trick!!!!

Dim adoconnection As ADODB.connection
Set adoconnection = New ADODB.connection

adoconnection.Open ("Provider=Microsoft.jet.oledb.4.0;" & "Data Source = h:\NewDatabase.mdb&quot

Set rs = New ADODB.Recordset
rs.Open "Select * from Landlord where Landlord_Name='" & cboName.Text & "'", adoconnection, adOpenDynamic, adLockOptimistic

rs.update
With rs
rs!Landlord_Name = cboName.Text
rs!Address = txtAddr.Text
rs!Tel_No = txtNum.Text
rs!Mobile_No = txMobile.Text
End With

rs.UpdateBatch

adoconnection.Close
Set rs = Nothing

 

[engineer2100]

Hi,

The whole problem that lies here is that you are basing your search on the name i.e treat name as a key to read the data from the database (that what I think you are doing and if you are, then ????).

What could have been happening is that when you try to change the name and then issue an update, it would search for a record with that name (the old name that existed before you changing it), and since it does not find one(as it has been updated) raises an error.

Try storing the original value in the combo box, before editing, into some temporary variable (say tmp) , and then change the Select Statement like this

"Select * from Landlord where Landlord_Name='" & tmp & "'"

Hope this helps.

Engi