Error : You cannot assign a value to this object

[luzz]

Hello guys, I keep getting the above error whenever I want to "Edit" my record on my Access form. The error message highlight the code where I declare
me.txtMXDPO = .Fields("MXDPO")

I concatenate the value of txtCode (MXD), txtYearCode (17/) and txtPOnumber (101) in MXDPO textbox.

I also attached a image of my form and an image of my code
Thankyou

 

[The_Doc_Man]

If the other references in your code execute correctly but the sequence stops on that line, then there is something that we can't see from your other presentation.

I am thinking that there is something special about Me.txtMXDPO, something that makes it different from Me.txtYearCode or Me.txtPONumber, both of which are the targets of a value assignment above your problem line. Otherwise this would make no sense at all.

The possibilities that come to mind regarding Me.txtMXDPO

- not in scope at the moment because it is in a different section on the form (though I would have expected a different error)

- set to ".Locked=True" or ".Enabled=False" (but again, I would expect a different error)

- maybe was defined in a way such that it does not have a .Value property.

I'm betting on the last one. Just because you call it txtXYZ doesn't mean it is a text box. Names are arbitrary. So... compare the three fields I named on your form and try to identify the differences. This is actually easy in a sense because the odds are you could move that offending line further down in the sequence to see if everything else is working. So you could debug the rest of the code while thinking about this one.

My problem in being more specific is that we can't see the actual form so those would be hard to differentiate what is wrong with that one field. Open the form in Design Mode. Select the control with the required name. Look at its properties.

 

[MarkK]

Where do you do this?

I concatenate the value of txtCode (MXD), txtYearCode (17/) and txtPOnumber (101) in MXDPO textbox.

You cannot set the value of a textbox if it already has an expression in its ControlSource property.

Mark

 

[Pat Hartman]

1. You cannot update a calculated columns
2. Why is the form unbound? Access is a RAD tool. If you are not using what makes it RAD, you have all the bad without any of the good.

 

[luzz]

If the other references in your code execute correctly but the sequence stops on that line, then there is something that we can't see from your other presentation.

I am thinking that there is something special about Me.txtMXDPO, something that makes it different from Me.txtYearCode or Me.txtPONumber, both of which are the targets of a value assignment above your problem line. Otherwise this would make no sense at all.

The possibilities that come to mind regarding Me.txtMXDPO

- not in scope at the moment because it is in a different section on the form (though I would have expected a different error)

- set to ".Locked=True" or ".Enabled=False" (but again, I would expect a different error)

- maybe was defined in a way such that it does not have a .Value property.

I'm betting on the last one. Just because you call it txtXYZ doesn't mean it is a text box. Names are arbitrary. So... compare the three fields I named on your form and try to identify the differences. This is actually easy in a sense because the odds are you could move that offending line further down in the sequence to see if everything else is working. So you could debug the rest of the code while thinking about this one.

My problem in being more specific is that we can't see the actual form so those would be hard to differentiate what is wrong with that one field. Open the form in Design Mode. Select the control with the required name. Look at its properties.

Thank you for replying! If i comment out the line where it state Me.txtMXDPO = .Fields("MXDPO")
my code is able to run perfectly just that when iedit my a recordset the MXDPO will be blank
I have attached an image of my form in design view and my code.

 

[luzz]

Where do you do this?


You cannot set the value of a textbox if it already has an expression in its ControlSource property.

Mark

Thankyou for replying! If i dont concatenate this three field as one, i wont be able to store the MXDPO together in my table

 

[luzz]

1. You cannot update a calculated columns
2. Why is the form unbound? Access is a RAD tool. If you are not using what makes it RAD, you have all the bad without any of the good.

Thank you for replying!

1. Is concatenating 3 different fields into 1 textbox consider as a calculated column? Because there is no calculation or formula involve.

2. I do not understand what you mean by RAD, can you explain more about it?
Thank you

 

[Minty]

You are storing redundant data, as you have explained MXDPO is a concatenation of three existing fields. And yes that would be considered a calculated field.

Don't store it. You can calculate it whenever you need it, and if any of the parts that make it up change your calculation will automagically reflect those changes, without you trying to have to remember to store the new result again.

 

[luzz]

You are storing redundant data, as you have explained MXDPO is a concatenation of three existing fields. And yes that would be considered a calculated field.

Don't store it. You can calculate it whenever you need it, and if any of the parts that make it up change your calculation will automagically reflect those changes, without you trying to have to remember to store the new result again.

I will need to concatenated the 3 together to form the MXDPO as i need it for my report and storing of my recordset. I use MXDPO to prompt my user in the report. Or do you have a better suggestion?

 

[Minty]

I've just realised this is the same PO number that we have discussed before, in another thread.

I think you were given a number of sensible suggestions, and alternative approaches then, so I'll bow out now. Suffice to say you can already concatenate it, and therefore group by it and display on a report, so just expand that process out.

 

[The_Doc_Man]

Just as a point of clarification:

If the field's value is defined as "= variablename" then it is an expression even if there are no operators and only one variable. It is the equals-sign that triggers the state of being an expression.

Regarding concatenation for a report, you can define the report field to be concatenated even if the form sees three individual fields. A query can also do that for you since a query can do that concatenation on the fly.

 

[Pat Hartman]

The_Doc_Man explained calculated columns.

RAD = Rapid Application Development.

If you bind your forms, none of this code is necessary. You will however still need any validation code that ensures proper values. Validation code belongs in the FORM's BeforeUpdate event. If an error is discovered, prevent the data from being saved by using the expression -
Cancel = True

This tells Access to leave the form dirty but to not save it. The user can then use your error message as a guide and correct the invalid entry or enter something in a required field that was left blank.