By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,777 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,777 IT Pros & Developers. It's quick & easy.

Populate a field on a form from another field & update table

beacon
100+
P: 579
Hi everybody,

I'm putting a database together at work for a committee, but one of their requirements is giving me trouble.

They want a table and a form that will track trips made by patients at a hospital. Two of the fields on the table, TripDate and DateValidation, also need to be on a form. I've got that part taken care of.

On the form, when the TripDate is entered, they want the DateValidation to be populated from the TripDate with the day of the week. For instance, if TripDate is 11/21/2008 they want Friday to appear in the DateValidation textbox and on the underlying table. Then they want me to disable the textbox so they can see what the day of the week is, but not allow users to change it.

Initially, I created the two textboxes and put
Expand|Select|Wrap|Line Numbers
  1. =Format([TripDate],"dddd")
in the control source property of the DateValidation textbox, but it isn't placing the value on the table.

I also tried using Before & After Update events for the TripDate and DateValidation textboxes (separately) with the following code:
Expand|Select|Wrap|Line Numbers
  1. dim myDate, weekDay
  2.  
  3. myDate = TripDate.Value
  4.  
  5. weekDay = Format([myDate], "dddd")
  6.  
  7. DateValidation.Value = weekDay
  8.  
I used the code above, or some small variation of it, but to no avail.

Does anyone have any ideas how I can implement this?

Thanks...
~ beacon
Nov 21 '08 #1
Share this Question
Share on Google+
5 Replies


RuralGuy
Expert 100+
P: 375
Since the DateValidation field seems to always be derived from another field in the record, there is absolutely *no* reason to waste disk space with the field. Simply calculate it in the query of the table and lock the control that displays it on the form.
Nov 21 '08 #2

beacon
100+
P: 579
I took your advice, but I may be missing something.

I moved my =format expression to the query that was used to create the form, but that still isn't populating the textbox on the form for DateValidation.

This may show my lack of knowledge with Access, but does a textbox have to have a control source to a field on a table/query for it to show up on the table/query?

Can I have a field on a form that calculates the day based on the TripDate field and then set the value of another field that has a control source on the table/query to the field that calculated the day? Perhaps using hidden textboxes?
Nov 21 '08 #3

beacon
100+
P: 579
How do I calculate it in the query of the table?
Nov 25 '08 #4

RuralGuy
Expert 100+
P: 375
Format([TripDate],"dddd") As TripDay in the query
and then bind TripDay to your TextBox control
Nov 25 '08 #5

beacon
100+
P: 579
Ok...I understand now. Thanks for your patience!
Nov 26 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.