Converting to Access 2002 Code doesn't work 
Author Message
 Converting to Access 2002 Code doesn't work

All -

I had a code in Access 97 that would allow users to add and item to a combo
box if it wasn't already one of the choices.  This code is {*filter*} in Access
2002.  Can someone direct me to anything that would help?

Thank you,

Melanie



Mon, 03 May 2004 00:17:18 GMT  
 Converting to Access 2002 Code doesn't work
For a better analysis you'd have to post the code.  Are you using a
recordset, by any chance?  One common cause of errors in code copied from
A97 to A2002 is that A2002 doesn't include a reference to DAO by default (it
defaults to ADO), and even wth such a reference ADO objects have (by
default) a higher precedence than DAO objects with the same names.  Both ADO
and DAO define a Recordset object, so if you have code like this:

    Dim rs As Recordset

it will normally be interpreted by A2002 as an ADO recordset, which has
different properties and methods.  For safety's sake, you would do well to
"disambiguate" the declaration by making it like this:

    Dim rs As DAO.Recordset

If that doesn't compile, you need to open the Tools -> References... dialog
and check the reference for the DAO 3.6 Object Library.

Of course, you could have a completely different problem.  For that, we'd
need to see the code.
--
Dirk Goldgar
www.datagnostics.com

(to reply via e-mail, remove NOSPAM from address)


Quote:
> All -

> I had a code in Access 97 that would allow users to add and item to a
combo
> box if it wasn't already one of the choices.  This code is {*filter*} in
Access
> 2002.  Can someone direct me to anything that would help?

> Thank you,

> Melanie



Mon, 03 May 2004 00:46:54 GMT  
 Converting to Access 2002 Code doesn't work
I believe you have hit on the problem exactly.  I will work on this and get
back to you.  Thank you for your time and response.

Melanie

Quote:
> For a better analysis you'd have to post the code.  Are you using a
> recordset, by any chance?  One common cause of errors in code copied from
> A97 to A2002 is that A2002 doesn't include a reference to DAO by default
(it
> defaults to ADO), and even wth such a reference ADO objects have (by
> default) a higher precedence than DAO objects with the same names.  Both
ADO
> and DAO define a Recordset object, so if you have code like this:

>     Dim rs As Recordset

> it will normally be interpreted by A2002 as an ADO recordset, which has
> different properties and methods.  For safety's sake, you would do well to
> "disambiguate" the declaration by making it like this:

>     Dim rs As DAO.Recordset

> If that doesn't compile, you need to open the Tools -> References...
dialog
> and check the reference for the DAO 3.6 Object Library.

> Of course, you could have a completely different problem.  For that, we'd
> need to see the code.
> --
> Dirk Goldgar
> www.datagnostics.com

> (to reply via e-mail, remove NOSPAM from address)



> > All -

> > I had a code in Access 97 that would allow users to add and item to a
> combo
> > box if it wasn't already one of the choices.  This code is {*filter*} in
> Access
> > 2002.  Can someone direct me to anything that would help?

> > Thank you,

> > Melanie



Mon, 03 May 2004 01:44:44 GMT  
 Converting to Access 2002 Code doesn't work
I am putting my code below.  It will not work with the suggestion below.
Please let me know if you can see the problem.

Thank you Melanie

Private Sub cmbDegrees_NotInList(NewData As String, Response As Integer)
Dim Rs As DAO.Recordset
Dim Msg As String
Dim CR As String

CR = Chr$(13)
' Exit this subroutine if the combo box was cleared
If NewData = "" Then Exit Sub

'Confirm that the user wants to add the new item
Msg = "'" & NewData & "' is Not in the list." & CR & CR
Msg = Msg & " Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    ' If the user chose not to add a customer, set the Response
    ' Agrument to suppress an error message and undo changes.
    Reponse = acDataErrContinue
    'Display a customized message.
    MsgBox "Please try again"
Else
    ' If the user chooses to add the item open a recordset
    ' Using the table you wish to add it to
    Set Db = Current
    Set Rs = Db.OpenRecordset("tblDegrees", DB_OPEN_TABLE)
    'Let code execution continue if a run-time error occurs
    On Error Resume Next
   Rs![DegreeName] = NewData

If Err Then
    ' If a run-time error occured while attempting to add a new
    'Record, set the Response argument to suppress an error
    'message and undo changes
    Response = acDataErrContinue
    ' Display a customized message
    MsgBox Error$ & CR & CR & "Please Try Again.", vbExclamation
