[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

autopopulate second field

Posted on 2011-05-12
34
Medium Priority
?
302 Views
Last Modified: 2013-11-05
See attached screenshot. When I enter the "From" date (by using the calendar control), I want the "To" textbox to automatically get filled in with 7 days after the "From" date.  I have this code and I know it works...

Me.toDate.Value = DateAdd("d", 7, Me.fromDate.Value)

I can't seem to find the right event for this to work.  The change event only works on the 2nd time it's been changed.  These are both unbound fields.
ee-daterange.jpg
0
Comment
Question by:gcgcit
  • 12
  • 7
  • 5
  • +3
34 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35748513

me.to=dateadd("d",7,Me.From)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35748520
place the codes in the lost focus, or exit event of textbox from
0
 

Author Comment

by:gcgcit
ID: 35748548
problem is... its the only thing you select on the form before press view report... so it sits empty until when you press view report
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:danishani
ID: 35748550
Try the On Enter event of your DateField From and place your code:

Me.toDate.Value = DateAdd("d", 7, Me.fromDate.Value)

HTH,
Daniel
0
 
LVL 12

Expert Comment

by:danishani
ID: 35748576
I that dont work, place it on the OnExit event of your DateField...

Daniel
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35748578
did you try it?
0
 

Author Comment

by:gcgcit
ID: 35748595
i've put it on all the events... none work right... its wierd.  Its almost like I have choice but to fire it from a button (which is annoying).  What I provided on the screen is the entire form... you just select one date, the 2nd one should autopopulate then you click view report.  OnExit or LostFocus only fire when I click View Report... I'll try to set the focus to the button maybe?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 35748611
hi cap, hows it going


>> When I enter the "From" date (by using the calendar control),

did u try the afterupdate event of the calendar control?



0
 

Author Comment

by:gcgcit
ID: 35748627
rockiroads: its just the textbox in 2010 that pops up a calendar control... not sure i have control over it
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35748630
howdi rocki,

the afterupdate will not fire when you assign a value from calendar control or from vba codes.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35748643
<OnExit or LostFocus only fire when I click View Report.>

so, what is your problem with this?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35748659
<ot>rocki, what have you been doing? </ot>
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 35748682
have u looked at the code behind this form? is there anything in formload that automatically brings up the cal control?
0
 

Author Comment

by:gcgcit
ID: 35748685
the problem is... the person clicks the "From" they aren't likely to press "view report" when the "To" field is still empty... so I'm trying to figure out a way to display it for them to prompt them that all information is entered but save them from figuring out what 7 days from the date they picked is
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 35748690
yo cap, been busy with job and apt hunting plus no internet at home. now I have internet :)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35748728
if they will accept a +7 days range for the report, just add it to the filter of your report

post the codes that you are using to open the report


 


0
 
LVL 26

Expert Comment

by:Nick67
ID: 35750116
So, this is really a question of esthetics.
The after update event will do what you want, and the report opens correctly, but you don't see the end date until you click the button.
Which is bothersome.

Am I correct?

So you are looking for something to change the second textbox, while the first still has the focus.
And the change event doesn't do it because the calendar control doesn't fire the Change event by itself.

The coding won't be elegant, but....
If you give your first textbox  OnGotFocus and OnLostFocus events that change the timer value from 0 to 250 (1/4 second) and back...
Then you could put your date add code in the Timer event and you should get your desired result.
Testing for null, and all those other nice things of course, should the user click in and then dally.

Catch my drift?
0
 

Author Comment

by:gcgcit
ID: 35755729
Nick67: It's totally esthetics... I'm digging that timer control idea.  What would the timer code be on the got focus event?

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35755957
Here it is shimmed up for you.
timer.mdb
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 35756006
Part of the key to it is the .Text property instead of the .Value
At any snapshot in time a control ALWAYS has a .text property
You can only read it when the control has the focus though.

Before you ACTUALLY leave a control that was null when you entered it, it has no .Value, only .Text

Now that I think about it, the key press event is actually MUCH more elegant for this

Private Sub fromDate_KeyPress(KeyAscii As Integer)
On Error Resume Next
Dim mydate As Date
If Nz(Me.fromDate.Text, "") = "" Then Exit Sub
mydate = CDate(Me.fromDate)
If IsDate(mydate) = True Then
    Me.toDate.Value = DateAdd("d", 7, Me.fromDate.Text)
