Why does code work on ertain subs but not on other?

[Freddie81]

Hi Guys

I've noticed that In some situations code work and then in other the same doesnt work. For example:

In my PaymntsF form I have this:
Private Sub InvoiceID_AfterUpdate()
Dim TmpCustID As Integer

TmpCustID = Me!InvoiceID.Column(1)
Me!txtCustomerID = TmpCustID
End Sub

Here it works fine. I am using the InvoiceID from the previous form to give a textbox on the current form a value. Then this value is saved to the table. The textbox txtCustomerID is bound to CustomerID in the table PaymentsT.

Then If I do the same thing in a different form I get an error that I cannot assign a value to this object.



Private Sub Form_Open(Cancel As Integer)
Dim TmpInv As Integer
Dim TmpCust As Integer
TmpInv = Forms!InvoiceDataEntryF!InvoiceID
TmpCust = Forms!InvoiceDataEntryF!CustomerID
Me.txtInvoiceID = TmpInv
Me.txtCustomerID = TmpCust
End Sub

On second code the textboxes both populate with the values but it wont save it to the source fields on the table

Does anyone now why Access does this?

I want to save the two text boxes value to the 2 fields on the table.

[Minty]

Try it on form load, not form open, then add

If Me.Dirty then Me.Dirty = False

to save the record.

[June7]

Some things cannot be done in some events. Likely the controls are not yet available when the code runs in Open event.

Use Load event.

[Freddie81]

Hi Minty

Could you explain what does the code mean pls? What is Dirty r rather what does it mean on a form?

[Freddie81]

Hi June7... that actualy makes sense even to me lol. I'll try that

[Minty]

When any data changes on a form the is a flag to indicate the data is changed - this is the Dirty property.
Me.Dirty will be True if any data has changed, you can force that data to be saved by Setting the Dirty property to False.

And as both June and I said in the earlier post - move the event to the Load event of the form.

[Freddie81]

Ooooh okay..thank you now I understand the Dirty concept

I have changed the code from open to load event and no error anymore. However, the textboxes are not being populated. I tested the TmpInv and TmpCust variables with message boxes and they do get the correct values...just the textboxes don't show it so the table is not saving it (yet)

[Freddie81]

I removed the source from both text boxes to make them unbound and now they show the values, so obviously they don't update the values in table

[Minty]

Is this form opening on a new record by any chance ?
What is the forms record source ?

Can you post up a stripped down version of your database?

[Freddie81]

Yes, the form is opening to a new record. The form source is a table called PaymentsT.

Every payment a client makes is captured to this table. It has the invoiceID, CustomerID, Date, payment method and amount as fields. All the fields work except the invoiceId and the customerid which both are supposed to be acquired from the form and thn saved. Ive got a Form for the invoice (InvoiceDataEntryF) that has a button for payment. when clicked it opens a Form for the payment so that the payment is for this specific invoice only.

[Missinglinq]

...I removed the source from both text boxes to make them unbound and now they show the values...(

That sounds like you had the values assigned by having an expression in the Control's Control Source Property...is that correct? That would be responsible for not allowing it to be set using code in the Form_Load event.

Also,

...I've noticed that In some situations code work and then in other the same doesnt work...I get an error that I cannot assign a value to this object...

Once again, do you (or have you, in the past) had the values assigned by having an expression in the Control's Control Source Property?

Another possibility involves the Datatype of the Field that the Control is Bound to. Many times things like ID numbers are defined as Autotype Fields, and thus cannot be set by VBA code or thru their Control Sources.

Linq ;0)>

[Freddie81]

No there was no expression in the data source field, it was directly linked to InvoiceID and CustomerID. The fields on the table are both number and not autonumber.

[Minty]

If the form is only ever used for new records then I would set the forms default values to the calling form underlying fields

Me.txtInvoiceID.DefaultValue = Forms!InvoiceDataEntryF!InvoiceID

Or better still use the open args property in the form load event, to pass the default values so that it's not tied to one calling form.

[Freddie81]

That default value code works like a charm. Many Thanks