set default value to field in another table

[cheriherron]

I would like to have my "actual date" fields default to the value of my "projected date" fields in another table to eliminate a step for users. When I go to the expression builder (from table-design view), in the left column of the bottom section, I only see Function, Constants, & Operators. Functions will expand to Built-in Functions. When I see the screenshot in Help it shows the sample database listed in that column - but I do not see it. I am using Access 97.

Can this be done in this manner? Do I need to move all fields to one table? If it's possible, how can I set the value either way?

 

[kathryn]

Well, if you are using a form, you can use code to fill in the value for you, instead of using the default value.

Is there only one "projected date" value in the other table, or are there multiple values from which you pick using some criteria?

Kathryn

 

[cheriherron]

This is for a County HR Dept. They want to track job announcement, closing, interview dates, etc. There will only be one projected date per field which I want the actual field to default back to. If I can do this, then the clerks will not have to go back and complete the actual dates if everything goes as planned.

Do I set this up in the initial form so they can just tab through the actual dates fields? Can I set up a second form they can go to in order to correct the actual dates if they change without having to go through all the projected? If I'm not clear on what I'm asking, please let me know. Thanks for your help.

 

[kathryn]

Ok, let me make sure I understand you correctly.

You have a table with job announcements. This table has dates for the job closing, interviews, etc.

You want to have these dates be the default for fields in another table, which contains the actual dates that the jobs closed, the interviews were held.

Is there a field in both tables, maybe a job requisition number, that can be used to link the tables?

Do you need to save both the projected dates and actual dates for tracking purposes?

Is there a reason why the actual and projected dates are in two different tables?

Sorry for all the questions.

Kathryn

 

[cheriherron]

Yes, there is a job req # for linking the tables. Yes, we need to keep both dates for tracking - they want to calculate date differences. Uh-oh... analysts may start getting in trouble

I originally had these in one table - then read somewhere that I couldn't default to another field in the same table, so I split them. At this point, I can go back to one table very easily. I was misguided in my thinking about how this would be accomplished - I did not realize it was in the form. I will go whichever way you recommend.

Thank you very much for your help.

 

[kathryn]

Well, if they are in the same table, when the users enter the intial Projected date in a form, you could put code in the AfterUpdate of the field on the form to populate the Actual field with the same value. The easiest way I can see to do this is to have the Actual field on the form, hidden if you wish.

The code would look like this

sub txtProjected_AfterUpdate()

me!txtActual = me!txtProjected

end sub

I think that should work. Why don't you create a dummy table with just two fields, Actual and Projected. Create a form based on the table and add the two fields. Rename the Actual textbox to txtActual and the Projected textbox to txtProjected. Add the code above to the form and test it.






Kathryn

 

[cheriherron]

I will try that - thanks! I do not work there again until Monday, I may try to work on it at home, though.

 

[elizabeth]

BTW, rather than go to a table for the date, you might be able to use a calculation to fill in the date - you know, date of event x plus 30 days or whatever. And if the calculation is true across the board, you would not store the calculated result at all (storing calculations is generally a no-no in RDBMS). Instead you would calculate the date when you run a report or go to a new record in a form.