Full Version: Default Value for Date in a Combo Box
UtterAccess Forums > Microsoft® Access > Access Forms
I have a combo box (named fldCBStart) in a form that I want to retain whatever value was last entered into it; whenever it is reopened. I believe the code I need to use in the After_Update event is:
e.fldCBStart.DefaultValue = Me.fldCBStart
Obelieve I am experiencing a Date/Time issue, since any and all selections appear as 12/30/1899. Everything I've tried to do to make Access see the selection as "Date" and not "Time" has so far been ineffective: I have tried formatting the combo box as shortdate, I have tried formatting the Select Syntax with CDate. I have even tried using Format$(DatePart.. all to no avail.
Does anyone know what I am doing wrong?
Check out fill record with data from previous record automatically
Jack Cowley
Me.fldCBStart.DefaultValue = "=#" & Me.fldSBStart & "#"
ill carry forward to the next record the date selecte as long as the form is open . If you close the form when it reopens it will no longer have the last value you enter showing and will be blank. If you want to carry the value forward after the form is closed and reopened then look at the code supplied by William.
Thanks William and Jack. I went to the article and followed the instructions; activating the Microsoft DAO. I pasted in the module; renamed it to "modAuto_Fill_New_Record"; then set the Form's "OnCurrent" event property to =AutoFillNewRecord([Forms]![Daily PACE Replace]). On the bright side, it now sees the selection as a date; the actual date selected. But when I close the Form and re-open it, the value I had previously selected is gone and just a blank appears.
I am using the entry to command many queries to select this date as the StartDate for the query, but it does not actually apply a value to a table when a selection is made. Is this part of my problem?
Jack Cowley
Are you saying that when the form opens you need a date to show in the combo box because queries rely on that date for criteria? Do the queries ran right after the form is open or do you have to click a button to start the queries? Either way I think I would save the date in a field in the table OR in a single field table so your queries can get the data without using the combo box. You can use the combo box to save the date of your choice in the field or in the table. Also, is the combo box based on a table of dates? If not, where are the dates coming from as the Row Source for the combo box?
I agree with Jack. I tried something like what sorbit is asking and had a horrible time trying to get it to work.
Oadded a text field on the forms, then used the following in the After Update and it does exactly what i needed. Maybe this will help...
e.(addtextfieldhere)= Date$
Thanks Jack and Scott. Yes, I am using the combo box to display the dates that the queries use as criteria. The queries do not automatically fire off. Instead, there is command box with an option group (Monday thru Friday). The bullet you choose determines the reports that fire off for that day. One of the reports in this group's date range always starts the friday before last, continuously, until the next Friday arrives, at which time the friday selected moves up a week. That's why I want the StartDate to remain in place until the new rolling 2-week period starts.
And Yes also to question number 3: the combo box grabs its values from a table that lists dates only.
Adding a text field on the form? This might seem really dumb, but I don't know how to go about doing this..
Have not read the entire post and apologize if off base on this but if you are running your reports every two weeks why not use the DateAdd function in your query set to the two week period?
Thanks for jumpin' in John! Actually, the reports are run every day. The startdate is adjusted just once a week: each friday. Thus the friday report is a 5 day report, the monday report is a 6-day report, etc. until you get to friday where 2 reports are run: a 10-Day report (FINAL) and 5-day (report for the new rolling 2 weeks).
I guess what I am saying if I understand you correctly is why have to adjust the Date/s and also assuming that they are the same report/s the dateadd function will run All reports from your query Using a select case SQL Or a Between Start Date and End date
Week Beginning
Week Ending
Would Something Like that work?
Jack Cowley
On Friday, May 5th you run a 5 day report. On Friday May 12th you run a 5 day and a 10 day report. On Friday, May19th you run a 5 day report and on Friday, May 26th you run a 5 day and a 10 day report. Do I have that right?
If so, why do you need the combo box? You can save the May 5th date in a field in a table, check the date each Friday, and if it has been two weeks the run the 10 day report and update the date in the table to the current Friday date so you can start the process again
Just a thought sparked by John's DateAdd suggestion...
Good Afternoon to you Hope things are well with you and your loved ones on this fine Monday afternoon!
Just reading over your last post and tossing it around it seems like a parameter query would work best for this to me using maybe a date picker for clarification to the user and a begin and end date??????????? If there is a need for two seperate reports or more use an option group to open the seperate report/s. From the original post I got that they have a need for a daily, a 5 day (assuming for example from todays date-5) and a 10 day (assuming todays date -10)
how do you intrepet it?
Jack Cowley
Good afternoon John!
All is well in the Bay Area and the rain has finally gone (thought I was in Tacoma for a while yesterday) and the sun is out!
I am not sure how his reports are set up, but it sounds like they may be 'automatic'. I do not know if he uses criteria to filter the reports or no parameters at all. My thought was to just use code -
If Format(Date, "ddd") = "Fri" Then
If DateAdd("d", 7, DLookup(date in table)) = Date() Then
..do 5 day report...
ElseIf DateAdd("d", 14, DLookup(...)) = Date() Then
...do 5 day and 10 day reports...
...update date in table...
End If
I have not taken into account Monday through Thursday for the daily reports but similar code should do the trick.
Do you think that would do the trick based on what we know about what the OP wants to do?
I trust you and your family are well and the sun is shining in Tacoma!
Yes Thank You All is well here in Tacoma although while the weekend was really perfect it has since turned to showers and a normal Tacoma day frown.gif:)Going down to Boise friday to see the new Grandson (3rd Grandchild) apart from that things are pretty much normal.
Yes I think that would work fine just as you have it. Guess we will find out!
Have A Nice Evening!
Jack Cowley
John -
While in Boise you can stop by Fish And Game and say hello to my daughter, if she is working that day... Just kidding as you have more important things to do!!!
You have me beat in grandchild quantity department, but I have you beat in their age department!!! Ya win some and ya lose some...
Hey! I will be pulling my hair out sounds like a good idea to me as both my son and I like to fish. No comment in the age department do not want to give away either my age or yours!
Jack Cowley
There you go! I know there is a stuffed fish on the wall behind where she sits, but I guess you have to catch your own... Just tell her (her name is Virginia) that I told you to tell her to give you a free fishing license...but I don't think she is in that department. Another thing - if you son and daughter-in-law took the birthing class from St. Lukes or one the other hospitals my daughter could have been their instructor! She teaches birthing classes and as will as sibling classes for little kids who are about to get a new brother or sister. And she show parents how to properly install child safety seats, also through the hospitals.... Wouldn't it be something if she knew who they were? My, my, my....
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.