Else

End If
End Sub

Timer always has the bleed in effects to the VBA editor that are annoying!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35756024
But, on second thought, I am in Access 2003, with no calendar picker, so KeyPress may not work for your setup!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35756232
Nick:  The problem I'm having with an unbound Form is trying to find an event that fires after keyboarding in some data in a textbox and then pressing Enter.  If you bind the Form to a dummy table, the AfterUpdate event fires.  Unbind the form - no event.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35756676
Until you leave the control, the AfterUpdate doesn't fire.
This is exactly the problem.

The author would like to see the second control get a value 'before' leaving the control, because the user is clicking a button that opens a form next.
The control does get updated and the report works, but because the report covers over the form, the end user doesn't see the value before clicking, and has to take it on faith that the date range is what is expected.

<Unbind the form - no event. >
In the shim I posted, if you put an afterupdate event to fromDate

Private Sub fromDate_AfterUpdate()
MsgBox "I fired"
End Sub

and key something into fromDate and hit enter, it fires, so the form being unbound is not an issue in Access 2003 at least.
Are you asking something related to the author's problem and what I posted
<The problem I'm having with an unbound Form>
Or related to stuff you have done where you have had the same operational requirement?
I'm confused.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35757052
I have a small form, and a textbox.  The form is not bound.  The form is in the View mode, I type something in the textbox, press Enter, the cursor moves to the beginning of the entry in the text box.  I had placed a msgbox in the Afterupdate event of the textbox.  In my case, the AfterUpdate event did not fire when the form was not bound to a table.  It did fire when I made the control source of the form a local table.  I'm not making this up.  My previous comment was posted because of a similar requirement - but your results are at odds with what I experienced.  I'm running A2003 under XP Pro.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35757107
Only one control that can take the focus, maybe?
If there's nothing bound, and only one control, maybe the focus can't go anywhere else and so no afterupdate occurs?
If it's bound and only one control, then it'd go to a new record!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35761219
As a matter of fact, there was my test text box another text box, and two subforms each with a form as the source object.  Go ahead and try it.  Create a small form with two textbox controls.  Put the msgbox in the AfterUpdate event of one text box.  Open the form, type some data in the text box and press Enter - no message.  Now bind the form to any table - type in data and press enter - presto - the message.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35762031
I did try it.  With the very form I created and posted to this question.  Unbound.  No table in existence whatsoever.  I put in an afterupdate event.  Commented out all the other events.  Typed in 13-May-11.  Pressed enter.  My afterupdate event fired.  Why your db doesn't behave the same I don't know.  If you take the stub I posted and do the same, what happens?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35762063
Here it is, done up as described.
Works in Access 2003 and Access 2010
with an afterupdate event--this is not a solution for the author though!
timer.mdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35762071
It's funny, the keypress event works so elegantly in Access 2003, but it doesn't give the right result in Access 2010, even if you type in the date manually.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35765177
I downloaded the stub at http:#a35762063.  I opened the form, typed in a date in the ToDate textbox, press Enter, and nothing happens.  If I move to the next text box or to the command button, the date in FromDate advances a week from that displayed in ToDate.  Question - who has the sick machine?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35765754
Well, given that the behavior I've noted happens on all of my domain machines, and my home machine, and in differing versions of Access, we have a mystery.
To all the other posters in this question:
If you download the stub, enter text in the textbox with the after update event and press enter, what happens?
On all my machines, an after update evnt occurs.
On @GRayL's machines, nothing occurs.

Please post what happens for you.

Thanks
Nick67
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35765822
Since my db doesn't work for you, and it works for multiple machines in my control, but not in my domain, it has to be a global setting
 properties window@GrayL,
Do you have 'Don't move' as a default?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35772638
No, but I had Next Record, and as there was no recordset, I guess it did not move.  I changed it to Next Field, and all became right with the world.  Very shrewd dude!  Thanks for sorting that out.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35772679
No Biggie.
Canucks helping Canucks
Albertans helping Albertans
Survivors of the meatgrinder known as U of A lending mutual aid.

Its all good!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question