Form Selections to convert on table?

[cougercruiser]

Hi Guys,



I've created a ComboBox and would like one of my selections: "Met" to be converted into a number value: "10" within the related field on the table.

I've come up with the expression: =IF([Initial Review Q1]="Met",0,10)

Control Source accepts the expression, however when I go back to Form View and select "Met" access chimes and states: "Control can't be edited it's bound to the expression =if([Initial Review Q1]="met",0,10)"



I apologize, I am still in the early stages of learning access. Hopefully this video can shed some more light on my problem:

[June7]

Cross post at http://forums.aspfree.com/microsoft-...on-536864.html

[istari88]

This is a good place for a data macro, new to Access 2010. A data macro acts like a trigger on the table

Maybe VBA code like:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "tblName", , , , adCmdTable
End With
With rs
.MoveFirst
.Find "RecordID='" & variable & "'"
!New_value = 10
.Update
' If .EOF Then
' MsgBox "Not found"
' Exit Sub
' End If
Me.txtCarPrice = rs!CarPrice
End With
rs.Close

--hope it helps!
--Phil

[istari88]

To add to my post:
1) Use the combobox_after_update event to place all the code as below

2) Save the record so that it has a record ID
docmd.runcmd.....
3) Place the recordID in a variable
dim var as integer
var=RecordID


4) Open table, find record, add "10" to table at the right record
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "tblName", , , , adCmdTable
End With

With rs
.MoveFirst
.Find "RecordID='" & var & "'"
If .EOF then exit sub
!New_value = 10 'New_value is the field name of table
.Update


End With
set rs=nothing


==Give that a try, let me know if this helps or I can add to it
-Phil