Else
    'If a run-time error did not occur, set response argument
    ' to indicate that new data is being added.
    Response = acDataErrAdded
End If

End Sub


Quote:
> For a better analysis you'd have to post the code.  Are you using a
> recordset, by any chance?  One common cause of errors in code copied from
> A97 to A2002 is that A2002 doesn't include a reference to DAO by default
(it
> defaults to ADO), and even wth such a reference ADO objects have (by
> default) a higher precedence than DAO objects with the same names.  Both
ADO
> and DAO define a Recordset object, so if you have code like this:

>     Dim rs As Recordset

> it will normally be interpreted by A2002 as an ADO recordset, which has
> different properties and methods.  For safety's sake, you would do well to
> "disambiguate" the declaration by making it like this:

>     Dim rs As DAO.Recordset

> If that doesn't compile, you need to open the Tools -> References...
dialog
> and check the reference for the DAO 3.6 Object Library.

> Of course, you could have a completely different problem.  For that, we'd
> need to see the code.
> --
> Dirk Goldgar
> www.datagnostics.com

> (to reply via e-mail, remove NOSPAM from address)



> > All -

> > I had a code in Access 97 that would allow users to add and item to a
> combo
> > box if it wasn't already one of the choices.  This code is {*filter*} in
> Access
> > 2002.  Can someone direct me to anything that would help?

> > Thank you,

> > Melanie



Mon, 03 May 2004 02:19:23 GMT  
 Converting to Access 2002 Code doesn't work
This code wouldn't have worked in A97, either.  Are you sure you transcribed
the whole thing?

(1)

Quote:
>     Set Db = Current

should be
     Set Db = CurrentDb

(2)

Quote:
>    Rs![DegreeName] = NewData

has no preceding Rs.AddNew or following Rs.Update.  This should work:
    With Rs
        .AddNew
        ![DegreeName] = NewData
        .Update
    End With

Although you'd be better off without opening the recordset at all, and just
executing an append query on the fly:

    CurrentDb.Execute _
        "INSERT INTO tblDegrees (DegreeName) " & _
            "VALUES (" & Chr(34) & NewData & Chr(34) & ");", _
        dbFailOnError

Note: the above assumes that DegreeName is a text field;  if not, just leave
out the "& Chr(34)"s.

--
Dirk Goldgar
www.datagnostics.com

(to reply via e-mail, remove NOSPAM from address)


Quote:
> I am putting my code below.  It will not work with the suggestion below.
> Please let me know if you can see the problem.

> Thank you Melanie

> Private Sub cmbDegrees_NotInList(NewData As String, Response As Integer)
> Dim Rs As DAO.Recordset
> Dim Msg As String
> Dim CR As String

> CR = Chr$(13)
> ' Exit this subroutine if the combo box was cleared
> If NewData = "" Then Exit Sub

> 'Confirm that the user wants to add the new item
> Msg = "'" & NewData & "' is Not in the list." & CR & CR
> Msg = Msg & " Do you want to add it?"
> If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
>     ' If the user chose not to add a customer, set the Response
>     ' Agrument to suppress an error message and undo changes.
>     Reponse = acDataErrContinue
>     'Display a customized message.
>     MsgBox "Please try again"
> Else
>     ' If the user chooses to add the item open a recordset
>     ' Using the table you wish to add it to
>     Set Db = Current
>     Set Rs = Db.OpenRecordset("tblDegrees", DB_OPEN_TABLE)
>     'Let code execution continue if a run-time error occurs
>     On Error Resume Next
>    Rs![DegreeName] = NewData

> If Err Then
>     ' If a run-time error occured while attempting to add a new
>     'Record, set the Response argument to suppress an error
>     'message and undo changes
>     Response = acDataErrContinue
>     ' Display a customized message
>     MsgBox Error$ & CR & CR & "Please Try Again.", vbExclamation
> Else
>     'If a run-time error did not occur, set response argument
>     ' to indicate that new data is being added.
>     Response = acDataErrAdded
> End If

> End Sub



> > For a better analysis you'd have to post the code.  Are you using a
> > recordset, by any chance?  One common cause of errors in code copied
from
> > A97 to A2002 is that A2002 doesn't include a reference to DAO by default
> (it
> > defaults to ADO), and even wth such a reference ADO objects have (by
> > default) a higher precedence than DAO objects with the same names.  Both
> ADO
> > and DAO define a Recordset object, so if you have code like this:

