Combo check

[nevstic]

Could you help with the following please
I have a combo box that I wish to add a name if not already on the combo list / Table.
Is there a way to check and then ask I want to add the new record to the table and therefore combo afterwards using VB
many thanks

Nick

 

[PHV]

Take a look at the NotInList event procedure of the ComboBox object in the VBA help file.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Grandkathy]

I just had to figure this out today myself, and here's what I came up with. IT WORKS TOO!
On combo box, limit to list needs to be 'yes'
and the name needs to be changed below to whatever name your combo box has.

Private Sub ActivityNames_NotInList(NewData As String, Response As Integer)
'Handle non-list items entered into
'ActivityNames combo box
Dim bytResponse As Byte
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
'On Error GoTo HandleErr
bytResponse = MsgBox("Do you want to add " _
& NewData & " to the list?", vbYesNo)
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
.Open "ltblKeyWords", cnn, adOpenDynamic, _
adLockOptimistic
If bytResponse = vbYes Then
.AddNew "KeyWord", NewData
.Update
Response = acDataErrAdded
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue
ActivityNames.Undo
GoTo ExitHere
End If
End With

ExitHere:
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & ": " & _
Err.Description, vbOKOnly
Resume ExitHere
End Sub

 

[nevstic]

Hi ther grandkathy
many thanks for your reply
I put this in and it works up until here and seems to have a
problem with this bit

.Open "ltblKeyWords", cnn, adOpenDynamic, _
adLockOptimistic
I am in XP office by the way, I am not sure which version that is apart from Office XP.

Any ideas on this one please. sorry to be a pain but this is the closest I've got for ages !

Best regards
Nick

 

[PHV]

Replace ltblKeyWords with the name of the table the combo is looked for.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nevstic]

Yes !
wondeful. thankyou so much....beer's on me.

Best rgds
Nick