Record editting problem

[Schaap]

I still got an editting problem !

If I edit field offer or Photo I get an error : "error 438, this property or methode will not be supported by this object".
But I also edit field PowerPointSlide the same way and that's working OK !
See the code:

Private Sub Knop82_Click()

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("select InvestmentName, BudgetYear, InvestmentPurpose, Necessity, Offer, EstimatedPrice, PowerPointSlide, Photo, Capital, Expense, EEA, ROI, PaccarProjectNumber, Agreed, CarryOver, SbonNumber, SbonDate, FinishedSbonDate, ProductReceived, ChangeDate, ChangedBy From Investmentplanning where IndexNumber=" & Index)

Private Sub Knop82_Click()

With Rs
.Edit
BudgetYear = Me.txtBudgetY3
InvestmentPurpose = Me.txtInvestmentPurpose3
Necessity = Me.txtNecessity3
EstimatedPrice = Me.txtEstimatedPrice3
Capital = Me.txtCapital3
Expense = Me.txtExpense3
-> Offer = Offerte
PowerPointSlide = Slide
-> Photo = Foto
EEA = Me.txtEEA3
ROI = Me.txtROI3
ChangeDate = Date
ChangedBy = username
.Update
Rs.Close
Set Rs = Nothing
MsgBox ("Saved Application Form")
DoCmd.Close
DoCmd.OpenForm "blablabla", acNormal
End With

End Sub

If I put for every field (between edit and update) also a "!" like !Offer = Offerte. But when I do that I get a writing conflict : "When editting the record, this record is changed by another user.If the record will be saved, the changes of the other user will be lost.I have the possibility to save the record, copy it to the background or ignore the changes"

Offerte, Slide and Foto are public Strings and are declared in the database as hyperlink

So what do I do wrong ? Help needed !

 

[RoyVidar]

1 - without prefixing the field names with ! (or other notation indication you are working with fields), Access will think it is either variables or form controls. If yo have form controls with those names, it will probably update them. If you don't have form controls or variables with that name, then I suspect two things:

- if Option Explicit is present as the second line of the module (recommended - enforces variable declaration), you should get compile errors
- if Option Explicit is not present, then it will probably seem to work, but won't do anything (just assign values to the undeclared variables)

The 438 is probably related to that, trying to assign a value to a control that either doesn't support the .value property, or it might be calculated...

2 - If the form where you use this is bound, then you are two users on the same recordset - one user thru the form and one user thru the code, and both of you are trying to edit the same record. You could try to ensure the record is saved in the form prior to invoking this routine (docmd.runcommand accmdsaverecord), or decide whether you want to edit thru the bound form, or unbind it and do all editing/navigation/populating... thru code.

Roy-Vidar

 

[Schaap]

The form (with save button) is not bounded.
When I load the form I'm using the next code :

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("Select * FROM InvestmentPlanning WHERE IndexNumber=" & Index)
Me.txtInvestNR3 = Rs!InvestNumber
Me.txtInvestDate3 = Rs!InvestDate
Me.txtApplicantName3 = Rs!ApplicantName
Me.txtInvestmentName3 = Rs!InvestmentName
Rs.Close
Set Rs = Nothing
End sub
So everything is done by code, the user fills in a textfield.When the savebutton is clicked these fields will be written/edited to the particular fields of the database like !Offer = Offerte.
Option explicit is not been used.
But still got the same writing conflict !?!
It seems that this conflict is noticed when the form will be closed ! When I look into the dbase all new data is written into the wright record of the dbase.

 

[RoyVidar]

Use Option Explicit - it usually reduces headaches.

OK - so you have now altered the code to include a reference to the fields, and you now get the write conflict thingie. It does imply that more than one (user, connection, recordset) is trying to update the same record.

The usual way that occurs is if the form is bound, and you try to edit the same record thru both the forms save operation and thru the recordset. Ensure there is no recordsource of the form, and that no controls have controlsource. Or it might be you're performing an update in different events. Or there might be other users trying to update the same recordset.

Roy-Vidar

 

[Schaap]

I used your suggested code :
docmd.runcommand accmdsaverecord before Rs.Close and Set Rs = Nothing of the first openrecordset when loading the form (see also my 2d thread).

Now I don't get an writing conflict anymore, But I still don't understand it because at loading and editing the form I close both recordsets (the same recortdset) like :
Rs.Close
Set Rs = Nothing
like it should be !

 

[RoyVidar]

Docmd.RunCommand accmdsaverecord, as I've understood, only works on bound forms, so if that's what's "solved" the issue, I'll again ask you to have a look at the recordset property of the form, and the controlsources of the controls. It seems you're working with a bound form.

Roy-Vidar