> >     Dim rs As Recordset

> > it will normally be interpreted by A2002 as an ADO recordset, which has
> > different properties and methods.  For safety's sake, you would do well
to
> > "disambiguate" the declaration by making it like this:

> >     Dim rs As DAO.Recordset

> > If that doesn't compile, you need to open the Tools -> References...
> dialog
> > and check the reference for the DAO 3.6 Object Library.

> > Of course, you could have a completely different problem.  For that,
we'd
> > need to see the code.
> > --
> > Dirk Goldgar
> > www.datagnostics.com

> > (to reply via e-mail, remove NOSPAM from address)



> > > All -

> > > I had a code in Access 97 that would allow users to add and item to a
> > combo
> > > box if it wasn't already one of the choices.  This code is {*filter*} in
> > Access
> > > 2002.  Can someone direct me to anything that would help?

> > > Thank you,

> > > Melanie



Mon, 03 May 2004 02:46:38 GMT  
 Converting to Access 2002 Code doesn't work
In addition to the points Dirk raises:

The declaration of Db is missing - unless it's a public or module level
variable declared outside the procedure? And I'd leave out the
'DB_OPEN_TABLE' from the OpenRecordset statement. If it's a local table, it
will default to a table-type recordset anyway, and if it's not a local
table, attempting to specify a table-type recordset will cause an error.

--

Brendan Reynolds


Quote:
> I am putting my code below.  It will not work with the suggestion below.
> Please let me know if you can see the problem.

> Thank you Melanie

> Private Sub cmbDegrees_NotInList(NewData As String, Response As Integer)
> Dim Rs As DAO.Recordset
> Dim Msg As String
> Dim CR As String

> CR = Chr$(13)
> ' Exit this subroutine if the combo box was cleared
> If NewData = "" Then Exit Sub

> 'Confirm that the user wants to add the new item
> Msg = "'" & NewData & "' is Not in the list." & CR & CR
> Msg = Msg & " Do you want to add it?"
> If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
>     ' If the user chose not to add a customer, set the Response
>     ' Agrument to suppress an error message and undo changes.
>     Reponse = acDataErrContinue
>     'Display a customized message.
>     MsgBox "Please try again"
> Else
>     ' If the user chooses to add the item open a recordset
>     ' Using the table you wish to add it to
>     Set Db = Current
>     Set Rs = Db.OpenRecordset("tblDegrees", DB_OPEN_TABLE)
>     'Let code execution continue if a run-time error occurs
>     On Error Resume Next
>    Rs![DegreeName] = NewData

> If Err Then
>     ' If a run-time error occured while attempting to add a new
>     'Record, set the Response argument to suppress an error
>     'message and undo changes
>     Response = acDataErrContinue
>     ' Display a customized message
>     MsgBox Error$ & CR & CR & "Please Try Again.", vbExclamation
> Else
>     'If a run-time error did not occur, set response argument
>     ' to indicate that new data is being added.
>     Response = acDataErrAdded
> End If

> End Sub



> > For a better analysis you'd have to post the code.  Are you using a
> > recordset, by any chance?  One common cause of errors in code copied
from
> > A97 to A2002 is that A2002 doesn't include a reference to DAO by default
> (it
> > defaults to ADO), and even wth such a reference ADO objects have (by
> > default) a higher precedence than DAO objects with the same names.  Both
> ADO
> > and DAO define a Recordset object, so if you have code like this:

> >     Dim rs As Recordset

> > it will normally be interpreted by A2002 as an ADO recordset, which has
> > different properties and methods.  For safety's sake, you would do well
to
> > "disambiguate" the declaration by making it like this:

> >     Dim rs As DAO.Recordset

> > If that doesn't compile, you need to open the Tools -> References...
> dialog
> > and check the reference for the DAO 3.6 Object Library.

> > Of course, you could have a completely different problem.  For that,
we'd
> > need to see the code.
> > --
> > Dirk Goldgar
> > www.datagnostics.com

> > (to reply via e-mail, remove NOSPAM from address)



> > > All -

> > > I had a code in Access 97 that would allow users to add and item to a
> > combo
> > > box if it wasn't already one of the choices.  This code is {*filter*} in
> > Access
> > > 2002.  Can someone direct me to anything that would help?

