Not In List compile error, varible not defined?

[d9pierce1]

CompanyTesterVlad.zip

Good morning all,
I just ran into an error testing out a NotInList event which i have used a lot over time with no issues.
So while testing this out it is asking me to declare a varible? What varible is not decared all ready?

Private Sub CboLocationType_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("Location Name is Not in Current List, Would you Like to Add?", vbYesNo)
If x = vbYes Then
strsql = "INSERT INTO tblHelper (HelperTypeID, HelperValue) " & _
        "VALUES (" & CboLocationType & ", '" & NewData & "')"
    'MsgBox strsql
    CurrentDb.Execute strsql, dbFailOnError
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
End If


End Sub

I dont get why this creates an error. I have the same code in many db's and it works fine!!!!
What have I not done correctly?



Update: OK, I see where I beleive the issue is and its in the rowsource of the CboLocationType.
So, Instead of putting the Cbo in that i put "1" in and it works. Not sure if this is the correct way
to do so but see new code.

Private Sub CboLocationType_NotInList(NewData As String, Response As Integer)
    Dim strsql As String, x As Integer
    x = MsgBox("Location Name is Not in Current List, Would you Like to Add?", vbYesNo)
    If x = vbYes Then
    strsql = "INSERT INTO tblHelper (HelperTypeID, HelperValue) " & _
            "VALUES (" & 1 & ", '" & NewData & "')"
        'MsgBox strsql
        CurrentDb.Execute strsql, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
    
End Sub

Thanks
Dave

[Micron]

Did you compile your code? That ought to make it obvious. If the same code works in other db's, that suggests a missing reference. Perhaps DAO.

[d9pierce1]

Hi Micron,
Yes, I did compile and it had one error which was "dbFailOnError" highlighted in blue. I do have dao in references. I put a "1" in instead of the CboLocationType as the row source in that didnt have the HelperTypeID in it and it worked. Not sure or not if correct method but it does work now.
Suggestions....
See Update Code above

[Gicu]

In the sample db I added a reference to Microsoft Office 15.0 Access database engine Object Library and the compile error is gone. As for your initial question the Me.cboLocationType is bound to HelperID which is the autonumber PK of tblHelper. HelperTypeID (=1) is not in the combo's row source. So you have to leave it hard coded as you currently have it (1) as you do not have it in the combo.
Cheers,
Vlad

[Micron]

Well I haven't looked too far beyond what I found when I posted that because your code failed to compile until I added the reference. At first glance, it seems that the NotInListEvent is balking about adding a new value and will re-fire every time you try to leave the control, yet it will add multiple new values to the table anyway.

[d9pierce1]

In the sample db I added a reference to Microsoft Office 15.0 Access database engine Object Library

strange, I dont have that reference checked in my db and didnt get any compile errors after i changed the Cbo to a "1" ...
so, I tried adding that reference and it gave me a name conflict error?

So, hard coding the #1 then is OK, I am so glad.

[d9pierce1]

Hi Micron,

Not sure but i first compiled it, got the error, I added DAO 3.6, Still gave me error, and then i changed the Cbo.... to a #1, and compiled it again with success.

[Gicu]

The DAO (3.6) library has been incorporated into the newer one I mentioned. I think hardcoding it or better using a dLookup to get the value from tblHelperType (dLookup("[HelperTypeID]","[tblHelperType]","[Description]='Location Type') would both work (I don't like to hard code values as sometimes the IDs could change).
Cheers,

[d9pierce1]

Hi Gicu

I hate to be a pest but I am trying to understand this code in post #8 and i have no idea where that would go? In the rowsource of combo? I just not real sure and would love to know if you have the time.

Thanks
Dave

[Gicu]

Hi Dave, it should replace the hard-coded 1 in the NotInList event:

strsql = "INSERT INTO tblHelper (HelperTypeID, HelperValue) " & _
            "VALUES (" & dLookup("[HelperTypeID]","[tblHelperType]","[Description]='Location Type') & ", '" & NewData & "')"

Cheers,
Vlad

[d9pierce1]

thank you so much.... I looked that over and like ????? Thank you for your time explaining it.
Dave

[d9pierce1]

CompanyTesterVlad - Copy.zip

Hi Gicu

I tested this out and I am getting a parameter error for [Description]. I have checked all spelling and cannot imagine why this is happening?
I did have to add a " to the code, see below as it gave me an error and redded out when inserted into NIL statement code.

strsql = "INSERT INTO tblHelper (HelperTypeID, HelperValue) " & _
            "VALUES (" & dLookup("[HelperTypeID]","[tblHelperType]","[Description]='Location Type'") & ", '" & NewData & "')"

Any ideas on why this would give an error?

Thanks
Dave

[Gicu]

Hi Dave,
Here is the entire sub (this one works), notice you have an spelling error, the fields is called Decription:

Private Sub CboLocationType_NotInList(NewData As String, Response As Integer)
    Dim strsql As String, x As Integer, lLocationType As Long
    x = MsgBox("Location Type is Not in Current List, Would you Like to Add?", vbYesNo)
    If x = vbYes Then
    lLocationType = DLookup("[HelperTypeID]", "[tblHelperType]", "[Decription]='Location Type'")
    strsql = "INSERT INTO tblHelper (HelperTypeID, HelperValue) " & _
            "VALUES (" & lLocationType & ", '" & NewData & "')"
     
        'MsgBox strsql
        CurrentDb.Execute strsql, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

Cheers,
Vlad

[d9pierce1]

Thanks, I am so sorry.... I looked that over and over and didnt see the spelling error... Embarasing\
Thanks

[Gicu]

No worries Dave, it happens to all of us (I didn't notice it the first time I looked to write the dLookup ).
Cheers,