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:
TitleID (PK Autonumber)
TitleAuthorID (PK Autonumber)
AuthorID (PK autonumber)
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 & “);”
Response = acDataErrAdded
Response = acDataErrContinue
This fails to run. I know nothing of VB, and I don’t know how to fix this. Does anyone have any ideas?