Lost focus event not firing?

[Gasman]

Hi everyone,

When does a Lost Focus Event not fire please?

I ask as I have a form for crewnames. It consists of two text controls, surname and initials.
The lost focus for the surname makes the text Proper.
The lost focus for the initials makes the text uppercase.

In my links form I have the Not in List event open the crewnames form and put the value entered in the links form in the surname field (This code was supplied by a member here, and has saved me a lot of time keying in missing data)
However, when I tab out and go to the initials control, the code does not fire?
I enter the initials and tab out, and that code always fires, so I have to click back into the surname control and tab out again?

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

Response = AddNewToList(NewData, "Crew", "Surname", "Crews", "frmCrew")

End Sub

' usage..... Response = AddNewToList(NewData, "LtblCounties", "txtCounty", "Counties")
Public Function AddNewToList(NewData As String, stTable As String, _
                                   stFieldName As String, strPlural As String, _
                                   Optional strNewForm As String) As Integer
On Error GoTo err_proc
    'Adds a new record to a drop down box list
    'If form name passed, then open this form to the newly created record

    'Declare variables
    Dim rst As DAO.Recordset
    Dim IntNewID As Long
    Dim strPKField As String
    Dim strMessage As String
    
    ' Display message box asking if user wants to add the new item
    strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                 "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
                 "(Please check the entry before proceeding)."

    If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
        Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
        rst.AddNew
            rst(stFieldName) = NewData                'Add new data from combo box
            strPKField = rst(0).Name                  'Find name of Primary Key (ID) Field
        rst.Update
        rst.Move 0, rst.LastModified
        IntNewID = rst(strPKField)

        'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
        If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID
  
        AddNewToList = acDataErrAdded                'Set response 'Data added'
    Else
        AddNewToList = acDataErrContinue             'Set response 'Data NOT added'
    End If
    
exit_proc:
On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function

err_proc:
    MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & Err.Description, , "Function Error"
    Resume exit_proc

End Function

 

[moke123]

try this

Response = AddNewToList(strconv(NewData,3), "Crew", "Surname", "Crews", "frmCrew")

This should convert it before sending it to the procedure.

i believe i posted that Not in list code for you, but i got it from elsewhere also.

 

[Gasman]

try this

Response = AddNewToList(strconv(NewData,3), "Crew", "Surname", "Crews", "frmCrew")

This should convert it before sending it to the procedure.

i believe i posted that Not in list code for you, but i got it from elsewhere also.

Thank you Moke,

I tried to debug it and spotted in the code that I was using me.control.value (this is very old code created in Access 2003 a good while back, when I was even more of a novice )
I have removed the .value part and it *appears* to now work as I expected.

I've stopped data entry for now, as my eyes need a rest, but will explore more next time I start again. I have plenty of data to key in, so plenty of chances to test.

If my mod has not worked I'll try yours.

Damn, I've just see what the strconv(value,3) does
This was my way

Private Sub Surname_LostFocus()
If Not IsNull(Me.Surname) Then
    Me.Surname = UCase(Left(Me.Surname, 1)) & Right(Me.Surname, Len(Me.Surname) - 1)
End If
End Sub

 

[moke123]

even better, take look here...http://access.mvps.org/access/strings/str0008.htm

 

[Gasman]

even better, take look here...http://access.mvps.org/access/strings/str0008.htm

Thank you Moke,

I think that is a little overkill for my current situation, but will file away for any future projects.
I will probably change my code to use the strconv function now as well. Much tidier.

I will also look at the AfterUpdate event as you suggested.

 

[moke123]

i tried the after update and it didnt fire with the NIL procedure.

 

[Gasman]

i tried the after update and it didnt fire with the NIL procedure.

Well the strconv function would not work well in my case due to McGrath etc. Even when I made the G uppercase the function made it lowercase, so I went back to my code and then decided to use that code you linked to, which works a treat.

Thank you again, that will cut down on the time taken for new crew names.

FWIW the code is now in the AfterUpdate event and I call the AfterUpdate from the LostFocus event to try and cover all eventualities

 

[moke123]

FWIW the code is now in the AfterUpdate event and I call the AfterUpdate from the LostFocus event to try and cover all eventualities

You probably dont have to complicate things with these events.

this should do the trick in one shot

Response = AddNewToList(mixed_case(NewData), "Crew", "Surname", "Crews", "frmCrew")

 

[Gasman]

You probably dont have to complicate things with these events.

this should do the trick in one shot

Response = AddNewToList(mixed_case(NewData), "Crew", "Surname", "Crews", "frmCrew")

OK, I'll give that a go next time I add some new data.

Thank you.

 

[Gasman]

Response = AddNewToList(mixed_case(NewData), "Crew", "Surname", "Crews", "frmCrew")

Tried it today, and it works well.

Thanks again for the link and code

 

[moke123]

your welcome.
good luck with your project.

 

[Gasman]

Moke,

I wonder if you could help me further please?
When I key in an entry it would be like

moke l

This then is passed to the form, where I have to take out the initials.
So I added a line of code as below. I did try a seperate string initially, and then changed it to as you see.
The problem is I get the 'Do you want to add message', but when I get to the form I get the not in list message?
Then after entering the data and going back to the main form, I have the 'do you want to add' message again and No property found message after that one

This would occur occasionally before my change, but have never been able to identify why. Now it occurs each time I have to add a new name?

Private Sub Crew_ID_NotInList(NewData As String, Response As Integer)
'Dim strSurname As String
NewData = Left(NewData, InStr(NewData, " ") - 1)
Response = AddNewToList(mixed_case(NewData), "Crew", "Surname", "Crews", "frmCrew")

End Sub

Any idea as to why this behaviour occurs?
I notice this only happens when the name is not in the table.
If I have a Moke P in the table and want to add Moke L, it appears to work as expected.

TIA