Full Version: NotInList event and Variant data types
UtterAccess Forums > Microsoft® Access > Access Forms

I am fed up with this. I have tried everything in the book. I have literally copy and pasted from something that works EXACTLY the same...and it doesn't work. I'm totally stumped. I don't understand how it works for one thing, and not the other. Apparently I'm missing something big.

I have attached my database, as it would take a book for me to explain what is going on without it.

There are three tables: tblAnalyte, tblContaminant, tblConAna. tblConAna is a junction table to handle the many-to-many relationship between analytes and contaminants. A contaminant is a chemical that is tested for, and analytes are the aspects of that chemical that make it harmful. So, contaminants will have many analytes, and some analytes will be used for more than one contaminant.

I have a form, frmConAna, that will take care of associating the contaminants with their analytes. This works well.
Now, should the user go to enter a contaminant/analyte in the combo box, I want the NotInList event to fire. I used the example found here

It works perfectly for the contaminant. I type in the box, my message box comes up asking if I want to add it, I say yes, my little form pops up, I hit save, everything requeries...perfect. The same code, however, blows up with the analytes. In the code, I try to set Analyte to null. It says it is not of type variant. Why on earth would that matter now, when it didn't with contaminants? So...whatever. I comment that line out. It works, sort of, I quit getting the data type error (except when I press no on my message box, then I am stuck in a record with no way out other than switching to design view--or selecting a different analyte, the delete doesn't work at this point). So, after commenting that line out and pressing yes on message box, I get my add analyte form (frmAnalyteListMini). Good, until I press save. Then it tells me, and this is very annoying cause the save line of code is there, you must save the current field before you can run the requery action. What is this??? Why did contaminants NOT do this? Say OK, then cancel out of the add form, check the table and my new analyte is there...just not in the combo box... Well, this is probably because of the subform action, but how would I direct a save command from another form, to save a record on a different form?

I don't know. The posted version has the Me.Analyte = Null commented out, in the example it was included, and like I said, it worked elsewhere within THIS VERY DATABASE.

Please, some enlightenment might save my sanity right now. I didn't post the db to have you do it for me, I posted it so I didn't have to write as much. Just point me in the right direction, and I'll take off running. I just have no clue anymore...

Thanks in advance!

Edit: I am trying to post the db now

Edited by: parkerkaylee on Wed Jul 30 15:56:43 EDT 2008.

OK...I have a crappy zip utility that likes to add additional files I'm sure no one wants to the archive...so I posed my database at this site

it is called, ironically, zipstuff
Edited by: parkerkaylee on Wed Jul 30 15:59:25 EDT 2008.
o attachment
There should be a link now...
This is the best Not In List event that I was pointed to by Alan G
Not in List Code
On Error GoTo cboEmployee_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The employee " & Chr(34) & NewData & _
Chr(34) & " is not currently in the list." & vbCrLf & _
"Would you like to add this employee to the list now?" _
, vbQuestion + vbYesNo, "Employee Not In List")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbluEmployees (ownername) " & _
"VALUES ('" & NewData & "');"
CurrentDb.Execute strSQL
MsgBox "This employee has been added to the list." _
, vbInformation, "Employee Added To List"
Response = acDataErrAdded
MsgBox "Please choose another employee from the list." _
, vbInformation, "Choose Another employee"
Response = acDataErrContinue
End If
Exit Sub
MsgBox Err.Description, vbCritical, "Error"
Resume cboEmployee_NotInList_Exit
I do think I ran across this, however, the client requires...and I know this is silly, they have a form pop up so they can edit what they typed, because, "I might change my mind." So, I say, close the dialog box, and type as desired. No, no, no...that won't work.
pondered doing that for the analytes, and leaving the contaminants 'as is', since it IS working. It just feels wrong to me...but if worse comes to worse, that's what they'll get...because I only have until Friday. Thanks for the reference.
est to tell them that what you suggest is the Best Practice in regard to Not In List - sometimes you can never give them what they demand
I understand what you're saying completely. And I agree.
However, what bothers me so about this is that it works in one instance, and not in the other. Same code, same events, different outcome.
This is the type of thing that will keep me up at night.
No ideas on why one works and not the other?
See here for a version of the 'Not in list code' (Very similar to the code Mike suggested).
To add the facility to correct an entry thenmake the following alterations to the common code module:
Deleting code in red and adding code in blue
Public Function AddNewToList(NewData As String, stTable As String, stFieldName As String) As Integer
On Error GoTo Err_Proc
    'Adds a new record to a drop down box list
    'Declare variables
    Dim strSQL As String
    Dim strMessage  As String
    Dim intNewItem As Long
    ' Display message box asking if user wants to add the new item
    strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                 "Do you want to add it? " & Chr(13) & _
                 "(Please check the new entry is correct before proceeding)."
    intNewItem = MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data")
    If intNewItem = vbYes Then [/color]  
    NewData = InputBox(strMessage, "Add New Data", NewData)
    If NewData <> "" Then  [/color]    
        'Allow for appostrophies in text string
        If InStr(1, NewData, "'") > 0 Then NewData = Replace(NewData, "'", "''")
        'Assemble and run append SQL
        strSQL = "INSERT INTO [" & stTable & "] ( [" & stFieldName & "] ) VALUES  ( '" & NewData & "' );"
        CurrentDb.Execute strSQL, dbFailOnError
        AddNewToList = acDataErrAdded
        AddNewToList = acDataErrContinue
    End If
    Exit Function
    MsgBox Err.Description, , "AddNewToList"
    Resume Exit_Proc
End Function

Hi parkerkaylee
I've looked atr our DB, In fact the structures of the Not In List Combo boxes are not the same, one is on a form and the other is on a subform. Also the Save code is different (but suspect this is from where you have been trying to get it to work)
The code saving the record on the form is OK on the main form, but you haven't changed the focus to the sub form when you attempt to save the data on the subform - therefore it fails.
If you change the code on the Add new Analyte pop up form to
    Form_sfrmAnalyte.Analyte = Me.AnalyteID
    DoCmd.RunCommand acCmdSaveRecord

This will work.
I have also tried AddNewToList code I suggested in my last post. this works for both combo boxes and is a much more robust method. It also does not depend on the location on a form or subform.

Thank you very much. I thought that the subform might have something to do with it...well, actually I didn't think that exactly...I thought since it was 'continuous' it was messing things up (I've had problems with continuous forms not working like I'd think).

Oreally appreciate you taking the time to look at my db, and the code you supplied. I had no idea that the form didn't have focus. I thought setting it to pop up and modal would MAKE it have the focus... I bet it does really, but my code set the focus back to my main form??

At any rate...thanks for your help, I really appreciate it. I showed the updated version, with your code, to the customer, "It does exactly as I thought it would, good show." Which means they approve. So, thanks again!!

Edited by: parkerkaylee on Thu Jul 31 9:39:28 EDT 2008.
Hi Kaylee
You're very welcome thumbup.gif
It occured to me that if you are allowing the user to correct the 'not in list' entry, you ought to check that the correction is not itself already in the list:
Immediatley after the new code you added (the blue in my earlier post], add
        intNewItem = DCount("[" & stFieldName & "]", "[" & stTable & "]", "[" & stFieldName & "]='" & NewData & "'")
        If intNewItem > 0 Then
            strMessage = "'" & NewData & "' is already in the list - please reselect"
            MsgBox strMessage, vbInformation, "add New Data"
            AddNewToList = acDataErrContinue
            Exit Function
        End If

Good luck with your project
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.