Adding comment in text field depending upon date

[brent01]

Firstly, many thanks to Jerry Dennison, for help on my last (similar) question... it worked!

I have simple database with two TABLES and one FORM.

Two of the fields in the form are a date field (short date format) and a text field. The date field, for this form, is applied to one of the underlying tables (SHIFTLOG)

The second table (DIARY), is a very simple table with two fields, the first is a date field named (DATE), the second is a text field named (COMMENTS) - Entries are added directly into this table, without using a form.

What I am trying to do is, get the text field on the form, to look at the DIARY table and see if there is an entry that matches the date entered, in the date field of the form. If a record is found, then to simply add the text contained in COMMENTS field of the DIARY table, into the forms text field.

Any ideas how to do this? As usual, I have tried using my limited knowledge, without success, so any help would be greatly appreciated.

Brent - Levi Strauss (UK)

 

[JoeMiller]

You could use the DLookup function:

MyTextBox = DLookup("[Comment]","Diary","[Date] = " & Me.DateControlOnForm)

HTH

PS - Date is a bad choice for a field name because Date is a reserved keyword in access (as well as a function name). It can cause you major headaches and be hard to find, so I would suggest changing it, CommentDate maybe?

Joe Miller
joe.miller@flotech.net

 

[brent01]

Thanks Joe,

Your suggestion worked -

I have used Dlookup, as you suggested, but couldn't get the '& Me.DateControlOnForm)' part to work.

I have constructed the statement as follows -

=DLookUp("[COMMENT]","DIARY","[COMMENTDATE] = [DATE]&quot

which is adding the correct comments to my text box. (I will change the field name from Date, as you suggested)

However, the text box is not updated with the text from my COMMENT field, immediately. I have to close my form, and then open it again, before any update occurs.

Any suggestion please.

 

[JoeMiller]

Sounds like you need to issue a requery to the text box in the appropriate event. Not knowing what events are going on, when this field should be filled etc... I can't tell you which event to use. Here's the code to use to requery the textbox.

TextBoxName.Requery Joe Miller
joe.miller@flotech.net