> > > Thank you,

> > > Melanie



Mon, 03 May 2004 03:40:20 GMT  
 Converting to Access 2002 Code doesn't work
Ok I have made the changes that you noted.  However I want the user to be
prompted to make sure that they want to add the item first.  I have kept
that the code does indeed add the item to the table.  However now I need to
do something to requery the combo box since it does not see the new item in
the list and keeps giving me an error.  Any ideas on what I missing?  Here
is the code.

Private Sub Combo6_NotInList(NewData As String, Response As Integer)
'Confirm that the user wants to add the item
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    ' If the user chooses not to add an item, set the reponse
    'argument to suppress an error message and undo changes
    Response = acDataErrContinue
    'Display a customized message
    MsgBox "Please Try Again."
Else

CurrentDb.Execute _
        "INSERT INTO tblDegrees (DegreeName) " & _
            "VALUES (" & Chr(34) & NewData & Chr(34) & ");", _
        dbFailOnError

End If

End Sub


Quote:
> This code wouldn't have worked in A97, either.  Are you sure you
transcribed
> the whole thing?

> (1)
> >     Set Db = Current
> should be
>      Set Db = CurrentDb

> (2)
> >    Rs![DegreeName] = NewData
> has no preceding Rs.AddNew or following Rs.Update.  This should work:
>     With Rs
>         .AddNew
>         ![DegreeName] = NewData
>         .Update
>     End With

> Although you'd be better off without opening the recordset at all, and
just
> executing an append query on the fly:

>     CurrentDb.Execute _
>         "INSERT INTO tblDegrees (DegreeName) " & _
>             "VALUES (" & Chr(34) & NewData & Chr(34) & ");", _
>         dbFailOnError

> Note: the above assumes that DegreeName is a text field;  if not, just
leave
> out the "& Chr(34)"s.

> --
> Dirk Goldgar
> www.datagnostics.com

> (to reply via e-mail, remove NOSPAM from address)



> > I am putting my code below.  It will not work with the suggestion below.
> > Please let me know if you can see the problem.

> > Thank you Melanie

> > Private Sub cmbDegrees_NotInList(NewData As String, Response As Integer)
> > Dim Rs As DAO.Recordset
> > Dim Msg As String
> > Dim CR As String

> > CR = Chr$(13)
> > ' Exit this subroutine if the combo box was cleared
> > If NewData = "" Then Exit Sub

> > 'Confirm that the user wants to add the new item
> > Msg = "'" & NewData & "' is Not in the list." & CR & CR
> > Msg = Msg & " Do you want to add it?"
> > If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
> >     ' If the user chose not to add a customer, set the Response
> >     ' Agrument to suppress an error message and undo changes.
> >     Reponse = acDataErrContinue
> >     'Display a customized message.
> >     MsgBox "Please try again"
> > Else
> >     ' If the user chooses to add the item open a recordset
> >     ' Using the table you wish to add it to
> >     Set Db = Current
> >     Set Rs = Db.OpenRecordset("tblDegrees", DB_OPEN_TABLE)
> >     'Let code execution continue if a run-time error occurs
> >     On Error Resume Next
> >    Rs![DegreeName] = NewData

> > If Err Then
> >     ' If a run-time error occured while attempting to add a new
> >     'Record, set the Response argument to suppress an error
> >     'message and undo changes
> >     Response = acDataErrContinue
> >     ' Display a customized message
> >     MsgBox Error$ & CR & CR & "Please Try Again.", vbExclamation
> > Else
> >     'If a run-time error did not occur, set response argument
> >     ' to indicate that new data is being added.
> >     Response = acDataErrAdded
> > End If

> > End Sub



> > > For a better analysis you'd have to post the code.  Are you using a
> > > recordset, by any chance?  One common cause of errors in code copied
> from
> > > A97 to A2002 is that A2002 doesn't include a reference to DAO by
default
> > (it
> > > defaults to ADO), and even wth such a reference ADO objects have (by
> > > default) a higher precedence than DAO objects with the same names.
Both
> > ADO
> > > and DAO define a Recordset object, so if you have code like this:

> > >     Dim rs As Recordset

> > > it will normally be interpreted by A2002 as an ADO recordset, which
has
> > > different properties and methods.  For safety's sake, you would do
well
> to
> > > "disambiguate" the declaration by making it like this:

> > >     Dim rs As DAO.Recordset

