Tabbing Stopped By Coding ???

[LeonS]

Hello! I have written into my coding a facility that changes the BackColor (to light blue) when the field is GotFocus. The field BackColor returns to white on LostFocus. It works perfectly on about 10 fields in succession (Title, Initials, GivenName, FamilyName etc etc). However in one field (Company) on GetFocus, no problem but the cursor will not leave that field by using the tab or return. The AdComany form is a dialogue box that allows the user to enter a company name. If the name is in the Compaanies table already, the name is shown. If the company does not exist in the Companies table, the user enters a new company name. To leave that form there is a "Done" button, which closes the AddCompany form. What am I doing wrong? The code is as follows:

Private Sub Company_GotFocus()Dim ctl As Control


    DoCmd.OpenForm "AddCompany", acNormal, "", "", acEdit, acNormal
    DoCmd.RunCommand acCmdRefresh
    
    Set ctl = Me.ActiveControl
    If ctl.BackColor = 16777215 Then
        ctl.BackColor = 15913666
    Else
        ctl.BackColor = 16777215
    End If
    
End Sub

[June7]

Why open AddCompany form just because control gets focus? I tried this and I not only can't get out of that field, I can't get the second form to stay closed. End up in a loop. Closing second form and returning to first form means Company field gets focus again and opens second form again - this would go on forever.

If not already, suggest using a combobox to select company and its NotInList event to manage opening AddCompany form to add a new company 'on the fly' during data entry - open form if it is needed. Get that code out of the GotFocus event.

[LeonS]

June7 - thanks for that. It is the existing system and is similar to the results to a ComboBox. I will investigate.
In the meantime, is that the reason the tabbing does not work? It used to work before I added the colour change code. Leon

[June7]

I edited my previous answer. I don't see how it could have worked. Does not for me. And I tested without the color code.

[LeonS]

June7 - but it does work for me!!??? This is the code under the "Done" button in the AddCompany form which accepts the input and closes the form. The cursor then moves on to the next field (Website).

Private Sub Done_Click()On Error GoTo Err_Done_Click


    Forms![Contacts]![Company] = Me.Company.Value
    Forms![Contacts]![IDCompany] = Me.IDCompany.Value
    Forms![Contacts]![WebSite] = Me.WebSite.Value


    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close


Exit_Done_Click:
    Exit Sub


Err_Done_Click:
    MsgBox Err.Description
    Resume Exit_Done_Click
    
End Sub

[June7]

Instead of me trying to replicate - if you want to provide db for analysis, follow instructions at bottom of my post.

[LeonS]

June7 - thanks for your help. As you suggested, I am trying to go the "NotInList" route and I am using the following code. The ComboBox is based on a table called "Companies". In that table, apart from the primary IDCompany, there is a field called "Company" and one called "WebSite". The value is saved in the Master1 table, which has a field "IDCompany". I have tried to run the code/ box and the first part works - it shows the message box asking if I want to add, on saying 'Yes', an error comes up saying it does not recognise the field name Company. Would you please do me a favour and check the code for me? Thanks, Leon

Private Sub Combo266_NotInList(NewData As String, Response As Integer)

    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String


    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub


    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"


    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Company...")
    If i = vbYes Then
        strSQL = "Insert Into Companies ([strCompany]) " & _
                 "values ('" & NewData & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
    
End Sub

[June7]

You have strCompany as field name, not Company.

But if you would rather open a form so other info about company can be entered, review https://blueclawdatabase.com/notinlist-event-code/

[LeonS]

June7 - Brilliant!! Sorry I was so stupid! All is working well! All I need to do is get the code to do a "Proper Case" on LostFocus. I have some code, which works on all the other fields, but not on this one!! Thanks, again. Leon