One little problem, SQL joining tables... database

[koperski]

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

[funkyd77]

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:

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

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

[koperski]

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.

[funkyd77]

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:

'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?

[koperski]

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

[funkyd77]

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 -