> > > If that doesn't compile, you need to open the Tools -> References...
> > dialog
> > > and check the reference for the DAO 3.6 Object Library.

> > > Of course, you could have a completely different problem.  For that,
> we'd
> > > need to see the code.
> > > --
> > > Dirk Goldgar
> > > www.datagnostics.com

> > > (to reply via e-mail, remove NOSPAM from address)



> > > > All -

> > > > I had a code in Access 97 that would allow users to add and item to
a
> > > combo
> > > > box if it wasn't already one of the choices.  This code is {*filter*}
in
> > > Access
> > > > 2002.  Can someone direct me to anything that would help?

> > > > Thank you,

> > > > Melanie



Mon, 03 May 2004 03:58:42 GMT  
 Converting to Access 2002 Code doesn't work
You're missing the line

    Response = acDataErrAdded

after you've added the new record to the table.

--
Dirk Goldgar
www.datagnostics.com

(to reply via e-mail, remove NOSPAM from address)


Quote:
> Ok I have made the changes that you noted.  However I want the user to be
> prompted to make sure that they want to add the item first.  I have kept
> that the code does indeed add the item to the table.  However now I need
to
> do something to requery the combo box since it does not see the new item
in
> the list and keeps giving me an error.  Any ideas on what I missing?  Here
> is the code.

> Private Sub Combo6_NotInList(NewData As String, Response As Integer)
> 'Confirm that the user wants to add the item
> Msg = "'" & NewData & "' is not in the list." & CR & CR
> Msg = Msg & "Do you want to add it?"
> If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
>     ' If the user chooses not to add an item, set the reponse
>     'argument to suppress an error message and undo changes
>     Response = acDataErrContinue
>     'Display a customized message
>     MsgBox "Please Try Again."
> Else

> CurrentDb.Execute _
>         "INSERT INTO tblDegrees (DegreeName) " & _
>             "VALUES (" & Chr(34) & NewData & Chr(34) & ");", _
>         dbFailOnError

> End If

> End Sub



> > This code wouldn't have worked in A97, either.  Are you sure you
> transcribed
> > the whole thing?

> > (1)
> > >     Set Db = Current
> > should be
> >      Set Db = CurrentDb

> > (2)
> > >    Rs![DegreeName] = NewData
> > has no preceding Rs.AddNew or following Rs.Update.  This should work:
> >     With Rs
> >         .AddNew
> >         ![DegreeName] = NewData
> >         .Update
> >     End With

> > Although you'd be better off without opening the recordset at all, and
> just
> > executing an append query on the fly:

> >     CurrentDb.Execute _
> >         "INSERT INTO tblDegrees (DegreeName) " & _
> >             "VALUES (" & Chr(34) & NewData & Chr(34) & ");", _
> >         dbFailOnError

> > Note: the above assumes that DegreeName is a text field;  if not, just
> leave
> > out the "& Chr(34)"s.

> > --
> > Dirk Goldgar
> > www.datagnostics.com

> > (to reply via e-mail, remove NOSPAM from address)



> > > I am putting my code below.  It will not work with the suggestion
below.
> > > Please let me know if you can see the problem.

> > > Thank you Melanie

> > > Private Sub cmbDegrees_NotInList(NewData As String, Response As
Integer)
> > > Dim Rs As DAO.Recordset
> > > Dim Msg As String
> > > Dim CR As String

> > > CR = Chr$(13)
> > > ' Exit this subroutine if the combo box was cleared
> > > If NewData = "" Then Exit Sub

> > > 'Confirm that the user wants to add the new item
> > > Msg = "'" & NewData & "' is Not in the list." & CR & CR
> > > Msg = Msg & " Do you want to add it?"
> > > If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
> > >     ' If the user chose not to add a customer, set the Response
> > >     ' Agrument to suppress an error message and undo changes.
> > >     Reponse = acDataErrContinue
> > >     'Display a customized message.
> > >     MsgBox "Please try again"
> > > Else
> > >     ' If the user chooses to add the item open a recordset
> > >     ' Using the table you wish to add it to
> > >     Set Db = Current
> > >     Set Rs = Db.OpenRecordset("tblDegrees", DB_OPEN_TABLE)
> > >     'Let code execution continue if a run-time error occurs
> > >     On Error Resume Next
> > >    Rs![DegreeName] = NewData

