ACCESS 2000 - Error in finding a field

If you are running this code behind the frmPurchaseOrder form, then...

Private Sub cmbJobNo_Enter()
    Me!txtContractAmount = 0
    Me!txtAmountPaid = 0
    Me!txtBalance = 0
End Sub

I think I will set my mail client to refresh at 1 second intervals, lol ....

mx

Is txtContractAmount a calculated field that is calculated in a query, perhaps?

Suddenly I recalled that I did not state that this sub is being run from cmdJobNo in a subform!!!!

Sorry I didn't mention that, Jim.  

If the code is in a subform, and all the txtStuff is in a parent form, then...

Me.Parent!txtContractAmount = 0
etc.

New error message:
   Microsoft (perhaps Mr. Ballmer himself) can't find the field "Forms" referred to in your expression.

Here is the modified code:

    Me.[Form_frmPurchaseOrder]!txtContractAmount = 0   ***
    Me.[frmPurchaseOrder]!txtAmountPaid = 0
    Me.[frmPurchaseOrder]!txtBalance = 0

After it blew up the first time in this version I changed the first line to start with Form_  just because the list of forms in the project window addressed this frmPurchaseOrder that way.
jc

adraqhn - txtContractAmount is a referenced field: =[Forms]![frmPurchaseOrder]![AP_Job SubForm].Form!txtAmount

MX - I don't need you anymore.  I now know everything there is to know about queries!!!!

jc

"You can't assign a value to this object (highlighting the first of the 3 objects.)"

This can occur for the following reasons:

1) The control (text box) you are referencing is a calculated control.
2) The Control Source attached to the control you are referencing is itself a calculated field in the Record Source for the form/subform.
3) The Record Source itself is a non-updatable query - which could be for several reasons.
4) Evil forces from outer space.

Which one is it Jack?

mx

just for giggles, try this:

Forms!frmPurchaseOrder.Setfocus
Forms!frmPurchaseOrder.txtContractAmount.setfocus
Forms!frmPurchaseOrder.txtContractAmount = 0

Which line does it break on?

-a

MX,

Well, perhaps I do NEED you!!  lol lol lol

It broke on each of these lines when they were first in the list.

    Me.[Form_frmPurchaseOrder]!txtContractAmount = 0
    Me.[frmPurchaseOrder]!txtAmountPaid = 0
    [Forms]![frmPurchaseOrder]!txtBalance = 0

