Why dont this work now - suggestions 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

 

[Alcar]

make sure you have the DAO library in your references.
To check your references go to the VB editor and click on:
[tools]-[references...]

check the

Microsoft DAO 3.6 Object Library

then he will recognize the database word..

Another thing, remember when creating long string, to put the SPACES:


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."

otherwise your text will appear like this on the msgbox:

' is not a recognised ArtistDo you want to add the artist to the list?Click Yes to add or No to re-type it.
HTH
Alcar

 

[KennyUK]

Hi Alcar,

Hey thank you very much for the advice, I didnt know about the long string and spaces bit.

It seems to be all working how I want now so all I can say is thank you very much for your reply.

Regards,

Event2020

 

[Nancy2]

It sounds like you might be missing the reference to the DAO library (in a module, select tools, references) add the dao library, if it's missing, and compile.

 

[Nancy2]

Sorry -- new to this forum - realized I repeated an answer someone already posted (did not read the whole post before positng)

 

[KennyUK]

Hi Nancy,

Hee hee - dont worry about it - its so nice that anyone replys at all.

Welcome to the forum and thanks for the post.

Kind regards,

Kenny