Wednesday, March 7, 2007

VBA code: how to insert a value in a combo box that is not in list?!!

hi.
every beginners access developers have a problem when they want to insert a new value in a combo box. that is created by "lookup wizard" or it's a foreign key and contain bound column that is generally number (hidden one) and a second column that usually a text (visible one).
so when u try to insert a new value. you will got this horrible message:







do you remember itt??!!
now this is your solution:

1- go to the properties of the combobox in design view..
2- click on the events tab..
3- and double click On Not in List.
4- you will go to the VB window.
5- insert this code:

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 will apppear to the user asking him what he want to do?
'change the text if you want.
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
If i = vbYes Then
'Change the "table" and the "field" in strSQL
strSQL = "Insert Into table([field]) " & _
"values (' " & NewData & " ');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If


No comments: