Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: Finding Last Entry

    Hi,
    I have a table with records which have distinct ID No.s (Not autonumber though) The ID.s always go in increasing progression, so the most recent ID no. entered would be the highest number in the table.

    How would i write a function which will find the highest no. in the table?
    Could you put the code pls?

    Thanks a lot

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT * FROM MyTable ORDER BY UniqueField DESC;


    The 1st record will be the most recent ...

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    I would like to use the highest no. value to be put to a textbox.. so a query wont be helpful.

    Is there a way?

    It has to find the highest no. and then store it to a variable, say, HighestValue (int)

  4. #4
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Assuming you have a form bound to the table and the field is in your record source then in the textbox that you want to display the highest number, type in this:

    "=[MaxOfUniqueFieldName]" to get the highest number, or
    "=[MaxOfCandNumber]+1" to get the next number to use


    Regards - Andy

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    The value is in another table
    The table is called File_Table, with the ID field in question

    so,

    Textfield.Value = [MaxOf[File_Table].[ID]]?

  6. #6
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    No that won't work if you don't have the table in your forms recordset. I'm assuming your form is based on a table but you want to include an ID field from another table.

    I think the easiest thing you could do would be to create a subform based on File_Table and just have 1 textbox on the subform: the ID field.

    That will work fine but I suppose it depends what you're using the form to do?!


    Regards - Andy

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Jerrie
    I would like to use the highest no. value to be put to a textbox.. so a query wont be helpful.

    Is there a way?

    It has to find the highest no. and then store it to a variable, say, HighestValue (int)
    And to do something like:

    Dim MyConnect As ADODB.Connection
    Dim MyRecSet As ADODB.Recordset

    Set MyConnect = New ADODB.Connection
    Set MyRecSet = New ADODB.Recordset

    MyConnect.CursorLocation = adUseClient
    MyConnect.Open "DSN=SomeDSN;" ' OR YOUR PROVIDER INFO HERE ...

    MyRecSet.CursorType = adOpenDynamic
    MyRecSet.LockType = adLockOptimistic
    MyRecSet.CursorLocation = adUseClient

    SQLString = "SELECT * FROM MyTable ORDER BY MyField DESC;"
    MyRecSet.Open SQLString, MyConnect
    If MyRecSet.BOF = False Then
    MyRecSet.MoveFirst
    HighTextBox.Value= MyRecSet.Fields(0).Value
    End If
    MyRecSet.Close

    Wouldn't work???

  8. #8
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    MyConnect.Open "Front"

    Wat do you put in the place of the DSN? The file name? Cant you just connect to the database that is already open? .Open(this)?

    i dont know what to put in after .Open..

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Jerrie
    MyConnect.Open "Front"

    Wat do you put in the place of the DSN? The file name? Cant you just connect to the database that is already open? .Open(this)?

    i dont know what to put in after .Open..
    Quote unquote: OR YOUR PROVIDER INFO HERE ...

    Ex: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"


    This is from: www.connectionstrings.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •