PostCodes

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

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
 
Hi Rob,

You don't give any information about your data structure so it's not
possible to give a very useful answer. But presumably your table of
delivery rates is actually a table of postcode areas (YO30 etc.) and the
associated rates; and presumably you have already got the customer's
postcode from the customers table and put it in a textbox (which I'll
call txtPostCode) on the form.

In that case, you can use DLookup() to get the rate for a postcode area.
You'd use code somewhat like this, probably in the AfterUpdate() event
of the control you use to select the customer.

Dim strPostCodeArea As String
Dim varDeliveryCharge As Variant

varDeliveryCharge = Null
strPostCodeArea = Nz(Me.txtPostCode.Value, "")
If Len(strPostCodeArea) > 0 Then
'PostCode field isn't empty
If InStr(strPostCodeArea, " ") Then
'Trim off the in-code
strPostCodeArea = Left(strPostCodeArea, _
InStr(strPostCodeArea, " ") - 1)
End If
'Look up the postcode
varDeliveryCharge = DLookup("DeliveryCharge", _
"tblDeliveryCharges", _
"PostCode = """ & strPostCodeArea & """)
End If
'Set the value of the combo box
Me.cboDeliveryCharge.Value = varDeliveryCharge




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
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top