Replicating Data in Table

[V3LV3T]

I'm updating the database for the company that I work for and I'm currently working on the customer list (approximately 600-700 records), and I have a few different fields: BillToAddress, ShipToAddress, BillToCity, ShipToCity. Now, I'd say the majority of these clients use the same address for their BillTo and ShipTo, but some do not, i.e. using a P.O. Box for their BillTo. What I'd like to be able to do, just to save me time is have it so that when I enter in the BillTo address, that data automatically carries over to the ShipTo address when I tab over, so that if it's different, I can type over it, but if it's not, I can tab past it.

Is this possible? I've tried looking up stuff on duplicate fields, etc, but I only find info about getting rid of duplicate fields/records (definitely not what I'm looking for).

If anyone has any ideas as to if Access can even do this, please, please, please let me know! Thx!

 

[koresnordic]

Try: In the bill to fields, change their "on exit" property to set the relevent ship to property to equal the bill to field. e.g. if one of the bill to fields is [bill addr1] and the equivelent ship to field is [ship addr1], then make the "on exit" event function "[ship addr1]=[bill addr1]". By the way, when you select this event, the type selected should be "expression builder". Hope this helps.



Graham

 

[V3LV3T]

Well, Graham, as good as that sounds, I don't know much at all about scripting like that, so I have no idea what the syntax would be. I tried taking a wild guess at the syntax and it didn't work for me. So if you or anyone knows that the exact syntax would be, I'd appreciate it.

In the meantime, here's what I'm trying now. Obviously some sort of event has to happen in order for it to run the command to basically copy the data from one field to another, right? Right.

The whole reason I'm updating their database is so that their Sales Rep can enter in new information, such as adding a new customer, so all he's going to see is the forms, so I went ahead and built the form that he'll be using and added a command button. So now, instead of an 'OnExit', we're looking for an 'OnClick,' correct?

Well, yet again, I don't know the syntax. So I thought about trying a macro. I went in and created a new macro and did a 'SetValue' action and put in the following using the Expression Builder. I have no idea if I'm even close to getting what I want yet. Have a look:

[tblCustomers]![ShipToAddress] = [tblCustomers]![BillToAddress]

Seems simple enough.

Then, under the form design, I set the On Click to that macro.

Still doesn't work. So I don't know what else to try. As much as it's not that big of a deal considering I can just copy & paste, now I want to figure it out just because now I'm curious, and determined.

Hehehe

Any help would be appreciated.

 

[koresnordic]

Ok, you have created your form showing both the bill to address and the ship to address. In edit mode select the properties of the [shiptoaddress] field and click on the event tab. Now click on the "on exit" option. To the left of the box, click on the three dots and select event procedure (sorry - mentioned the wrong above - late nights, long days etc again sorry about that). Now enter the following:"[shiptoaddress] = [billtoaddress]" (without the quotes), between the two lines that are already there. Then close this area. Now when you enter the ship to address and move to the next field, the bill to address will fill with whatever is in the ship to address. Caution.. This will overwrite anything that already exists in the ship to address.




Graham

 

[V3LV3T]

Woot! Thanks Graham! It worked. Who knew it would be that simple (not me, obviously).

Thanks so much for your help.. And for your patience.

 

[koresnordic]

No problem, sometimes we try to find the more complex solutions when the simplest will do.



Graham

 

[willir]

V3LV3T

Also consider a design change. I like to use an address table.

In the customer table, I would have
customer_id
...
invoice_add_id
shipto_id

Then for the order master table
order_id
...
shipto_id

This allows an order to be shipped elsewhere than the default for the customer.

Instead of duplicating all the address fields, only the refernce number needs to be changed. With 600+ customers, I suspect you get a few orders so this will save time (no typing, just select the address), and save space.

Richard