Overwriting subform address

[JoeBeach]

Hi.

I am in transportation and am designing a form for bills of lading (air/sea/truck). There are two tables, a NameAddress list, and the BillOfLading record list. The bill of lading form has a drop down combobox to let the user choose a Ship From name and address, and another for a Ship To name and address.

The simplest way I can think to display, and later report-output the names and addresses is with subforms for Ship From, and Ship To, or alternately, using DLookup fields. Both methods seem to work fine.

But in this business, the user, a shipping clerk or agent, is often called on edit a name or address text in the Ship To or Ship From, usually for a one time occurance for the specific shipment, rather than for a change for the Name or Address list. That change might be a request for adding a purchase order number, or a pickup number, to an address field. This change might occur rarely or regularly, but often it must be placed in the address lines rather than elsewhere on the bill of lading.

With subforms, I seem to have been able to set it up where you can edit the address field, however, when doing this, it has always meant that the related record in the NameAddress table gets changed by this editing.

So the question is, would it be better to create real name and address fields in the bill of lading table, and somehow have the related NameAddress field data written to those fields? Then, if required they could be edited for the specific shipment and that that bill of lading data record would be saved. If so, how can I write to those fields from the related record in the NameAddress table?

Thanks for any help or suggestions.

(PS: I know that doubling up on name and address fields breaks a rule in DB design but I don't see how to avoid it in this case.)

 

[ThreeCrow]

Joe

a shipping clerk or agent, is often called on edit a name or address text in the Ship To or Ship From, usually for a one time occurance for the specific shipment, rather than for a change for the Name or Address list. That change might be a request for adding a purchase order number, or a pickup number, to an address field. This change might occur rarely or regularly, but often it must be placed in the address lines rather than elsewhere on the bill of lading.

Add a field to your BillOfLading table name it something like "Document". And add the same to your Form and Report.

Am I understanding correctly that you may also have to change the Name or Address as well.

 

[JoeBeach]

The change in Name or Address would be for the bill of lading record only. The user wouldn't be concerned with making a permanent change to the NameAddress table record in this application.

Here the change would have to be made on, say, AddrLine1 field(first address line), or, city field, and not on an additional, separate field on the form. Example: The Bill Smith Company had as first address line "123 Oak Avenue, Ste 456" in the NameAddr table displayed in the subform or Dlookup field on the B/L form. The user needs to edit that to "123 Oak Ave, Suite 456", but just of this bill of lading, which can't be done using DLookup and if done using a subform alters the original NameAddress record, which is not desired.

I've tried to work around this by having a button offer the option of inputting alternate name and address fields on a popup form, then on print or other report output the alternate field data gets used if their fields aren't null. But that's proved awkward in that you still have to have the separate fields for the b/l, but the user isn't seeing this alternate addressing unless they open the popup alternate set.

So I thought, if I need the full set of names and address fields in the bill of lading anyway, why not have some command "write" the related NameAddress field data to the name and address fields in the bill of lading table and form, which of course could be edited on the b/l. In other words is there a "write" or similar command or procedure in VB or SQL to make this happen?

I'm not sure what the two document fields you mention are for.

Thanks.

 

[ThreeCrow]

Joe

Apologize for taking so long to get back; wife said I had to go to work.

The “Document” field that I had mentioned was to store the “Purchase Order Number” or “Pickup Number” that you may need to add to the BillOfLading. You’ll need to have this on the Form for entry and on your report (printed BillOfLading).

As for changing “AddrLine1”, unless you have the change in a table DLookup will not work as you have found out.

So my idea is that you create another Table just for your Addresses.
A simple example would be

Table Name: Company Addresses
CompanyPK(AutoNumber)
CompanyID(FK)
AddrType (Choice of “Main” or “BillTo” or “ShipTo”)
AddrLine1
AddrLine2
City
State
Zip/PostalCode

Relating this table to your Company table via the CompanyID

And on your BillOfLading Entry Form you will have another ComboBox to select the Address to use. These two ComboBoxes are cascading, will be link in the underlying query.

You can do the same for the ShipTo Combobox.

This is a relativity simple method, but should work for your needs.

The theory behind it is that a Company can have more than one mailing address, more than one Billing address, and also more than one Shipping addresses.

Hope this helps, post back if you need morehelp

 

[JoeBeach]

Thanks for the alternate idea. (I too got sucked into the diversion of work, for a few days.)

This solution seems to use alternate addresses saved for possible reuse in a third table. Unfortunately my original question is a classic case of thinking that giving an overly simple explanation for a need will be best. Now, here is the longer version.

These BillsOfLading are international documents, and are sometimes used for payments on bank letters of credit. A seller in one country will only allow a buyer to pay by cash in advance or,to protect both parties, by letter of credit. The buyer goes to their local bank, takes out a letter of credit, and that bank writes it up with certain stipulations, among them that the seller must present proof of shipment with a bill of lading showing certain things such as a specific port of arrival, what shipped, shipped from and shipped to.

That ship from and ship to is normally the NameAddress table info. But each letter of credit is unique, with banks notoriously writing out the name of say, the ship-from, differently each time, ever so slightly. Letter of credit document rules require that the documents, including the bill of lading, be word and letter perfect matches to the letter of credit in order for them to be honored for payment. This includes abreviations and even mispellings by the bank.

So, my "123 Oak Avenue, Ste 456" changing to "123 Oak Ave, Suite 456" affects payment for goods. My mention of having to add a PO number or similar to the bill of lading is also part of this; that PO often must be added to AddressLine1, or maybe AddressLine2, rather than at a separate location and field. That can be in Ship-From or Ship-To addresses.

If the ship-from company gets paid alot by letter of credit, that address will change often, again ever so slightly, likely never to be used again, so there is no need to store this except in the BillOfLading record itself. Indeed storing this in the NameAddress or a 3rd table will cause confusion when the user goes to choose from a list. But, there is still the need for a set name and address over in the NameAddress table, because the shipper doesn't always get paid by L/C, and the user clerk doesn't need to be retyping names and addresses when it isn't needed.

In the early 90's I helped design something to this affect with a couple of programers who went to the "write" solution, but I can't remember the program language they were using then, except it was on some Dbase dirivative platform. In Access, I was looking for the writable overwrite solution again, but not being a programer I'm stumped.

With Thanksgiving coming maybe the turkey will ignite some sparks of ingenuity.

Thanks again.