Editing a record using recordset.

[VegaLA]

Hi all,
I am trying with little success to update the fields of a table on a record that already exists. However, after running the code from a command button on the form the record's fields are still blank. Reason why I am doing it this way is because once the user has completed entering data for a few fields the rest of the fields can be automatically imported from a query at the press of a button. Around ten fields are populated this way but I have cut the code down to two as an example of how I have coded this:-

Dim db As Database
Dim rs As DAO.Recordset
Dim curTest01 As Currency
Dim curTest02 As Currency


Set db = CurrentDb
Set rs = db.OpenRecordset("tblCosts")

curTest01 = DLookup("[FieldValue01]", "qryQuery", "[FieldValue01]<> Null")
curTest02 = DLookup("[FieldValue02]", "qryQuery", "[FieldValue02]<> Null")

'These fields are created from the results of the fields above

With rs
.Edit
!tblField01 = curTest01
!tblField02 = curTest02
.Update
.Bookmark = .LastModified
.Close
End With

db.Close
Me.Refresh

Can anyone see why this will not populate the table ? If I use this code with .addnew the data populates but I want it to add to the already exisiting record, not to create a new record.
I tried using DoCmd.RunCommand acCmdSaveRecord at the end of the code but got error "runtime error 2046 the command or action 'saverecord' isn't available now" but I should not have to save the record manually anyway.

Any ideas why this code will not populate the record ?

Thanks in advance,
Mitch...

 

[RuralGuy]

Have you tried "Not IsNull([FieldValue01])"?

 

[VegaLA]

Cheers RuralGuy.

The Not IsNull method won't work, it errors up on that line but it works fine using my method. It also worked using my method for .addnew, its only since changing it in favour of .edit

Any ideas ?

Mitch...

 

[RuralGuy]

Have you tried a DynaSet instead of a table?
Set rs = db.OpenRecordset("tblCosts", dbOpenDynaset)
I take it this table has only one record?

 

[VegaLA]

Hi again.
Tried adding dbOpenDynaset but still no joy.
I can't firgure out why it will not add the data to the fields ?????

The table itself already has a lot of data, it was pulled in from an existing Excel sheet, i'm just tring to automate the process.
The form opens and displays the last record's data. If there is data missing (Theer will be since only the date field is populated along with a field they input manually) then all they need do is press a button and the code above should pull in the data and refresh the form to show the user it is now complete.

Like I say using .addnew works fine but its not a new record I want to populate the last record in the table.

Mitch...

 

[RuralGuy]

Add:
.MoveLast
.Edit
Then toss on some MsgBoxes or Debug.Prints so you can see what is happening whan you single step the code.

 

[VegaLA]

Hi Rural.
I've decided to use .addnew instead.
I noticed that if I cleared the table down, opened the form, hit the button, i'd get the message that theer is no record to edit, then hit the prev record button, then hit the import button it would work, but only if it's the first record in the table. I think this must be down to how I display the data on form open so i'm rethinkign my plan of action.

Thanks for your time, I appreciate it.

Mitch....

 

[RuralGuy]

Sorry I couldn't be of more help Mitch.