Cbo association to part of a field

[Rob]

RePosted from 16th aug 03

Hi All,

I have a database with delivery rates stored in a table. The people taking
the orders have to know the charges for different areas going on the
customers postcode.

I wish to streamline the order taking process by making the orders form
combo default to the correct delivery charge based on a postcode field in
the customers table.

The way the postcodes are banded are

YO30
YO29
YO28
etc .... etc ....

How do I make the association between the postcode and the combo??

Many thanks

Rob

 

[John Vinson]

I wish to streamline the order taking process by making the orders form
combo default to the correct delivery charge based on a postcode field in
the customers table.

The way the postcodes are banded are

YO30
YO29
YO28
etc .... etc ....

How do I make the association between the postcode and the combo??

If the "banding" is the first four bytes of the postcode (you don't
say even what country you're in; I don't recognize these as either US,
Canadian or British postcodes) you can use the AfterUpdate event of
the postcode field to populate the delivery charge. I'm assuming you
have a table with fields PostCodeBand (4 bytes) and Charge:

Private Sub txtPostCode_AfterUpdate()
Me!cboCharge = DLookUp("[Charge]", "[BandCharges]", _
"[PostCodeBand] = '" & Left([Postcode], 4) & "'")
End Sub

You'll want to put in some checks for valid values, post codes not
found, etc.