Results 1 to 14 of 14
  1. #1
    Join Date
    May 2004
    Posts
    37

    Unanswered: Goto Last Record that is not "None"

    Hi,

    In the Forms OnOpen Event, I want a textbox (txtICL) to display the last record that is not "None".

    How would I go about doing this?

    Thanks a lot!

  2. #2
    Join Date
    Jun 2004
    Posts
    96
    do you mean go to the last record that is not 'blank' or empty?
    you can try docmd.gotorecord.. it let's you go to first, last, newrecord, etc..

    Cyherus

  3. #3
    Join Date
    May 2004
    Posts
    37
    No, I have a table that has values such as "Math, Science, History, etc." The rest of the records have "None" as their values. When the form opens, I want it to go to the last record (such as "Math") that does not have a value of "None".

    Basically, I want the form to go the the record right before the "None" records begin.

    Is this possible?

    Thanks!

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select Top 1 *
    from table
    where NOT column = 'NONE'
    ORDER BY column DESC
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    May 2004
    Posts
    37
    r123456,

    I have several questions:

    1. What does "top 1" mean?
    2. Do i change "column" to a field in the table?
    3. Where would I put this code?

    Thanks!

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    1. First Record
    2. Replace column with the criteria that determines 'MATH', 'NONE' etc
    3. Query => Design View => Right Click => SQL View
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    another way would be to create a function whichs loops through the recordset and analyzes the value until you find the one you want.

  8. #8
    Join Date
    May 2004
    Posts
    37
    r123456,

    What do you mean by "First Record"?

    Thanks!


    TerpInMD,

    I was thinking about that too but I haven't been able to do this. I tried Do....Loop, but it didn't work! Could you give me an example of the code?

    Thanks!

  9. #9
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Function ReturnValueBeforeNone() as String

    Dim db as dao.database
    dim rst as dao.recordset
    Dim strSQL as string

    Set db = currentdb

    strSQL = "You_SQL_statement_Text"

    Set rst = db.Openrecordset(strSQL, dBOpenSnapshot)

    With rst
    if rst.fields("Your Field Name").Value <> "None" then
    .movenext
    else
    .moveprevious
    ReturnValueBeforeNone = rst("Your Field Name").value
    end if
    end with




    ____________________________________-



    I just wrote that so hopefully it works

  10. #10
    Join Date
    May 2004
    Posts
    37
    Thanks!

    Would I put this code in a module or in the form's OnLoad or OnOpen event?

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I strongly recommend that you do not iterate through the result set on the client.

    "Select top 1 *" returns the first record returned from the query.

    Given the following table:

    A | 1
    NONE | 20
    B | 2
    C | 3
    NONE | 10

    Select top 1 *
    from table
    where NOT columnA = 'NONE'
    order by columnA DESC

    returns:

    C | 3
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    you would put that code in a module, and then reference it like:

    MyTextBox = ReturnValueBeforeNone()


    when you want to push it into the text box (OnOpen for example).



    To comment on iterating through a recordset: so long as your recordset has criteria and is not returing thousands and thousands of records I think you would be fine. In your case I bet your not returning more than say 10 records (If your criteria is a student), so use which ever method you find more straight forward.

  13. #13
    Join Date
    May 2004
    Posts
    37
    The maximum number of records that I can have is 12,000! Will I run into any problems?

  14. #14
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Try both ways see for yourself. 12K is a lot and I would think that soemthing is wrong with your query. Are you looking for one instance of none is 12K records?

Posting Permissions

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