Field auto-update based on another field value

[MfanyaKitu]

Hi there,

I'm trying to automatically update a field in a table based on what has been inserted in another field. I have been successful in one table but not in another although I have been using the same code, and the same way. Here is the piece of code which is not working;

Dim rsLiability As DAO.Recordset
    Dim strSql As String
 
    strSql = "SELECT txtLiabilityCategory, txtLiabilityName FROM tblLiability;"
    Set rsLiability = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
 
    Do While Not rsLiability.EOF
     If rsLiability!txtLiabilityName = "Dhima/Mkopo wa Muda Mrefu(Zaidi ya mwaka mmoja)" And rsLiability!txtLiabilityCategory = "Madeni" Then
        rsLiability.Edit
        rsLiability!txtLiabilityCategory = "Non-current"
        rsLiability.Update
     ElseIf rsLiability!txtLiabilityName = "Dhima/Mkopo wa Muda Mfupi(Chini ya mwaka mmoja)" And rsLiability!txtLiabilityCategory = "Madeni" Then
        rsLiability.Edit
        rsLiability!txtLiabilityCategory = "Current"
        rsLiability.Update
     End If
        rsLiability.MoveNext
    Loop
 
    'Closing and resetting the recordset
    rsLiability.Close
    Set rsLiability = Nothing

I have attached the code to the OnClose event of the input form. The field that need to be updated (txtLiabilityCa tegory) is defaulted to 'Madeni'.
Any idea of why it doesn't work or other way around to do it better?

[twinnyfo]

So, if I read your code correctly, every time you close your form, you want to cycle through all records in the table in question, then, if txtLiabilityCat egory = "Madeni", and also meets one of the other two criteria you have listed, you want to adjust the value for txtLiabilityCat egory accordingly? I just restate, so that I am clear in knowing what you want to do.

One trouble area that I see in is lines 8 and 12. Although, "Dhima/Mkopo wa Muda Mrefu(Zaidi ya mwaka mmoja)" is, in fact, a text string, inclusion of a reserved symbol like "/" causes problems when using that string for evaluations. You may want to use a dash, which is still not a perfect solution, or another way of identifying this record. You could use:

If Mid(rsLiability!txtLiabilityName, 27, 5)  = "Zaidi" _
    And rsLiability!txtLiabilityCategory = "Madeni" Then
...
If Mid(rsLiability!txtLiabilityName, 27, 5)  = "Chini" _
    And rsLiability!txtLiabilityCategory = "Madeni" Then

But this may cause problems if you every change those values in your text field.

However, this might be a place to start....

[MfanyaKitu]

What I want is to populate a hidden field based on a combo box option selected by the user, and I have decided to use the OnClose event of the form. So from your first paragraph question, the answer is yes, that's what I thought would be the way out to accomplish the task.

Unfortunately your advice and code has failed to solve the issue, the txtLiabilityCat egory default value-Madeni- remains unchanged regardless of the option chosen.