T Nation

Help With MS Access

I’m working on a library database for a small non-profit group, and I could really use some help.

I have 3 tables and two junctions, as follows:

tblTitles
TitleID (PK Autonumber)
Title
Edition
Year
ISBN
Publisher
PubLoc

tjxTitleAuthors
TitleAuthorID (PK Autonumber)
TitleID (FK)
Author (FK)

tblAuthors
AuthorID (PK autonumber)
AuthorFName
AuthorLName

tjxTitleKey
titleID
KeywordID

tblKeyword
Keyword ID
keyword

tblTitle is the main table, with tblKeywords and tblAuthors connected via tjxTitleKey & tjxTitleAuthors, respectively.

I am working on a form so that anyone can enter books into the database. I have continuous display subforms (comboboxes) to list all authors and all keywords for each book. With authors, I also have the first and last name joined (lname, fname), so that the rowsource for the combobox reads:

SELECT AuthorID, AuthorLName & ", " & AuthorFName AS Fullname FROM tblAuthors ORDER BY AuthorLName, AuthorFName;

Now here’s my problem: I can’t set LimitToList = no, and I’m having trouble coding a NotInList event. I have tried the following code:

Private Sub Combo4_NotInList(NewData As String, Response As Integer)
Dim strFirst As String, strLast As String
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl

strLast = Left(NewData, InStr(1, NewData, “,”) - 1)
strFirst = Right(NewData, Len(NewData) - InStr(1, NewData, “,”))

strMsg = "The Author, " & NewData & “, you entered is not listed!” & vbCrLf & “Do you want to add it?”
If MsgBox(strMsg, vbYesNo, “Not listed”) = vbYes Then
strSQL = "INSERT INTO Authors (AuthorFName, AuthorLName) "
strSQL = strSQL & “VALUES(’” & strFirst & “’, '” & strLast & “);”
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If

End Sub

This fails to run. I know nothing of VB, and I don’t know how to fix this. Does anyone have any ideas?