Full Version: SetValue
UtterAccess Forums > Microsoft® Access > Access Forms
***I am a novice and do not know if this considered a proper design practice.***
Here is what I am trying to do. A calendar control selects a date. That date is then used as a Control Source in a text box, as the user needs to be able to view that date. A second text box next calculates a week-ending date, based upon that initial selection (i.e. 3-3-08 is selected on the calendar, and 3/9/08 is populated in the calculated control. These dates will not ever need to change as the weeks, for reporting purposes, are set in stone. What I need to do is capture the calculated week-ending date in a table. I thought perhaps using the SetValue option would work, using another text box, but I cannot seem to extract from the calculated control.
Any assistance is certainly appreciated. If this design is found to be reprehensible, please advise.
Thank you!
I wouldn't go so far as to call it reprehensible, but storing a calculated field in a table is a common mistake amongst beginners. Do a search here in UtterAccess for something called "Database Normalization" and you'll find lots of stuff about database design and structure.
Here's what I don't understand. Why are you storing the date in the first place? Is this date being used as a filter for reports on existing data that is date-sensitive? For example, you want to list all invoices created between date 1 and date 2? If this is the case, when you select a date for reporting, it would never be stored in a table.
I hope this helps.
- Sligo
To answer your question, yes, basically othe users will be pulling those beginning dates and ending dates, the ending being the calculated date (beginning date + 6). Those dates will never change, and as such they need to be captured because they are the only criteria from whcih to extract the rest of the data.
On short, I am receptive to any solutions. Is it possible to just capture that date using perhaps a SetValue in a macro?
Thank you again!
Let me get this straight...
You have a table (or set of tables) that contain data pertaining to something that is date related.
You have a form from which you want to generate a report. In order to generate the report, the user enters through some means (not at issue here) a date value.
You want the report to recognize this date value and calculate an actual beginning range and ending range (Sunday thru Saturday, for example), then use this range to fiilter the report.
If this is correct, then the two dates generated have no need to be stored in a table anywhere because you can filter a report based on values found on a form (forms!frmReport!txtFromDate and forms!frmReport!txtThruDate)
Going further, you have a button that opens the calendar control. The user picks the date and you want this date to establish the range.
No macros needed (and I don't particular care for them anyway)...
On the form, have two fields: txtFrom, txtThru, and your button that calls the calendar and returns it. (Or double-click in either of the fields, it doesn't matter, but I'm going on the premise that the date the calendar generates is returned to the txtFrom field)
Here's some code that would presumably go in a subroutine in the form, called by the after_update events in both fields and the event(s) that calls the calendar.
private sub ReDoDates()
     ' this routine sets the from and thru dates for the report so that
     ' the from date is always sunday and the thru date is always saturday
     me.txtFrom =  DateAdd("d", -(Weekday(me.txtFrom) - 1), me.txtFrom)
     me.txtThru = DateAdd("d", 6, me.txtThru)
end sub

No matter what date the user puts into me.txtFrom (the from date), it will always be reset to the first day in the range (in this case, Sunday), and the field me.txtThru will always be set to the following Saturday.
I hope this is what you're looking for, and I hope it helps.
- Sligo
First off, thank you for taking the time to reply.
few things: Users are entering the data into a form, which is in turn bound to a table. On this same form, the user will select the beginning date using a calendar control, which is also saved in the same table, and another text box will calculate the end date. I would like this date to be captured and saved in the table, if possible. Basically, all the data is being saved into one table and the values and dates will never change. Once computed, they are fixed. May sound strange, but the business is such wherein this happens to be the case.
For example, a user enters $10 into the Expected field. He then enters $5 into the Actual field. A third field, Adjustment, calculates the difference. Since this is a calculated control, I can't (rather, don't know how) to bind this to the table. So, there is a macro (your favorite) that has a SetValue command that binds this calculated amount to a fourth text box, which is in turn bound to the table. So, if a user makes an error, for example, in entering an amount into any field, the amounts are recalculated and a Make Table query creates anew the data.
Likely not at all the ideal way to do things, but I am learning.
Again, thank you for the replies!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.