0

I need the code to execute the following logic:
  - get a record with the maximum value of the "Sort" field;
  - get the value of the "Student IDS" field of the found record;

I made the code, but it does not work.
In the line valueSort =! [NameFieldSort] I get an error:
Run-time error '3265': Element not found in this family.

Code. Form "frm_03_03_Students_Tape". Button "AddRecor_btn" (Add). 

Private Sub AddRecor_btn_Click()
        Dim nameTable As String         ' Имя Таблицы
        Dim nameFieldID As String      ' Имя поля - `ID`
        Dim nameFieldSort As String   ' Имя поля - `Сортировки`

        nameTable = "тбл_02_Студенты"                   ' Имя Таблицы

        nameFieldID = "ИДСтудента"                          ' Имя поля - `ID`
        nameFieldSort = "Сортировка"                       ' Имя поля - `Сортировки`

        Call AddRecord_md.AddRecord(nameTable, nameFieldID, nameFieldSort)

End Sub

Code. Module "AddRecord_md". 

Public Sub AddRecord(nameTable As String, nameFieldID As String, nameFieldSort As String)
        Dim rst As Recordset
        Dim valueSort As Integer
        Dim strSQL As String

        strSQL = "select [" & nameFieldID & "], [" & nameFieldSort & "] " & _
            " from [" & nameTable & "]" & _
            " WHERE [" & nameFieldSort & "] = (" & _
        " select max([" & nameFieldSort & "]) " & _
            " from [" & nameTable & "])"

        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) '

        With rst
            ' valueSort = !Fields(nameFieldSort)
            valueSort = ![nameFieldSort]
         End With

   End Sub

Question.
How to choose a record and all the values of its fields by the maximum value of one of the fields?
enter image description here

Update - 1.
1. The table "tbl_02_Students" contains records - 12 pieces.

2. I am debugging. During debugging in the strSQL variable, I get a query: 

select [Student ID], [Sort]
from [tbl_02_Students]
WHERE [Sort] = (select max ([Sort]) from [tbl_02_Students])

I create a request in Access. 

select [Student ID], [Sort]
from [tbl_02_Students]
WHERE [Sort] = (select max ([Sort]) from [tbl_02_Students])

I ask Access fulfill the request.
I get an entry in which the field Sort`` =9, and should be 12`
enter image description here

3. The line of code valueSort =! Fields (nameFieldSort) throws an error Run-time error '3265': Element not found in this family. enter image description here

Update - 2.
1. I solved the problem "Update - 1.". p.1, p.2.
Table "tbl_02_Students". Field "Sort".
It was a text;
It has become a number.
The request is working correctly.

2. "Update - 1.". p.3.
Changed the line.
It became - valueSort =!Сортировка.
Everything works correctly.

Question.
1. How to use the syntax correctly, so that it can be in the string valueSort =!Сортировка. pass the field name as a variable?

braX
  • 11,506
  • 5
  • 20
  • 33
eusataf
  • 807
  • 1
  • 12
  • 24
  • The real problem is not about the SQL query or getting the record with a max value. The problem is with correct syntax and verifying inclusion of columns, etc. Consider the meaning of the error text... it cannot find the "element" (i.e. column) that you request, so something is wrong with the column name code. What about line that you have commented out (`valueSort = !Fields(nameFieldSort)`), does it work? Since you are trying to retrieve a column which name is in another string variable, you must use the commented syntax. Otherwise VBA is trying to find a column named "nameFieldSort". – C Perkins Aug 10 '19 at 19:33
  • See https://stackoverflow.com/questions/2923957/bang-notation-and-dot-notation-in-vba-and-ms-access – C Perkins Aug 10 '19 at 19:40
  • @CPerkins I will study the link you made. Updated the question. See "Update - 1". Did I manage to answer your question? – eusataf Aug 10 '19 at 19:51
  • 1
    Part of problem may be Sort field is text. This means data uses alpha sort rules - one character at a time. "10", "11", "12" sort before "9" so "9" is maximum value. Change Sort field to number type or convert values in code. – June7 Aug 10 '19 at 20:09
  • @June7 Updated the question. See "Update - 2". – eusataf Aug 10 '19 at 20:22

1 Answers1

2

Use dot (.) with Fields collection:

.Fields(nameFieldSort)

Or if you know the index position of field:

.Fields(1)

Or don't use With rst and .Fields not needed:

rst(nameFieldSort)

rst(1)

Use bang (!) with the actual field name: !Sort, !Сортировка

June7
  • 19,874
  • 8
  • 24
  • 34