Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    20

    Unanswered: show a text box content from last time it was field

    hello,

    I am building a sort of "task-notebook". the table has a field (type:memo) named for_tomorrow listing all the things the user want to do tomorrow. How do I present the field contents on the form in the next date he opens the form?

    (i have a field that lists the current date by defaulting Date(),

    I built two querys:

    no 1:

    SELECT Max(table1.Date) AS MaxOfdteOrderDate
    FROM table1
    WHERE (((table1.date)<=forms!form1!date));

    -- it sets the "biggest date" listed

    no 2:

    SELECT Table1.for_tomorrow
    FROM Table1, Query1
    WHERE table1.date=Query1.MaxOfdteOrderDate;

    -- the query lists the tomorrow field contents of the first query's result.

    I've made them run automatically when opening the form,

    But I can't figure it out how to make the second query's results on a text box... it won't work the regular way (that is putting "=query2.Value" in the control source.

    Can anyone help?

    Thanks,

    Adnaket

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Let's suppose you named the second query "Query2". You can use:
    Code:
    Private Sub Form_Open()
        Me.<TextBoxName>.Value = DLookUp("for_tomorrow", "Query2")
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Apr 2010
    Posts
    20

    thanks but it did't work

    Quote Originally Posted by Sinndho View Post
    Let's suppose you named the second query "Query2". You can use:
    Code:
    Private Sub Form_Open()
        Me.<TextBoxName>.Value = DLookUp("for_tomorrow", "Query2")
    End Sub
    Hi Sinndho,

    First - Sorry for the late reply - I couldn't check this earlier due to all kinds of things and so I didn't get a chance to show you my gratitude for you answering so fast. Thanks.

    Secondly - I checked it now and it didn't work - when using the above code I had a warning message saying "You tried to execute a query that does not include the specified expression 'for_tomorrow' as part of an aggregate function".

    Any idea what that means?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try:

    Me.<TextBoxName>.Value = DLookUp("[for_tomorrow]", "Query2")

    note the brackets around for_tomorrow
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is strange because it works when I try it (see the attached file). Domain functions (such as DLookUp) are known to return error codes and error messages that are not always accurate for the error generated. Are you sure that the queries (query1 and query2) are exctly as you described them? Also, where did you place the code I suggested? Finally, though I don't believe this can be the cause, which version of Access do you use? My solution, as well as the sample database joined to this post, uses Access 2003 but I've tested it with Access 2007 and it works too.

    More generally:
    1. You should not use reserved words to name objects in your database (Date is a reserved word in Access), it can cause a lot of problems.
    2. Except if you need them both for a different purpose, you do not need to create two different queries, you can combine them in a single one:
    Code:
    SELECT Table1.for_tomorrow
    FROM Table1
    WHERE table1.date=(SELECT Max(table1.Date) FROM table1 WHERE table1.date <=forms!form1!date)
    Have a nice day!

  6. #6
    Join Date
    Apr 2010
    Posts
    20

    thanks for the replies, guys,

    I really appriciate it, but both your solutions didn't work (pk, yours didn't work - it showed me the aggregate warning again and Sinndho,well, the last query you posted worked, but I can't figure out how to make the form show the results of the query for the date before the current...).

    I am so ashamed such a tiny and idiotic thing is causing me such much mess...
    I'm attaching my mdb as I can find anything wrong with it, maybe you can. Sorry its a mess- I had to translate it hastly, so it doesn't look much. As for the saved keywords, I'll keep that in mind.

    and to questions:
    1. Sinndho, I couldn't find the file you attached, is there anywhere I didn't look (as I'm new and might have missed something) or did you forget?

    2. How do you post pieces of code on the message?

    Thanks a million,

    Adnaket
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is nomal: when the form opens, there is no value in the controls yet, while the query is dependent of a value in the [date] control on the form.

    I modified your form this way:
    Code:
    Private Sub Form_Current()
    
        Me.yesterday.Value = DLookup("for_tomorrow", "query3_lastdate")
    
    End Sub
    and it works.
    Attached Files Attached Files
    Have a nice day!

  8. #8
    Join Date
    Apr 2010
    Posts
    20
    OK, right now it shows todays 'for_tomorrow' field value, so maybe if I do an nested select it will choose yesterday's 'for_tomorrow' value.

Posting Permissions

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