CODEWITHSUNDEEP
vbams-accessms-access-2007recordset
DanilGholtsman
DanilGholtsman

Reputation: 2374

GUI using VBA in access. Recordset from query

Making GUI in Access with VBA (first time i saw it in this semester and it looks unusual for me). I got table Authors where i got columns author_id, last_name, first_name and table Books with columns author_id, book_name. I got button on Form which on click should ask user to input author last name and then search and show all books of this author.

So I trying to find author id from Authors table and then from Books table show all books where author.author_id is equal to books.author_id.

I was thinking that i need to create temp query which contained author_id value and after that create record set with this query using SQLquery like "SELECT [Books].[book_name] AS [Bookname] FROM [Books] WHERE [Books].[author_id] = [test].[ID]" But i stucked here - I trying to just check if this thing working but it says there is an error 3061

Private Sub authorlist_Click()

    Dim dbs As Database, authorsRS, booksRS As Recordset, queryStr, idbynameQuery, srchASurStr, strOutput, srId As String, qdf As QueryDef

    Set dbs = CurrentDb()

    srchASurStr = InputBox("Input author surname, please", , , 100, 100)

    strQuery = "SELECT [Authors].[author_id] AS [ID] FROM [Authors] WHERE [Authors].[last_name] =  " & srchASurStr & ""



    Set authorsRS = dbs.OpenRecordset(strQuery, dbOpenSnapshot)

    With dbs
        Set qdf = .CreateQueryDef("test", strQuery)
        DoCmd.OpenQuery "test"
        .QueryDefs.Delete "test"
    End With


End Sub

So could you help me please to understan what's wrong? And is there maybe more simple way to show all books of some author (maybe without using SQL querys)?

Upvotes: 0

Views: 1661

Answers (2)

Zev Spitz
Zev Spitz

Reputation: 15327

String values in an SQL statement need to be surrounded with single-quotes (') or double-quotes ("):

SELECT author_id FROM authors WHERE last_name = "Smith"

If written without the quotes:

SELECT author_id FROM authors WHERE last_name = Smith

Smith will be understood to be a field name and not a string value. So your code should look something like this:

'Chr returns a string from a character code. 34 is the code for "
strQuery = "SELECT author_id FROM authors WHERE last_name = " & Chr(34) & srchASurStr & Chr(34)

In VBA, you can escape double-quotes with a string by doubling them:

strQuery = "SELECT author_id FROM authors WHERE last_name = """ & srchASurStr & """"

SQL injection: Keep in mind that if the user inputs a string with " in it, there will probably be an error, as the resulting SQL statement has invalid syntax:

SELECT author_id FROM authors WHERE last_name = "Smi"th"

The right way to avoid this problem is to use parameters.

Some notes:

  • You can reference a form control within a query: [Forms]![FormName]![ControlName]. Thus, you can create a saved query that filters based on a form textbox, instead of using an inputbox.
  • Consider using a combobox to have the user select from a list, instead of having the user type free text. The combobox can have multiple columns, with the value of the combobox being the first column (author_id) and the displayed value being another expression (last_name or last_name & " " & first_name). If you set the ColumnWidths property to 0 (for the first column), the next column will be displayed

  • If you prefer to use a textbox, consider using the LIKE operator in your query, to display all authors whose last_name contains the user string:

    SELECT author_id FROM authors WHERE last_name LIKE "%sm%"

    will return Smith, Smythe, and Asmodeus.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

I suggest you set up a form and subform. The form can contain author details and the subform can contain books by that author, you can further add a textbox that the user can fill in with part of the author name. You can then apply a filter to the main form to show all the authors with that name.

Me.Filter = "Author Like '*" & Me.Search & "*'"
Me.FilterOn = True

There are a number of variations on this, the user could select names from a combo or listbox. The form could be a continuous form with a filter / search in the header and so on.

Upvotes: 0

Related Questions