> > > If Err Then
> > >     ' If a run-time error occured while attempting to add a new
> > >     'Record, set the Response argument to suppress an error
> > >     'message and undo changes
> > >     Response = acDataErrContinue
> > >     ' Display a customized message
> > >     MsgBox Error$ & CR & CR & "Please Try Again.", vbExclamation
> > > Else
> > >     'If a run-time error did not occur, set response argument
> > >     ' to indicate that new data is being added.
> > >     Response = acDataErrAdded
> > > End If

> > > End Sub



> > > > For a better analysis you'd have to post the code.  Are you using a
> > > > recordset, by any chance?  One common cause of errors in code copied
> > from
> > > > A97 to A2002 is that A2002 doesn't include a reference to DAO by
> default
> > > (it
> > > > defaults to ADO), and even wth such a reference ADO objects have (by
> > > > default) a higher precedence than DAO objects with the same names.
> Both
> > > ADO
> > > > and DAO define a Recordset object, so if you have code like this:

> > > >     Dim rs As Recordset

> > > > it will normally be interpreted by A2002 as an ADO recordset, which
> has
> > > > different properties and methods.  For safety's sake, you would do
> well
> > to
> > > > "disambiguate" the declaration by making it like this:

> > > >     Dim rs As DAO.Recordset

> > > > If that doesn't compile, you need to open the Tools -> References...
> > > dialog
> > > > and check the reference for the DAO 3.6 Object Library.

> > > > Of course, you could have a completely different problem.  For that,
> > we'd
> > > > need to see the code.
> > > > --
> > > > Dirk Goldgar
> > > > www.datagnostics.com

> > > > (to reply via e-mail, remove NOSPAM from address)



> > > > > All -

> > > > > I had a code in Access 97 that would allow users to add and item
to
> a
> > > > combo
> > > > > box if it wasn't already one of the choices.  This code is {*filter*}
> in
> > > > Access
> > > > > 2002.  Can someone direct me to anything that would help?

> > > > > Thank you,

> > > > > Melanie



Mon, 03 May 2004 04:03:32 GMT  
 Converting to Access 2002 Code doesn't work
Thank you very much for your help this now works perfectly.

Melanie

Quote:
> You're missing the line

>     Response = acDataErrAdded

> after you've added the new record to the table.

> --
> Dirk Goldgar
> www.datagnostics.com

> (to reply via e-mail, remove NOSPAM from address)



> > Ok I have made the changes that you noted.  However I want the user to
be
> > prompted to make sure that they want to add the item first.  I have kept
> > that the code does indeed add the item to the table.  However now I need
> to
> > do something to requery the combo box since it does not see the new item
> in
> > the list and keeps giving me an error.  Any ideas on what I missing?
Here
> > is the code.

> > Private Sub Combo6_NotInList(NewData As String, Response As Integer)
> > 'Confirm that the user wants to add the item
> > Msg = "'" & NewData & "' is not in the list." & CR & CR
> > Msg = Msg & "Do you want to add it?"
> > If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
> >     ' If the user chooses not to add an item, set the reponse
> >     'argument to suppress an error message and undo changes
> >     Response = acDataErrContinue
> >     'Display a customized message
> >     MsgBox "Please Try Again."
> > Else

> > CurrentDb.Execute _
> >         "INSERT INTO tblDegrees (DegreeName) " & _
> >             "VALUES (" & Chr(34) & NewData & Chr(34) & ");", _
> >         dbFailOnError

> > End If

> > End Sub



> > > This code wouldn't have worked in A97, either.  Are you sure you
> > transcribed
> > > the whole thing?

> > > (1)
> > > >     Set Db = Current
> > > should be
> > >      Set Db = CurrentDb

> > > (2)
> > > >    Rs![DegreeName] = NewData
> > > has no preceding Rs.AddNew or following Rs.Update.  This should work:
> > >     With Rs
> > >         .AddNew
> > >         ![DegreeName] = NewData
> > >         .Update
> > >     End With

> > > Although you'd be better off without opening the recordset at all, and
> > just
> > > executing an append query on the fly:

> > >     CurrentDb.Execute _
> > >         "INSERT INTO tblDegrees (DegreeName) " & _
> > >             "VALUES (" & Chr(34) & NewData & Chr(34) & ");", _
> > >         dbFailOnError

> > > Note: the above assumes that DegreeName is a text field;  if not, just
> > leave
> > > out the "& Chr(34)"s.

