JS Smith
New member
- Local time
- Today, 12:31
- Joined
- Mar 31, 2021
- Messages
- 15
The setup:
Table BuyOut includes in it's columns Company (Short Text) and Contract Number (Short Text)
Form COLog includes in it's text boxes Contract # and Company
Form COLog feeds table COLog. Both Contract # and Company are Short Text
Goal: User keys in a Contract # and the Company field is autofilled on the form with the corresponding Company name.
Reasons: User data entry issues; we never get the same Company name twice. It'll alert the user if they typed the contract# incorrectly. We'll want this for upcoming reporting, mail merges and a few other things so accuracy is required.
Understandings:
It's a big faux pas to have same data in different tables; this is designed to be a proverbial snapshot in time. This is how the business wants it.
It's probably better to have contract # be called the same name in both places but I'm dyslexic and this helps me not scramble everything up.
I've played for hours with many iterations of the DLookup and fallen down many Google rabbit holes, the latest attempt is:
Error: The expression you entered has an invalid date value.
How should this thing read? I can no longer think logically how to proceed. Thanks, in advance. for any help and guidance you can provide.
Table BuyOut includes in it's columns Company (Short Text) and Contract Number (Short Text)
Form COLog includes in it's text boxes Contract # and Company
Form COLog feeds table COLog. Both Contract # and Company are Short Text
Goal: User keys in a Contract # and the Company field is autofilled on the form with the corresponding Company name.
Reasons: User data entry issues; we never get the same Company name twice. It'll alert the user if they typed the contract# incorrectly. We'll want this for upcoming reporting, mail merges and a few other things so accuracy is required.
Understandings:
It's a big faux pas to have same data in different tables; this is designed to be a proverbial snapshot in time. This is how the business wants it.
It's probably better to have contract # be called the same name in both places but I'm dyslexic and this helps me not scramble everything up.
I've played for hours with many iterations of the DLookup and fallen down many Google rabbit holes, the latest attempt is:
Code:
=DLookup("[Company]","[99BuyOut]","[Contract Number]=" & Forms!COLog!Contract #)
Error: The expression you entered has an invalid date value.
How should this thing read? I can no longer think logically how to proceed. Thanks, in advance. for any help and guidance you can provide.
Last edited: