0

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

Improve this question
1
  • Have you got a form? BTW those field names will drive you insane - most people recommend avoiding anything but alpha and (if you really must) numeric in column and table names, eg, LastName. Commented Nov 6, 2012 at 9:55

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

But it still can't open recordset
@DanilGholtsman What error is it giving you? What is the value of strQuery at the point of the error? (View -> Locals Window)
well Query is okay i think cuz idbynameQuery : "SELECT [Authors].[author_id] AS [ID] FROM [Authors] WHERE [Authors].[last_name] = Dostoevsky" : Variant/String it highlighting with yellow color this >Set authorsRS = dbs.OpenRecordset(strQuery, dbOpenSnapshot) and says that run-time error 3061 and that there should be more parameters
Yes, because as I said, the query should be SELECT [Authors].[author_id] AS [ID] FROM [Authors] WHERE [Authors].[last_name] = "Dostoevsky" (with quotes around Dostoevsky). Otherwise, Access evaluates Dostoevsky as an expression - either a field or a named parameter, and doesn't find any field/parameter named Dostoevsky
@DanilGholtsman Can you tell me what was missing in the body of the answer, that cleared things up for you in the comments?
|
0

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.

Improve this answer

2 Comments

oh i always avoiding of using all this master forms and actually don't know how to use it properly. where can I find some tutorials and examples about it?
office.microsoft.com/en-ie/access-help/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.