I had Combobox's on several forms that used a NotInList Event to add new items to the list. This was working until now. When a new item is added
it uses this code that goes with the Module PubFuncAddNewToList
Private Sub cboLang_NotInList(NewData As String, Response As Integer)
Dim intAns As Integer
intAns = AddNewToList(NewData, "tblProf_exp", "PROFEXPID", "Prof_exp", "Skills of this type")
Response = acDataErrContinue
I am getting "Invalid use of Null" and then "Search key was not found in any record" This was previously working.
It is only occurring on 2 of the comboboxes even though there are 4 based on the same query with only a different skilltypeid selected.
e.g. SELECT tblProf_exp.PROFEXPID, tblProf_exp.Prof_exp, tblProf_exp.skilltypeid
ORDER BY tblProf_exp.skilltypeid;
Well, I used the example :here
and it has the two Undo commands.
All error checking is just what is included in the code and I'm not sure how to see what line is causing the error. I'm not getting the debug window opening up as with other coding I have used. Is there a way to make this happen?
I recently got hold of "Learn Access 2003 BVA with The Smart Method" mainly because I can't understand any of the error handling I see in the bits & pieces of code I pick up all over the place By recently I mean this afternoon and the problem is causing a bit of stress right now and I don't have time to calmly read a book. Although I hope to get into it soon.
This is the function to add a new item to the list.
Public Function AddNewToList(NewData As String, stTable As String, stTableID As String, stFieldName As String, strPlural As String)
On Error GoTo Err_AddNewToList
'Adds a new record to a drop down box list and
'selects that records in the source drop down box that called this code
'Declare variables and set source objects
Dim strSql As String, intStoreNewData As Long, objSource As Control
Dim strMessage As String, intNewItem As Long
Set objSource = Screen.ActiveControl
strMessage = "'" & NewData & "' is not in the current list of " & strPlural & ". " & Chr(13) & Chr(13) & "Do you want to add it to the list of " & strPlural & "? " & Chr(13) & "(Please check the new entry is correct before proceeding)."
strSql = "INSERT INTO [" & stTable & "] (" & stFieldName & ") SELECT '" & NewData & "';"
' Display message box asking if user wants to add a new item
intNewItem = MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2)
'1. Run SQL statement to add to item to table
'2. Recover new items record ID
'3. Requery source combo box to refresh the combo box drop down list
'4. Write the new items recordID to the combo box to display new record in box
If intNewItem = vbYes Then
intStoreNewData = DMax(stTableID, stTable, "[" & stFieldName & "] = '" & NewData & "'")
objSource = intStoreNewData
'move cursor out of and then back to the combo box to close the combo box list, but retain focus on combo box
I made these forms a while back and used them to enter a sample of about 15 people (making about 300 records in the main table).
This was fine and then I worked on forms and reports for display. It is now going back to enter the remaining staff members (about 70) that it is giving this error.
I haven't made any changes since then to those forms and I haven't made any changes to tables or queries that they are based on. I got around one of them by just adding every language I could think of into the underlying table so there should be no need to add new ones. The other one unfortunately requires new items to be added so I can't really avoid fixing it.
I'm not sure how to step through the code to see what is happening?
To step through your code, place the cursor in the first line of code and presss F9 to set a breakpoint. Then go and use your form, enter something into the combo box that isn't in the list. The code will stop at the breakpoint. Use F8 to step through the code one line at a time. Point to variables to see what they contain. You should then at least be able to identify the offending line. When finished you can remove your breakpoint by again using F9 with the cursor on the breakpoint.
Hi thanks for your input. The forum seemed to be down for a little while but if you are able to post your code that would be great and I will read the link you gave.
Have read it and it is much simpler and with sensible error messages too. I thought of this before but that code just clarified that I did change my table
This means that my NotinList needs to also insert a record into a field called Skilltype - on this combo box it will always be 2. I've tried to change it it but
I always have 50 false starts before I get my sql syntax right! can anyone tell me?
Private Sub cboskillcentral_NotInList(NewData As String, Response As Integer)
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 = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Skill...")
If i = vbYes Then
strSQL = "Insert Into tblProf_exp ([Prof_exp], ([Skilltype]=2)) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Response = acDataErrContinue