why dont this work now - asny ideas please ???

[Event2020]

Hi

Can anyone help with the following code and why Access keeps chucking error messages
at me when it worked perfectly in a exact copy of the DB Its in.

The code is on the OnNotInList event of a txt box called txtArtistID which is on
my main data entry form called frmDataEntry, txtArtistID is a lookup field that
gets its data from a table called tblArtists

**************************CODE START***********************************
Private Sub txtArtistID_NotInList(NewData As String, Response As Integer)
Dim Db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not a recognised Artist"
strMsg = strMsg & "Do you want to add the artist to the list?"
strMsg = strMsg & "Click Yes to add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?&quot = vbNo Then
Response = acDataErrContinue
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblArtists", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtArtistID = NewData
rs.Update

If Err Then
MsgBox "Whooops, That didn't work. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub
****************************CODE END************************************

Here is what happens when I run it:

First I get a MS VB error popup box that says Compile error User-defined type not defined and
the words *Db As Database* are highlited in blue. Then when I click OK in the VB Msg Box
access un-highlites the blue but the highlites the whole line:
Private Sub ArtistID_NotInList(NewData As String, Response As Integer) in yellow.

I can not work out why it is doing this - can anyone help please.

Thank you,

Event2020

 

[MichaelRed]

For beginners, NotInList is not an event/property of a textbox. Your troubles appear to go beyond this, as the exact errors indicate that you have abducted code from a pre 2K mdb and attempted to use it as 'slave labor' in a 2K MDB. 2K -by default- will assume the record set needs to be an ADO recordset - however your syntax convinces it that db is not an ADO animal - hence the error you cite.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[KennyUK]

Hi Michael:

Many thanks for your reply, its strange that you should say what you did because BOTH databases were created in Access 2000.

The one that I am working on is an EXACT copy of the first in every way except that I am working on a new form design and just to re-add the original code.

Do you or anyone else reading this have any further suggestions on what this is and how I may put it right please.

Your help is greatly appreciated.

 

[jebry]

Hi!

I think Michael is still right in his basic contention that A2k is thinking you want this to be an ADO database and you don't. Your easiest solution is to go to the VBA window and click on Tools on the menu bar and select references. Find the reference to DAO 3.6 Object Library and make sure it is checked. After it is checked, use the arrow keys beside the list box and move the DAO reference up until it is above the ADO reference. This will make A2k assume your object is a DAO database.

hth
Jeff Bridgham
P.S. If you plan on using A2k extensively, it would probably be to your advantage to learn the ADO object model since it is the default model for A2k.

 

[KennyUK]

Hi jebry, well I did what you said and no more problems.

Thank you very very much.