Add a new record from Combobox On Not In List property

[brucemc777]

Thank you folks for considering my confusion (or as Alice Cooper once put it, "Welcome to My Nightmare")-

I watched a very nicely done YouTube video of adding a single field record by this vector from LearnAccessByCrystal (who goes slow enough for me to follow). Problem is, i want to add a record with 9 fields and i want it to be done all at the same time (reasonably...). I attempted to do so by having the triggered property call another form with the fields to be filled, then on close saving them, but that just triggers the "On Not In List" event again. And Again. And Again...

When my back recovers from being at this desk all day long i intend to try to use an INSERT INTO instead of the

DoCmd.Close acForm, "frmInputEquipment", acSavePrompt

thinking that maybe the INSERT INTO and possibly a refresh might avoid something about the DoCmd that triggers the "Not In List", and though i doubt it, it is worth a try (as much as i fear my very rudimentary experience at handling quotation marks in an SQL statement to be run via VBA).

Is there a better angle at this?

I am going to have to use the technique in a number of instances (five to ten) because i do not want to have to stipulate that the user first always populate one table (via a form) before they can populate another - i want it to be "inline".

Thank you very much for your expertise! I suspect i will be learning for quite a bit now, for some very knowledgeable people have provided me with examples to try for other issues and i am simply trying to keep my head above water by addressing priorities for now-

 

[Gasman]

Here is how I approached it for one of my DBs.
Not saying it is the best way, experts here will give a better way, but it worked for me.

I was entering over 23k records and this control was for a crew member name.

However I think you need to use

Response = acDataErrAdded

after adding the rest of the data?

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")
    Dim txtSurname As String, txtInitials As String, strPKField As String
    Dim intNewID As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String, strNewForm As String

    Response = acDataErrContinue
    strNewForm = "frmCrew"

    If MsgBox(NewData & " is not in list. Add it?", vbYesNo) = vbYes Then

        strSQL = "SELECT * from  Crew WHERE 1 = 0"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSQL)
        'Need to check if name does have initials, some crew do not have/use them
        If InStr(1, NewData, " ") Then
            txtSurname = Left(mixed_case(NewData), InStr(1, NewData, " ") - 1)
            txtInitials = UCase(Trim(Mid(NewData, InStr(1, NewData, " ") + 1)))
        Else
            txtSurname = mixed_case(NewData)
            txtInitials = ""
        End If
        rs.AddNew
        ' add dummy cabin just to get to form
        rs!Cabin = "C"
        rs!Surname = txtSurname
        rs!Initials = txtInitials
        strPKField = rs(0).Name                  'Find name of Primary Key (ID) Field
        rs.Update

        rs.Move 0, rs.LastModified
        intNewID = rs(strPKField)

        DoCmd.OpenForm strNewForm, , , strPKField & "=" & intNewID, , acDialog
        
        Response = acDataErrAdded
MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    Else
        Response = acDataErrDisplay
    End If
End Sub

 

[brucemc777]

TY!

In my present world, if it works, it's great!

I recall a saying a gentleman once taught me - "Perfection is the mortal enemy of good enough"...

Once again, Thank you for coming to my aid!

(Having to battle one of our cats for control of the keyboard while one of our dogs is mad i am not letting her out...)