Form Field will not display the string result of a drop down

[atrium]

I have several fields in a table that are subject to the resulting recordId that displays the string shown on the drop down. They all work as they should except one and for the life of me I can't find the reason. I have checked the property sheet for all of them and all of the properties are the same (other than the obvious) as the one that only displays the recordId not the description related to that ID

Private Sub CollStatusFld_Change()
MsgBox "The CollStatus selected is = " & Me.CollStatus
Me.CollStatusFld = DLookup("CollStatus", "CollStatus", "[CollStatusId] = " & Me.CollStatus)
End Sub

The attached files are
CollStatus001 = Properties format column
CollStatus002 = Properties Data column
CollStatus003 = Properties Event column
CollStatus004 = Properties Other column
CollStatus005 = Table Entry
CollStatus006 = Display Form
CollStatus007 =Update Form
CollStatus008 =CollStatus Table


Any suggestions would be greatly appreciated

 

[June7]

Why don't you provide db for analysis?

 

[arnelgp]

i think you have them in reversed order in your code?

Private Sub CollStatusFld_Change()
MsgBox "The CollStatus selected is = " & Me.CollStatus
Me.CollStatusFld = DLookup("CollStatus", "CollStatus", "[CollStatusId] = " & Me.CollStatus)
End Sub

should be...

Private Sub CollStatusFld_Change()
MsgBox "The CollStatus selected is = " & Val(Me.CollStatusFld.Text & "")
Me.CollStatus = DLookup("CollStatus", "CollStatus", "[CollStatusId] = " & Val(Me.CollStatusFld.Text & ""))
End Sub

Also, using Combobox for your field will make your coding easier.

 

[The_Doc_Man]

After looking at your presentations and reading your description, I don't understand your problem. We usually like to see things like this: (a) what were you trying to do in general? (b) what were you doing it with? (c) what did you actually get? (d) what would you have expected to get?

I'm not seeing the problem yet. Not denying you have one... but I don't see it. If I can't see it, I can't fix it.

 

[gemma-the-husky]

I looked at this again on a computer rather than phone.
I am confused between image 6 and 7. Are you trying to use a combo box, or just lookup a value?

Private Sub CollStatusFld_Change()
MsgBox "The CollStatus selected is = " & Me.CollStatus
Me.CollStatusFld = DLookup("CollStatus", "CollStatus", "[CollStatusId] = " & Me.CollStatus)
End Sub

In this code, I don't think you want to change the field in the change event FOR the same field.

In passing, I note you have also mis-spelled guarantor, although that won't affect the database.