1

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
Ricardo Cabral
  • 31
  • 2
  • 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 May 16 '21 at 21:04
  • Exactly where are you calling this function from? – June7 May 17 '21 at 20:43

1 Answers1

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.

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

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

  3. build a query object that joins tables without filter criteria and use DLookup() expression in textbox
    =DLookup("categoryName", "queryname", "trainPlate='" & [Combo_Search_Comboio] & "'")

June7
  • 19,874
  • 8
  • 24
  • 34
  • 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. – Ricardo Cabral May 17 '21 at 09:19
  • 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 May 17 '21 at 16:56
  • Another is to include T_Category in form RecordSource and bind textbox to categoryName - set as Locked Yes and TabStop No. – June7 May 17 '21 at 19:46