-
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
-
hello koperski,
looking at your code there is one thing that springs to mind. Assuming you are using the standard biblio.mdb database, that the "year published" is defined as a numeric field (a number), then in your expression for:
Code:
'change this
criteria = "Year Published = " & "'" & response & "'"
'to this
criteria = "Year Published = " & response
'ie you don't need the single quotes around itbecause it
'is numeric - if it was a string you would need them
Actully, i think the system might be smart enough to figure this out - but another thing is that because the name of your field contains a space, you need make another change.
Code:
'so this
criteria = "Year Published = " & response
'should be then changed to this:
criteria = "[Year Published] = " & response
hopefully this fixes your problem. If it does not then i think it might fix a problem you would have got after you get past your current problem.
-
Thanks!, That gets me past the first hump. Now I have to find out why the field "year published" = response
returns every book and author and continuously loops until I Ctrl/Alt/Del to end the task.
Any hints appreciated.
-
not sure about the looping continuously - do you mean that it loops through for all records in the recordset?
i think you need to make some changes like:
Code:
'Change this slab of code:
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
'to something like this:
datBooks.Recordset.FindFirst criteria
Do While Not datBooks.Recordset.NoMatch
'NoMatch will eventually be true when the end of
'the recordset is reached - no more records found
lstRequest.AddItem datBooks.Recordset.Fields("Title")
lstRequest.AddItem datBooks.Recordset.Fields("Author")
datBooks.Recordset.FindNext criteria
Loop
See - the reason all records were being retrieved <actually i suspect it was all records after the first matched record in the database> was that the FindFirst method found the first match for criteria, but then you were doing a MoveNext afterwards and thus just moving continuously to the next record until the end of the recordset.
Does this help?
-
I believe we have a winner!
I have changed a few other items to make things happen at appropriate times but it appears all is good again.
Thank you so much!
I struggle with these assignments. I work independantly taking these courses off-campus. I have no one to compare notes with. This sight has been a gift, and so is your help. One day, I too may understand...
;)
-
No probs...what goes around comes around. I'd like people to take some time to help me out when i need it so i'm happy to do the same. Pretty much everyone else here feels the same way too - i'll second that notion about this site being a gift...a gift from the vb gods - :D