How to I tell Access to do this?

[realtorDC]

Hello All,

If I have a database named "clients.db" with a table named "fsbo" with three special feilds:

Address (text)
Absentee (yes/no)
MailAddress (text)

I want MailAddress to be populated with Address when Absentee is no.

Whatever the code is, where do I write it to make the function happen? Does Access have a console? I am very new to Access, but have some programming experience, can anyone help me on this?

 

[Uncle Gizmo]

>>>Does Access have a console?<<<

Not sure what you mean? Could you provide more info....

 

[Uncle Gizmo]

>>>I want MailAddress to be populated with Address when Absentee is no.<<<

Why?

Please explain what you are trying to achieve....

 

[realtorDC]

>>>I want MailAddress to be populated with Address when Absentee is no.<<<

Why?

Please explain what you are trying to achieve....

I am mailing property owners who need to sell their homes, not all of whom live at the property they need to sell. If they have another mailing address than the property address I want to mail them my information to the mailing address (the one I will use when I mail merge my envelopes), but in describing the property I want to sell for them I want to use that one when I mail merge my letter. In the case they are not absent from their property, it mails to the address of the property.

Example:

Suzie Q owns 123 Any St. and 456 Any St. and wants to sell 456 Any St. because she does not feel like renting it anymore.

Envelope:

Suzie Q
123 Any St.
New York, NY 111222


Letter:

Dear Suzie Q,

I noticed that you need to sell your property at 456 Any St. and want to help you with this...

 

[ChipperT]

Hello All,

If I have a database named "clients.db" with a table named "fsbo" with three special feilds:

Address (text)
Absentee (yes/no)
MailAddress (text)

I want MailAddress to be populated with Address when Absentee is no.

Whatever the code is, where do I write it to make the function happen? Does Access have a console? I am very new to Access, but have some programming experience, can anyone help me on this?

Not a good design to have a table with two fields with essentially the same data. I would eliminate the MailAddress field altogether, store the Address and then use the Absentee field as a "filter" in a query, such as "Select * from Address where Absentee = 'no'"

 

[NigelShaw]

Hi

I disagree with the removal of the field. I understand it is bad practice to have the same data but removing the field isn't the solution. What if a billing address was required yet a posting address was different?

In regard to console, I think it is meant by the vb window? Then yes a console is available. F11 I think

I would Have a filter that checks for a posting address. If not present, use the billing address. I wouldn't copy the address to the second field as that is bad practice.

NS

 

[Rabbie]

My take on this would be to have a Property Table with a link to a Vendor table. The Vendor table would contain the mailing address of the Vendor. Since a Vendor could be selling more than 1 property and a property could be owned by more than 1 vendor you would need a junction table to model the Many-to-Many relationship.