NotInList

[bbwolff]

i have main table called patients, and a form PtEntry
I have only bound textboxes and comboboxes there, so the data is entered separately
I guess I could make it unbound but that would be a lot of unnecessary work and would cause some problems.

I have a separate table called misc, where I store different data used for combobox lists. You can choose surgeon form the list or simply type a new one and it gets added to the list. Now I know how to do that for unbound boxes

Private Sub txtSurg_NotInList(NewData As String, Response As Integer)
Dim strTmp As String
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as new value for Surgeon?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
    'Append the NewData as a record in the Categories table.
    strTmp = "INSERT INTO Misc ( Surg) " & _
        "SELECT """ & NewData & """ AS Surg;"
    DBEngine(0)(0).Execute strTmp, dbFailOnError
    
    'Notify Access about the new record, so it requeries the combo.
    Response = acDataErrAdded
     End If
End Sub

If I do this to a bound combo, nothing happens.
Is it possible to do it like this? Am I missing something?
tx for help

 

[bbwolff]

ps - i could probably add an update sentence in after_update event to go around this...

 

[namliam]

You need to have the combobox requery the table

txtSurg.requery

 

[bbwolff]

where? i tried that a nothing really changed and it's not necessary for an unbound box

 

[namliam]

Not needed for an unbound box because you are editing that directly.
In this case you are editing the source table, rather than the object.

You will also notice in your code:

    'Notify Access about the new record, so it requeries the combo.
    Response = acDataErrAdded

But acDataErrAdded doesnt seem to exist at all?
This is where you need to requery your combo, just like it says it should

 

[bbwolff]

that part is left out for now, ie i get error if i say no, should correct that
i might have found the solution, mistake, as I didn't limit values to the list
i think it works for a bound box if i do that without a requery
tx