NotInList Errors

  • Thread starter Thread starter shawnrad
  • Start date Start date
S

shawnrad

I have combo boxes in many places that allow user to add new records.
All worked fine. In preparatioin for beta testing by user, I copied
the db from my machine to his. NONE of the not In List combo boxes
work! Coming back to my machine and the original db, mine no longer
works either. NONE of them. I realized I had also changed the file
location on my computer. Wondering if this somehow affected the
coding, I moved it back to its original location on my machine. Nope,
still doesn't work. HELP!!

This is the error message I get:
"The expression On Not In List you entered as the event property
setting produced the following error: A problem occurred while
Microsoft Access was communicating with the OLE server or ActiveX
Control.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro.


The db with the coding uses tables linked from a spearate db. Here is
a sample of my coding...

Private Sub Color_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewColor As String

On Error GoTo Err_Color_NotInList

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Please try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("lstAutoColor", dbOpenDynaset)

Msg = "Please enter a new color" & vbCr & "Color."
NewColor = InputBox(Msg)
Rs.FindFirst BuildCriteria("Color", dbText, NewColor)
Do Until Rs.NoMatch
NewColor = InputBox("Color " & NewColor & " already
exists." & _
vbCr & vbCr & Msg, NewColor & " Already Exists")
Rs.FindFirst BuildCriteria("Color", dbText, NewColor)
Loop
Rs.AddNew
Rs![Color] = NewColor
Rs.Update

Response = acDataErrAdded

End If

Exit_Color_NotInList:
Exit Sub
Err_Color_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
End Sub


