Return Query Value Using VBA function in Access

Question

I'm currently working on a project and I've been having trouble trying to get a function that is able to return the value of a query, which I do need in order to display it on a textbox. The current code is like this:

Public Function rubrieknaamSQL() As String

    Dim rst As DAO.Recordset
       
    Dim strSQL As String
       
    strSQL = "SELECT T_Train.trainPlate, T_Category.categoryName FROM T_Category INNER JOIN T_Train ON T_Category.id = T_Train.category_id WHERE (((T_Train.trainPlate)=[Forms]![F_Comboio]![Combo_Search_Comboio]));"

    Set rst = CurrentDb.OpenRecordset(strSQL)

    rubrieknaamSQL = rst!categoryName
    rst.Close
End Function

I should say that the code is copied from other publisher and I do not own its rights. However, it still won't work when I try to run it and the error displayed goes like this:

Run-Time Error 3061 : Too few parameters. Expected 1

and it happens in Set rst command.

It would be more useful to convert this function to one that accepts a value (Function rubrieknaamSQL(strData As String) As String), and use this value instead of the combo box value. This would allow you to use it across the application, whereas your current function will only work when the form referenced is open. Also, there is no need to return T_Train.trainPlate in the recordset as it is not used. Finally, you should Set rst=Nothing after you have closed it to tidy up objects properly. — Applecore
– Applecore, Commented May 16, 2021 at 21:04

June7 · Accepted Answer · 2021-05-27 16:30:53Z

1

For a SELECT query to set a recordset object, concatenate variable:

" ... WHERE T_Train.trainPlate=" & [Forms]![F_Comboio]![Combo_Search_Comboio]

If trainPlate is a text field, need apostrophe delimiters (date/time field needs # delimiter):

" ... WHERE T_Train.trainPlate='" & [Forms]![F_Comboio]![Combo_Search_Comboio] & "'"

For more info about parameters in Access SQL constructed in VBA, review How do I use parameters in VBA in the different contexts in Microsoft Access?

There are ways to pull this single value without VBA.

make combobox RowSource an SQL that joins tables and textbox simply references combobox column by its index - index is 0 based so if categoryName field is in third column, its index is 2: =[Combo_Search_Comboio].Column(2)
include T_Category in form RecordSource and bind textbox to categoryName - set as Locked Yes and TabStop No
build a query object that joins tables without filter criteria and use DLookup() expression in textbox
=DLookup("categoryName", "queryname", "trainPlate='" & [Combo_Search_Comboio] & "'")

edited May 27, 2021 at 16:30

answered May 16, 2021 at 19:06

June7

21.6k8 gold badges28 silver badges36 bronze badges

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

3 Comments

Ricardo Cabral Over a year ago

First of all, thank you for your answear, it actually cleared some things out. However, now I get an error and it says: Run Time Error 2186: This property isn't available in design view.

June7 Over a year ago

And why is that - is anything in design view? I've never had this error. There are ways to pull this single value without VBA. One is to make combobox RowSource an SQL that joins tables and textbox simply references combobox column by its index. Another is to build a query object that joins tables without filter criteria and use DLookup() expression in textbox.

June7 Over a year ago

Another is to include T_Category in form RecordSource and bind textbox to categoryName - set as Locked Yes and TabStop No.

Collectives™ on Stack Overflow

Return Query Value Using VBA function in Access

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related