Auto populate text from date

[losingmymind]

I need some help again...I'm creating a DB for tracking time. I have a form with a subform. The main form is the employee data, the subform is their time sheet. What I would like to do is when I enter the date on the subform the pay period number will auto populate in a text field. I have created a table with three columns - PK auto number, Date, and Pay Period Number. I created a relationship between that table and the time sheet table using the date. I don't know if that's right or not. I have read about auto populating from a combo box but that's not what I want. Can someone help? Thanks :-)

[Beetle]

Use DLookup in the Control Source of the Pay Period text box on the form. Example;

=DLookup("[Pay Period Number]", "tblPayPeriods", "[Date]=" & [Date])

BTW - Date is an Access/VBA Reserved Word and should not be used as the name of a user defined object (like a table field).

[losingmymind]

Thank you, I'll try that Monday and I'll let you know. And why shouldn't I use Date as a field name? I always get a warning message when I do and I just ignor it...what could happen if I use it? I don't really understand because if I name a field DATE, that's all I'm going to input there. hmmm....I have a lot to learn :-)

[Beetle]

And why shouldn't I use Date as a field name?

Because it is a Reserved Word. Certain words are used internally by Access and/or VBA to identify various objects or functions. For example, there is a built in function named Date() which can be used to return the current system date in a query, form, etc. If you use this as the name of one of your objects (like a table field), then under some circumstances the system may be unable to determine if you're referring to the built in function named Date or your field named Date. This may cause errors or unreliable results. See the link in my previous post for more.

[losingmymind]

Use DLookup in the Control Source of the Pay Period text box on the form. Example;

=DLookup("[Pay Period Number]", "tblPayPeriods", "[Date]=" & [Date])

BTW - Date is an Access/VBA Reserved Word and should not be used as the name of a user defined object (like a table field).

Ok, I changed my field names to "DateField" and tried the code but now I get this "#Name?" This is what I typed in..
=DLookUp("[PayPeriod]","tblPayPeriods","[Datefield]=" & [tblPayPeriods]![DateField]). I tried others ways and nothing happened. This is the closest I got...please can anyone help? Thank You :-)