Autofill text fields based on checkbox selection

Question

Hi everyone!

I am okay(ish) with Access, but still consider myself a newbie to the program and to databasing in general.  That being said, there may be a very simple answer to the question I have below, but if there is I can't find it.

I am entering data collected from businesses on paper forms into a database in Access 2007.  While most of these businesses have their mailing address the same as their physical address, there are a few that have a different mailing address so we have a separate entry for that.  To make things easier for myself, I have included a checkbox in the Access form "Mailing address same as business address?" that I can check to avoid typing the data twice; however I can't figure out to autopopulate the mailing address fields with the physical address when that box is checked, so for now I've been leaving them blank.  I'd really like to have it autopopulate so that if we need to we can pull just the mailing addresses for mass mailings, etc.  I have set the database up using a single table and no subforms (yes I know we could do this in Excel but we weren't able to search and group the data the way we needed in Excel).

I have absolutely NO experience with VB/scripting so if that's the trick, I'll need it dumbed down!  :)

Here are my fields (there are two "direction" fields for each as some of our streets are "west such-and-such boulevard" and some are "blank street west"):

for physical address:

add_number

add_dir1

add_street

add_dir2

add_unit

add_city

add_state

add_zip

for mailing address:

mail_number

mail_dir1

mail_street

mail_dir2

mail_unit

mail_city

mail_state

mail_zip

Thanks very much in advance!!

Answer 1

It sounds like your procedure is not linked to the AfterUpdate event. Look at the property sheet for the checkbox, on the Events tab, and next to "After Update" it should say [Event Procedure].

If it does not, type "[" in the box ( [Event Procedure] should auto-fill) then click the button with 3 dots.  This will display an empty procedure stub into which you can paste the code.

Best wishes,

Graham

Answer 2

I assumed (perhaps incorrectly) that you have a Form with a Checkbox control named mailing_same, bound to the mailing_same field in your table; and that you also had textboxes already filled in for add_number, add_dir1 and so on. What should have happened is that you checked the mailing_same checkbox and moved off it to some other control.

If that's not what you have or what you did, please explain... for example, if I misunderstood and you have filled in all the mail_ fields and want them to populate the add_ fields, you would need to swap all the controls in the code.

Answer 3

Well, I'd generally agree with Karl and Graham that it may be best not to store this information redundantly, but I can see your logic. For one thing storing a second copy makes it easier to edit the "add" address information if that should be necessary.

What you could do is put the following VBA code in the AfterUpdate event of the checkbox. View the form's properties, select the checkbox, find the AfterUpdate event on the Events tab of the properties, click the ... icon by it and choose Code Builder. Access will give you the Sub and End Sub lines automatically; you would just edit in the lines between, like

Private Sub mailing_same_AfterUpdate()

If Me!mailing_same = True Then ' fill in the mailing address if they check it

   Me!mail_number = Me!add_number

   Me!mail_dir1 = Me!add_dir1

   <etc through all the controls>

End If

End Sub

Answer 4

It is a waste to store the same data twice and can results in errors.    In query where you extract the data use IIF statements like this --

Mail_Add_Num:  IIF([YourTable].[SamePhysical] = -1,  [YourTable].[add_number], [YourTable].[mail_number])

Answer 5

You could consider storing only one copy of the data (the business address) if the two are the same.

In the query which generates your mailing list, use expressions like this:

addr_number: IIf( [same_mail_address], [add_number], [mail_number] )

where [same_mail_address] is the boolean (yes/no) field bound to your checkbox.

On your form, you can show or hide the [mail_*] fields depending on the value of that field.

Best wishes,

Graham