> > > --
> > > Dirk Goldgar
> > > www.datagnostics.com

> > > (to reply via e-mail, remove NOSPAM from address)



> > > > I am putting my code below.  It will not work with the suggestion
> below.
> > > > Please let me know if you can see the problem.

> > > > Thank you Melanie

> > > > Private Sub cmbDegrees_NotInList(NewData As String, Response As
> Integer)
> > > > Dim Rs As DAO.Recordset
> > > > Dim Msg As String
> > > > Dim CR As String

> > > > CR = Chr$(13)
> > > > ' Exit this subroutine if the combo box was cleared
> > > > If NewData = "" Then Exit Sub

> > > > 'Confirm that the user wants to add the new item
> > > > Msg = "'" & NewData & "' is Not in the list." & CR & CR
> > > > Msg = Msg & " Do you want to add it?"
> > > > If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
> > > >     ' If the user chose not to add a customer, set the Response
> > > >     ' Agrument to suppress an error message and undo changes.
> > > >     Reponse = acDataErrContinue
> > > >     'Display a customized message.
> > > >     MsgBox "Please try again"
> > > > Else
> > > >     ' If the user chooses to add the item open a recordset
> > > >     ' Using the table you wish to add it to
> > > >     Set Db = Current
> > > >     Set Rs = Db.OpenRecordset("tblDegrees", DB_OPEN_TABLE)
> > > >     'Let code execution continue if a run-time error occurs
> > > >     On Error Resume Next
> > > >    Rs![DegreeName] = NewData

> > > > If Err Then
> > > >     ' If a run-time error occured while attempting to add a new
> > > >     'Record, set the Response argument to suppress an error
> > > >     'message and undo changes
> > > >     Response = acDataErrContinue
> > > >     ' Display a customized message
> > > >     MsgBox Error$ & CR & CR & "Please Try Again.", vbExclamation
> > > > Else
> > > >     'If a run-time error did not occur, set response argument
> > > >     ' to indicate that new data is being added.
> > > >     Response = acDataErrAdded
> > > > End If

> > > > End Sub



> > > > > For a better analysis you'd have to post the code.  Are you using
a
> > > > > recordset, by any chance?  One common cause of errors in code
copied
> > > from
> > > > > A97 to A2002 is that A2002 doesn't include a reference to DAO by
> > default
> > > > (it
> > > > > defaults to ADO), and even wth such a reference ADO objects have
(by
> > > > > default) a higher precedence than DAO objects with the same names.
> > Both
> > > > ADO
> > > > > and DAO define a Recordset object, so if you have code like this:

> > > > >     Dim rs As Recordset

> > > > > it will normally be interpreted by A2002 as an ADO recordset,
which
> > has
> > > > > different properties and methods.  For safety's sake, you would do
> > well
> > > to
> > > > > "disambiguate" the declaration by making it like this:

> > > > >     Dim rs As DAO.Recordset

> > > > > If that doesn't compile, you need to open the Tools ->
References...
> > > > dialog
> > > > > and check the reference for the DAO 3.6 Object Library.

> > > > > Of course, you could have a completely different problem.  For
that,
> > > we'd
> > > > > need to see the code.
> > > > > --
> > > > > Dirk Goldgar
> > > > > www.datagnostics.com

> > > > > (to reply via e-mail, remove NOSPAM from address)



> > > > > > All -

> > > > > > I had a code in Access 97 that would allow users to add and item
> to
> > a
> > > > > combo
> > > > > > box if it wasn't already one of the choices.  This code is
{*filter*}
> > in
> > > > > Access
> > > > > > 2002.  Can someone direct me to anything that would help?

> > > > > > Thank you,

> > > > > > Melanie



Mon, 03 May 2004 05:14:23 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Hyperlinks/ExtraInfo html doesn't work in 2002

2. Converting VB Code from Access 97 to Access 2002 or higher

3. Code converted from macro doesn't work

4. Access 2000 VBA code not working Access 2002

5. closing access from code doesn't work, see further down

6. Access Code Doesn't Work

7. old code - new browser - code doesn't work

8. Code doesn't work (from Dev's site)

9. Procedures for Converting an Access 97 MDB to Access 2002

10. Converting Access 2.0 to Access 97/2002

11. Converted macro doesn't work in runtime.

12. Converted macro doesn't work in runtime.

 

 
Powered by phpBB® Forum Software