-
Assignnment: Consider the database BIBLIO.MDB. Write a program that requests a year as input and then displays in a list box the titles and authors of all books published in that year. The program should use the Find method to locate the books.
Biblio.mdb contains the following tables:
Title Author - field1: ISBN field2: Au_ID
Authors - field1: Au_ID field2: Author
Titles - field1: year published field2: ISBN field3: title
This is what I have:
Private Sub cmdFind_Click()
Dim strSQL As String, response As String
Dim criteria As String
response = InputBox("Enter the year " & _
"to search.", "Books Published")
strSQL = "SELECT Authors.Au_ID, Authors.Author, [Title Author].ISBN, [Title Author].Au_ID, Titles.Title, Titles.[Year Published], Titles.ISBN " & _
"FROM Titles INNER JOIN (Authors INNER JOIN [Title Author] ON Authors.Au_ID = [Title Author].Au_ID) ON Titles.ISBN = [Title Author].ISBN" & _
" ORDER BY Titles.[Year Published]"
datBooks.RecordSource = strSQL
datBooks.Refresh
criteria = "Year Published = " & "'" & response & "'"
If Len(response) > 0 Then
datBooks.Recordset.FindFirst criteria
Do While Not datBooks.Recordset.EOF
lstRequest.AddItem datBooks.Recordset.Fields("Title")
lstRequest.AddItem datBooks.Recordset.Fields("Author")
datBooks.Recordset.MoveNext
Loop
If datBooks.Recordset.NoMatch = True Then
MsgBox "Unable to locate requested year.", , "Not Found"
End If
Else
MsgBox "Must enter a year", , ""
End If
lstRequest.Clear
End Sub
Private Sub Form_Load()
datBooks.DatabaseName = App.Path & "\biblio.mdb"
datAuthor.DatabaseName = App.Path & "\biblio.mdb"
End Sub
I get an error: Run-time error '3077':
Syntax error (missing operator) in expression.
First... well second database assignment - - HELP
-
Hi not sure about your find method as notused it byut my sql would be
dim db as database
dim rs as recordset
dim year as string
yea = txtYear
set db = opendatabase(app.path & "\biblio.mdb")
set rs = db.openrecordset("SELECT t.title, a.author FROM titles t, authors a, titleAuthor ta WHERE t.isbn = ta.isbn AND ta.au_id = a.au_id AND t.year published = ' " & year & " ' ")
if not rs.recordcount. EOF then
blah blah blah
else
msgbox("No Books published that year")
end if
rs.close
db.close
nuts
also mumof3/with no time