I am really stumped with this one.  As usual.  (I have to be gone for about 15-20 minutes, perhaps less.  Then I will try your suggestion.  
Jack

Jack ... have we established *why* ... the fields are *not* updatable ??

mx

Where is the code being run from?
And Where are the textboxes located?

code is being run from the subform. textboxes are on the main form. (from prior posts)

jimhorn has your answer already:

https://www.experts-exchange.com/questions/22857945/ACCESS-2000-Error-in-finding-a-field.html#19974560

MX,

It liked the first two lines but broke on the third.  

"Runtime error -2147352567  (80020009)
You can't assign a value to this object."

I pasted in your suggestion:
Forms!frmPurchaseOrder.Setfocus
Forms!frmPurchaseOrder.txtContractAmount.setfocus
Forms!frmPurchaseOrder.txtContractAmount = 0

As I said, it got by the first two lines and then busted on the last line.

Then an interesting thing happened.  I then changed the name of what was the control txtBalance and pulled down the Toolbox and created a new txtBalance changing only the name property to txtBalance.

I then changed the names of txtContractAmount in your 2 lines above to txtBalance.  So the fourth line became  Me.[Form_frmPurchaseOrder]!txtContractAmount = 0   - and that is where it broke.

So this is what it looked like when it broke on the fourth line.

Forms!frmPurchaseOrder.SetFocus
Forms!frmPurchaseOrder.txtBalance.SetFocus
Forms!frmPurchaseOrder.txtBalance = 0
    Me.[Form_frmPurchaseOrder]!txtContractAmount = 0
    Me.[frmPurchaseOrder]!txtAmountPaid = 0
    Me.[frmPurchaseOrder]!txtBalance = 0

MX,

So I changed the code to look like this - and it broke on the fourth line.  (I am now going to make new text boxes for the last two.

Forms!frmPurchaseOrder.SetFocus
Forms!frmPurchaseOrder.txtBalance.SetFocus
Forms!frmPurchaseOrder.txtBalance = 0
    Forms!frmPurchaseOrder!txtContractAmount = 0      ***  Broke here
    Forms!frmPurchaseOrder!txtAmountPaid = 0
    Forms!frmPurchaseOrder!txtBalance = 0

Since txtContractAmount is bound to another field, clear the field that it is bound to instead of txtContractAmount
ie:
[Forms]![frmPurchaseOrder]![AP_Job SubForm].Form!txtAmount = 0

Then try using Me.Parent.Control name to refer to controls on the mainform through subform code

So something like this:

Me.Parent.txtBalance = 0
Me.Parent.[AP_Job SubForm].Form!txtAmount = 0
Me.Parent.txtAmountPaid = 0
Me.Parent.frmPurchaseOrder!txtBalance = 0

I have now made new text boxes for all three controls using the names

txtContractAmount
txtAmountPaid
txtBalance

and the code you suggested worked perfectly.  And I do believe that is the code I originally started with early today when I encountered an error the first time.

Now those original text boxes had been working.  Then I sent the MDB to the client.  For another problem they sent the MDB back to me.  Then this group of text boxes started blowing up.  And the client didn't do anything to the code or the controls.

I just don't understand.  But this did make me nuts.
jack

mbizup,

As I said, I don't understand - and I am now officially nuts.

Here is what I ran the very last time.

Me.Parent.[AP_Job SubForm].Form!txtAmount = 0
'Me.Parent.SetFocus
'Me.Parent.txtBalance.SetFocus
Me.Parent.txtBalance = 0
    Me.Parent.txtContractAmount = 0
    Me.Parent.txtAmountPaid = 0

and the time before that.

Me.Parent.[AP_Job SubForm].Form!txtAmount = 0
Me.Parent.SetFocus
Me.Parent.txtBalance.SetFocus
Me.Parent.txtBalance = 0
    Me.Parent.txtContractAmount = 0
    Me.Parent.txtAmountPaid = 0

In both cases it BROKE on line 4.

What is the control source of txtContractAmount?

From your answer to adraughn, it is another textbox.
> =[Forms]![frmPurchaseOrder]![AP_Job SubForm].Form!txtAmount

You need to set that textbox = 0, not txtContractAmount directly.

Try this


Me.Parent.[AP_Job SubForm].Form!txtAmount = 0  '<--- set txtAmount, which controls txtContractAmount
Me.Parent.SetFocus
Me.Parent.txtBalance.SetFocus
Me.Parent.txtBalance = 0
'   Me.Parent.txtContractAmount = 0  <--- don't set this directly
    Me.Parent.txtAmountPaid = 0

mizup

This is exactly what I now have.  It blows up on line 3 (Balance).

    Me.Parent.[AP_Job SubForm].Form!txtAmount = 0
    Me.Parent.txtAmountPaid = 0
    Me.Parent.txtBalance = 0

Balance has a Control Source of:  =[txtContractAmount]-[txtAmountPaid]

Why is it that the system does not like setting a control to zero when there is a control source?  I almost understand that but it some way the reason eludes me.

And how do I get to set Balance to zero which is really necessary?

mbizup,

I copied your two lines of code, which are now the only lines of code in the sub procedure.

The procedure is in  cmbJobNo_Enter()  
JobNo is the first combo box in a new line of the line item set (continuous form) in the subform.

The results of the code is:
   ContractAmount  is  #Error
   AmountPaid         is  $0.0
   Balance               is  $4401.00   which is correct from the previous line.  So it didn't change.

The #Error for ContractAmount actually shows up when the form opens and has data in it.

The #Error also shows up when the form opens with NO DATA.

So I have to find that.

I found and corrected that.  When I go to a new line, the BALANCE still shows the $4401 from the previous line.  And that's not good.

Oh my.

mbizup -

OK, I GOT IT!

 I executed this code:

Private Sub cmbJobNo_Enter()

    Me.Parent.[AP_Job SubForm].Form!txtAmount = 0
    Me.Parent.txtAmountPaid = 0
   
    Me.Parent.txtBalance.ControlSource = ""
    Me.Parent.txtBalance = 0
    Me.Parent.txtBalance.ControlSource = "=[txtContractAmount]-[txtAmountPaid]"
 
End Sub

So the Balance is now set to zero and it still has it's ControlSource.

AND IT WORKS!  After all these hours and an incredible number of messages.

Moreover, I truly thank you for your help with this.  Your suggestions made it possible.

One more change... I was under the impression (mistaken, I think) that you were dealing with two subforms.

Change the subform reference, since the code is on the same form:


    Me.txtAmount = 0   <---- I think this is the correct reference.
    Me.Parent.txtAmountPaid = 0

See, you really don't need me ....

mx

I'm surprised you needed the last few lines of code to make this work.   Give this a try as well:

    Me.txtAmount = 0
    Me.Parent.txtAmountPaid = 0
    Me.Parent.txtBalance.Requery    '<---- this is effectively what you are doing by resetting the control source.

mbizup

Yes, only one subform.  I will make the change you suggest - and that really is correct.  I should have noticed  earlier.

BTW -  did you agree with my solution to the Balance problem?  Is there another, better way?


MX,
YEAH!  THAT'S TRUE!   (but please don't go away.)

jack

> did you agree with my solution to the Balance problem?
It works... But try simply requerying  the field instead.

not too worry Jack ... I'll be just around the corner (er, across the ocean) !

mx

I tried the Requery but the field still had the $4400 data from the previous line.

Thanks though.

jack