Thanks for any help anyone can provide! I know nothing about Visual
Basic, so felt pretty cool when I figured it out (actually I copied it
from a website I found but can't find again), and this is driving me
nuts!!
 
I have combo boxes in many places that allow user to add new records.
All worked fine.  In preparatioin for beta testing by user, I copied
the db from my machine to his.  NONE of the not In List combo boxes
work!  Coming back to my machine and the original db, mine no longer
works either.  NONE of them.  I realized I had also changed the file
location on my computer.  Wondering if this somehow affected the
coding, I moved it back to its original location on my machine.  Nope,
still doesn't work.  HELP!!

This is the error message I get:
"The expression On Not In List you entered as the event property
setting produced the following error:  A problem occurred while
Microsoft Access was communicating with the OLE server or ActiveX
Control.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro.

The db with the coding uses tables linked from a spearate db.  Here is
a sample of my coding...

Private Sub Color_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewColor As String

On Error GoTo Err_Color_NotInList

    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        Response = acDataErrContinue
        MsgBox "Please try again."
    Else
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("lstAutoColor", dbOpenDynaset)

        Msg = "Please enter a new color" & vbCr & "Color."
        NewColor = InputBox(Msg)
        Rs.FindFirst BuildCriteria("Color", dbText, NewColor)
        Do Until Rs.NoMatch
           NewColor = InputBox("Color " & NewColor & " already
exists." & _
                    vbCr & vbCr & Msg, NewColor & " Already Exists")
           Rs.FindFirst BuildCriteria("Color", dbText, NewColor)
        Loop
        Rs.AddNew
        Rs![Color] = NewColor
        Rs.Update

        Response = acDataErrAdded

    End If

Exit_Color_NotInList:
       Exit Sub
Err_Color_NotInList:
       MsgBox Err.Description
       Response = acDataErrContinue
End Sub

Thanks for any help anyone can provide!  I know nothing about Visual
Basic, so felt pretty cool when I figured it out (actually I copied it
from a website I found but can't find again), and this is driving me
nuts!!
Click to expand...

Is DAO actually registered? Open a code module (any one, doesn't
matter). Go to Tools, References... my money's on the fact that DAO
is MISSING/not registered (should be checked).
 
I have combo boxes in many places that allow user to add new records.
All worked fine.  In preparatioin for beta testing by user, I copied
the db from my machine to his.  NONE of the not In List combo boxes
work!  Coming back to my machine and the original db, mine no longer
works either.  NONE of them.  I realized I had also changed the file
location on my computer.  Wondering if this somehow affected the
coding, I moved it back to its original location on my machine.  Nope,
still doesn't work.  HELP!!
Click to expand...
This is the error message I get:
"The expression On Not In List you entered as the event property
setting produced the following error:  A problem occurred while
Microsoft Access was communicating with the OLE server or ActiveX
Control.
Click to expand...
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro.
Click to expand...
The db with the coding uses tables linked from a spearate db.  Here is
a sample of my coding...
Click to expand...
Private Sub Color_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewColor As String
Click to expand...
On Error GoTo Err_Color_NotInList
Click to expand...
    If NewData = "" Then Exit Sub
Click to expand...
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        Response = acDataErrContinue
        MsgBox "Please try again."
    Else
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("lstAutoColor", dbOpenDynaset)
Click to expand...
        Msg = "Please enter a new color" & vbCr & "Color."
        NewColor = InputBox(Msg)
        Rs.FindFirst BuildCriteria("Color", dbText, NewColor)
        Do Until Rs.NoMatch
           NewColor = InputBox("Color " & NewColor & " already
exists." & _
                    vbCr & vbCr & Msg, NewColor & "Already Exists")
           Rs.FindFirst BuildCriteria("Color", dbText, NewColor)
        Loop
        Rs.AddNew
        Rs![Color] = NewColor
        Rs.Update
Click to expand...
        Response = acDataErrAdded
Click to expand...
    End If
Click to expand...
Exit_Color_NotInList:
       Exit Sub
Err_Color_NotInList:
       MsgBox Err.Description
       Response = acDataErrContinue
End Sub
Click to expand...
Thanks for any help anyone can provide!  I know nothing about Visual
Basic, so felt pretty cool when I figured it out (actually I copied it
from a website I found but can't find again), and this is driving me
nuts!!
Click to expand...

Is DAO actually registered?  Open a code module (any one, doesn't
matter).  Go to Tools, References... my money's on the fact that DAO
is MISSING/not registered (should be checked).- Hide quoted text -

- Show quoted text -
Click to expand...

Check box for "Microsoft DAO 3.6 Object Library" is checked. I have
even tried clearing the check box, exiting Access, then re-opening the
db and re-checking the box. Still didn't work. Any other suggestions?
 
I have combo boxes in many places that allow user to add new records.
All worked fine.  In preparatioin for beta testing by user, I copied
the db from my machine to his.  NONE of the not In List combo boxes
work!  Coming back to my machine and the original db, mine no longer
works either.  NONE of them.  I realized I had also changed the file
location on my computer.  Wondering if this somehow affected the
coding, I moved it back to its original location on my machine.  Nope,
still doesn't work.  HELP!!
This is the error message I get:
"The expression On Not In List you entered as the event property
setting produced the following error:  A problem occurred while
Microsoft Access was communicating with the OLE server or ActiveX
Control.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro.
The db with the coding uses tables linked from a spearate db.  Hereis
a sample of my coding...
Private Sub Color_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewColor As String
On Error GoTo Err_Color_NotInList
    If NewData = "" Then Exit Sub
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        Response = acDataErrContinue
        MsgBox "Please try again."
    Else
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("lstAutoColor", dbOpenDynaset)
        Msg = "Please enter a new color" & vbCr & "Color."
        NewColor = InputBox(Msg)
        Rs.FindFirst BuildCriteria("Color", dbText, NewColor)
        Do Until Rs.NoMatch
           NewColor = InputBox("Color " & NewColor & " already
exists." & _
                    vbCr & vbCr & Msg, NewColor &" Already Exists")
           Rs.FindFirst BuildCriteria("Color", dbText, NewColor)
        Loop
        Rs.AddNew
        Rs![Color] = NewColor
        Rs.Update
        Response = acDataErrAdded
    End If
Exit_Color_NotInList:
       Exit Sub
Err_Color_NotInList:
       MsgBox Err.Description
       Response = acDataErrContinue
End Sub
Thanks for any help anyone can provide!  I know nothing about Visual
Basic, so felt pretty cool when I figured it out (actually I copied it
from a website I found but can't find again), and this is driving me
nuts!!
Click to expand...
Click to expand...
Is DAO actually registered?  Open a code module (any one, doesn't
matter).  Go to Tools, References... my money's on the fact that DAO
is MISSING/not registered (should be checked).- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Check box for "Microsoft DAO 3.6 Object Library" is checked.  I have
even tried clearing the check box, exiting Access, then re-opening the
db and re-checking the box.  Still didn't work.  Any other suggestions?- Hide quoted text -

- Show quoted text -
Click to expand...

I tried a couple of things today, the major one being importing my
tables from their own db to the same one containing queries, forms and
macros. The error message changed to:

“A problem occurred while Microsoft Access was communicating with the
OLE Server or Active X Control.

Close the OLE server and restart it outside of Microsoft Access. Then
try the original operation again in Microsoft Access. “


HUH?? I searched my computer for something called an OLE Server
(don't laugh) and found nothing. The only suggestion I found after
scouring the internet was to disable script something or other in
Norton AntiVirus. Even though I use MacAffee, I tried it. Didn't
work. Suggestions?
 
Back
Top