Converting to Access 2002 Code doesn't work
Author |
Message |
Melanie Dixo #1 / 9
|
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 |
|
|
Dirk Goldga #2 / 9
|
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 |
|
|
Melanie Dixo #3 / 9
|
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 |
|
|
Melanie Dixo #4 / 9
|
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 |
|
|
Dirk Goldga #5 / 9
|
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 |
|
|
Brendan Reynold #6 / 9
|
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 |
|
|
Melanie Dixo #7 / 9
|
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 |
|
|
Dirk Goldga #8 / 9
|
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 |
|
|
Melanie Dixo #9 / 9
|
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 |
|
|
|