Combo Box on Calculated Field

[access?]

Example

I have 2 fields in a query:
A = [null]
B = "Test"

In a form I have a field (C) where the control source is IIf(IsNull(A),B,A)

What I'd like to do is when a new value is selected in the combo box it writes that value to the A field and then the field recalculates

The form field should be showing "Test" now based on the above logic.

The field is a combo box which has a row source as
SELECT DISTINCT A from Table which gives possible choices of
- Detail1
- Detail2
- Detail3

If I choose Detail2 I currently get the error "Field is currently based on an expression and cannot be edited".

What I'd like to have happen is that the value I choose be written to field A and then field C would recalculate based on the above logic to then show "Detail2"

Does anyone have any ideas on how this can be done using access settings or VBA?

Thanks

 

[lcook1974]

How about using the after update event?

docmd.runsql "INSERT INTO table_name (column1) VALUES (" & me.cmboxvalue1 & ")"

me.cmbox.requery

 

[access?]

It seems that none of the events fire until the error is given. If you make any calculated field a combo box whether the field is calculated within the field itself or in a query as soon as you select a value from the dropdown of the combo box it gives the follwowing error in the status bar

"Field '[field]' is based on an expression and cannot be edited"

 

[lcook1974]

could you post your DB?

 

[access?]

I cannot post the db because I am using SQL as a backend. Can are you able to recreate my error based on the earlier reply?

 

[lcook1974]

Could you clarify where A= null and B="test" comes from?

use this to recreate your db...and I'll have a look.

 

[access?]

Here is an example database I've created. If you open the form and use the combo box in field C you will see the issue in the status bar.

 

[lcook1974]

Could you save it in 2003? I don't have anything higher on my home PC.