Control can't be edited; it's bound to an expression...

[buratti]

Are there any workarounds to not being able to edit a field based on an expression? Let me explain my situation… I have a form based on a query created from 2 tables. The control source of one of my fields is: =IIf([BillCodeID]=1,[CustomAmt],[Amount]). I understand that you cannot update data in a field/control whose control source is based on an expression. And I understand the logistics of it, if the expression is something like =[field1]&” “&[field2], but my actual expression can be expressed as “if BillcodeID is 1 then I want the control source to be [customAmt] otherwise I want the control source to be [Amount]. Logically speaking, I should be able to update these fields. But I guess Access just sees that there is an expression and locks me out of editing that field regardless of the type of expression. So, is there any method of overriding this ‘locking” of this field?

 

[boblarson]

Don't set the control source to be a formula like that. Use code - so in the form's On Current event you could use:

If Len(Me.ControlNameHere & vbNullString) = 0 And Me!BillCodeID <> 1 Then
   Me.ControlNameHere = Me!Amount
End If

Then if you move between records it will set the value for you (as long as Amount exists as a field) to Amount IF and only IF the field doesn't already have something in it AND the BillCodeID doesn't equal 1. Otherwise it leaves it alone and will be blank if the BillCodeID is something other than one. And then you can fill it in.

Then bind this to an actual field (if you want the custom values stored).

 

[buratti]

Thanks... I tried your code and it "kinda" worked. I bound my field/control to my CustomAmt field, but for any record that did NOT have a custom amount it copied the standard amount field to the custom amount field, technically working, but having nnecessary data. Then I got to thinking and I modified it to the following...

If Me!BillCodeID <> 1 Then
Me.CustomAmount.ControlSource = "Amount"
Else
Me.CustomAmount.ControlSource = "CustomAmt"
End If

This is working perfectly, but it seems so much more simpler than your idea. Am I missing something, or could this really be that easy?

 

[boblarson]

I was under the impression that you would save the amount, even if it was a custom amount, in the SAME field. So you wouldn't have to worry about it and it would be properly normalized.

 

[buratti]

Well I was going to ask that question about normalization, but then I would be veering off the main topic of this question. But since it is kinda solved now I guess I can. The standard amount is already saved in another table, and I figured I would save the custom amount in the customers table. Here is a short description of my structure, so can you tell me if it is correct?
Here are my tables and fields:

Customers: CustomerID, Name, Address, etc..., RateID, CustomAmt
Rates: RateID, RateName, Amount

My Customer Details form is based on a query from the 2 tables listed above. I list the customer info and their rate. This is where my original question came into play. If the customer had a rateID of 1 then the control source for their rate would be [CustomAmt], otherwise the control source is the amount taken from the Rate table ([Amount]). Is this correct normalization, or is copying the amount to the customers table the right way to do it?

 

[boblarson]

For correct normalization you should be doing this (especially if you want to retain history):

Customers
CustomerID
CustomerName (do NOT use NAME as a field or object name)
Address
etc.

Rates
RateID
RateName
Amount

CustomersRates
CustomerRateID - Autonumber (PK)
CustomerID - Long Integer (FK)
RateID - Long Integer (FK)
EffectiveDate - Date (the date the rate goes into effect)

Then you can enter the rate required and know when it was effective so any transactions between the dates can be used with the appropriate rates or you can just know what was and when.

 

[buratti]

Customers
CustomerID
CustomerName (do NOT use NAME as a field or object name)
Address
etc.

I didn't use the "Name" as the name of the field, I was just typing quickly in my last post.

Thanks for everything, and I dont want to be a pain now but I have more information that I left out in my last post that may or may not change your suggestions. I also have a charge table that holds all the charge history for the customers with the fields: ChargeID, CustomerID, ChargeDate, RateID, Amount, PeriodFrom, PeriodTo. When adding records to this table, I actually COPY the amount from rather the rate table, or if the customer has a custom rate, the customers table to the Charges table. I figured copying the "rate at that time" would give the correct rate amount, at that time and keep a history of past rates in case the customers rate ever changes.

Now, dont get me wrong, and am I in no way questioning your methods, and if you say that is the wright way to do it then I trust you, but with all the information I just supplied I just cant figure out why I would need the CustomersRates table, when essentially I can just include those fields in the customers table. (Or just a thought that came to me right now, is your suggested CustomersRates table just your version of my Charges table?) Can you possibly explain the method behind that a little more please?


Just when I think I am getting good at this I find out I am still doing things wrong

 

[boblarson]

My suggestion was because the information that was given showed one value at a time stored with the customer and didn't include the charge table information. You are fine to do that, I was just concerned that you were 1) losing history and 2) Using more than one field to store the rate.

So, no worries, if what you have works then it seems fine to me.

 

[buratti]

Ok thanks for all your help.