Forms: Populate next field based on previous field entry

[jbodford]

I am sure this is a simple question, but my brain is currently operating at "sub-simple".

I have a form used to collect data that populates a table. When I enter a value into one field on the form, I need that same data to appear in another field on the same form.

1) Which event will do that: AfterUpdate on the first field ?, or BeforeUpdate on the next field? (or GotFocus. LostFocus, etc???)

2) Can you help me on how that event procedure would be written? This is new for me and I think if I could see an example of how it should be written, that would really help. All I need is to take the entered value from the first field and duplicate it in the second field, so that it doesn't have to be retyped for each record. Then, of course, if/when the data for the second field truly needs to be different, it would be retyped on the form. The event procedure will save keystrokes for the occasion when the value should be the same for both fields. The purpose is to set a default for the second field based on the previous field's entry. Any/all help is greatly appreciated.

 

[RoyVidar]

I think I'd prefer after update on your first field, and I'm presuming this is not a continous form, but just one record.

Select your field1, go to properties, find AfterUpdate and use the button at right. Choose Code builder

Between the header and footer of the sub, try

me!txtField2=me!txtField1

assuming you've named the textcontrols txtField1 and txtField2.

Of course, this might be a bit confusing. Assuming you've changed the value in field2, and after that, you change the value in field1. The new value in field1 will then also populate field2 again;-) This could be solved by adding an IF statement, perhaps like this:

IF isnull(me!txtField2) then
me!txtField2=me!txtField1
else
if msgbox("Do you wan't to overwrite?",vbYesNo,"Overwrite?&quot=vbok then
me!txtField2=me!txtField1
endif
endif

Play a litle with this, and see if you can make it tick;-)

HTH Roy-Vidar

 

[jbodford]

Thanks you so much for such a quick response, Roy. I will post again if I can (or can't) make what you said work.

Thanks again for the speed! :>)

 

[RoyVidar]

Hope it'l work, I'm logging of now. But there are numerous great gals and gays here.

Have fun, Roy-Vidar

 

[jbodford]

Roy,
I assume your last message was a typo, right? Regardless, I don't think I'll need any additional help from the gals or 'guys', as your solution worked. I actually created two events. 1) Since I had the first field automatically populating with Today's Date, the second field did not populate (because I really hadn't "UPDATED&quot. So I created another LostFocus event with your same code to cover that. It all works great. If you can recommend any materials (online or in print) that help beginners to learn how to write the VBA code, I'd look for it too.

Thanks again!

 

[RoyVidar]

Comming from a small country in the northern part of Europe, so insignificant that few people notices it, or even know of it's excistence, might of course give some linguistic anomalities, but I try to learn - and yes - a typo - I'm rather ashamed...

Great that it works!

When I started programming Access VBA, back in 97, I used a book by Allison Balter. Surely called developing Access 97 something.

I've now bought her "Mastering Access 2002 Desktop Development", SAMS, which I recommend. I find her angle to be "a good newbie guide in VBA", with careful and good explanations of what's needed. She also covers a most of Access general stuff, like tables, relationships, queries and reports, in addition to lots of good code/samples.

A lot of people who have done some Access VBA programming, likes to refer to Getz, Litwin and Gilbert - "Access Developer's Handbook", Sybex. This one covers almost all my needs, but can be a bit "to technical" in a learning phase (in my opinion).

Of online resources, this one is a goodie!

There's also, among others

http://www.access-programmers.co.uk

' forums

http://www.mvps.org/access

' faq's & excamples

http://www.helenfeddema.com

' tips, downloads etc

Roy-Vidar

 

[jbodford]

Roy,
Thanks again for all the resource suggestions. I will look for some of them. Maybe I can find one that is basic enough to learn from. And please don't worry about the typo. I was not offended.