Me.field is not recognized

[bane]

I made a form using the wizard which named my text boxes the same name as their control source (RollDate1, RollDate2). After adding code to be run when the use clicks a button, I went back and changed them to txtRollDate1, txtRollDate2 and unbound them. Here is what is supposed to happen when they click the OK button.

Me.RollDate1 = Me.txtRollDate1.Value
Me.RollDate2 = Me.txtRollDate2.Value

The problem is that the coding is not recognizing the Me.RollDate as a field in the table. (I get a "Method or Data Member Not Found" error message) It is working fine for other fields, but not these two. Any suggestions? (My reasoning behind doing this is so that when a user 'accidentally' closes out the form, it doesn't enter false data into the table. Any help would be appreciated. Thanks in advance!

[Scott Price]

I made a form using the wizard which named my text boxes the same name as their control source (RollDate1, RollDate2). After adding code to be run when the use clicks a button, I went back and changed them to txtRollDate1, txtRollDate2 and unbound them. Here is what is supposed to happen when they click the OK button.

Me.RollDate1 = Me.txtRollDate1.Value
Me.RollDate2 = Me.txtRollDate2.Value

The problem is that the coding is not recognizing the Me.RollDate as a field in the table. (I get a "Method or Data Member Not Found" error message) It is working fine for other fields, but not these two. Any suggestions? (My reasoning behind doing this is so that when a user 'accidentally' closes out the form, it doesn't enter false data into the table. Any help would be appreciated. Thanks in advance!

Me.[FieldName] is the method of referring to a control on a form, not a field in a table. So when you renamed the control to txtRollDate1, there no longer exists a control named RollDate1 on your form, and therefore the db cannot find that Data Member.

As for the larger picture, I'm not sure how your reasoning is actually going to work for what you state as the objective. In any case, data validation is better handled in the BeforeUpdate event of the form, where the update can be canceled before the incorrect data is written to the table. Also, if you simply refer a control to itself, the data will still be saved with an 'accidental' closure of the form (I'm not sure if that situation is all that common, either?! )

Regards,
Scott

[bane]

Me.[FieldName] is the method of referring to a control on a form, not a field in a table. So when you renamed the control to txtRollDate1, there no longer exists a control named RollDate1 on your form, and therefore the db cannot find that Data Member.

As for the larger picture, I'm not sure how your reasoning is actually going to work for what you state as the objective. In any case, data validation is better handled in the BeforeUpdate event of the form, where the update can be canceled before the incorrect data is written to the table. Also, if you simply refer a control to itself, the data will still be saved with an 'accidental' closure of the form (I'm not sure if that situation is all that common, either?! )

Regards,
Scott

I'm a little confused. In the many other instances where I have use Me.[FieldName] to refer to a field in a table, it works the way I want it to. The problem lies in that the text field in the form was originally the same name as the field in the table (thank the wizard for that). I also just noticed that the Properties/Methods list doesn't show the new fields that I put in the table. FYI, the Properties/Methods list shows every field in my table (minus the new fields) along with every field in the form.
A bit more explanation on my reasoning - If the form fields are bound to the table and an user puts in a wrong value then closes the form, that wrong value is stored in that field in the table. And depending on which value they enter, it may prevent that record from being accessible by that form again (due to queries being run on opening, etc). So by making the form fields unbound, the only way that information gets to the table is via the code that I am trying to get to run via them hitting the OK button.
If that still does not make sense, let me know and I will try to explain further.
(BTW, I am using Access 2003 and VB 6.5)

[Scott Price]

I'm a little confused. In the many other instances where I have use Me.[FieldName] to refer to a field in a table, it works the way I want it to. The problem lies in that the text field in the form was originally the same name as the field in the table (thank the wizard for that). I also just noticed that the Properties/Methods list doesn't show the new fields that I put in the table. FYI, the Properties/Methods list shows every field in my table (minus the new fields) along with every field in the form.
A bit more explanation on my reasoning - If the form fields are bound to the table and an user puts in a wrong value then closes the form, that wrong value is stored in that field in the table. And depending on which value they enter, it may prevent that record from being accessible by that form again (due to queries being run on opening, etc). So by making the form fields unbound, the only way that information gets to the table is via the code that I am trying to get to run via them hitting the OK button.
If that still does not make sense, let me know and I will try to explain further.
(BTW, I am using Access 2003 and VB 6.5)

I understand what you are saying, however, if you read carefully my earlier post, and if you would explore carefully the other situations, I think you will find that Me.[FieldName] is referring to a control on the form, not directly to the field in the table.

What happens is that the Me keyword in access vba refers to the collection of the currently executing code block. So if you are using this in the code module of a form, it will only refer to controls on the form.

You should look into an update query (i.e. INSERT INTO) to do what you are trying to accomplish. You can run this from vba using the DoCmd.RunSQL command. Access's help file is actually quite helpful to point out the syntax of these two methods.

Good luck!

Regards,
Scott

[Juddles82]

Ok, 9 years too late but I've found a pretty ugly workaround for this one Bane.

Add a new combobox on your form and set your Dlookup to autopopulate this new control on the form, as Scott mentions. Then, once it is working, you can delete the control from the form and the Dlookup will continue to work in VBA.

An inelegant solution but it got me out of a hole so hopefully it can help someone else.

[PhilOfWalton]

Apart from anything else Me.ControlName is OK
Me.FieldName is not.
Forms & Reports have controls, not Fields

Phil

[Seth Schrock]

Me!Field_Name is acceptable however. I use it all the time.

[NeoPa]

@Juddles.
I find an easier solution is to add bound controls to your form that are not visible and reference them instead. I actually have hidden sections for just that purpose on some forms.

Yes. The items you see on forms and reports are controls and not fields, however forms and reports do still have fields related to their Record Sources. These can be referenced from VBA, as Seth indicates. I'm not so sure about accessing them from within other controls on the form though, except where using Aggregate functions like Sum(), Max(), etc.