Automatically Fill In Fields

[Chris Bellingha]

I have quite a complex database that seems to work fine apart from one small snag. I have a table which holds the following information:

REP2: Postcode, Rep Name, Region

and a table (for customers) which has a field in which to enter the postcode. I wish the database to automatically assign a rep and region based on the postcode I enter for a customer. The postcodes I hold in REP2 are simply the first 4 characters of a british postcode (before the space).

So what I want to happen is for the database to take the postcode from the customers table, trim it down to the first 4 characters, compare this to REP2, then fill in the two fields on the customers form for rep name and region.

Currently I can get the fields automatically updating but unfortunately they all update with information from the first record of REP2.

This is my code:

Private Sub Postcode_AfterUpdate()
Dim varsalesregion1, varsalesrep1 As Variant
varsalesregion1 = DLookup("[region]", "REP2", "left([postcode],4)=[REP2]![postcode]")
varsalesrep1 = DLookup("rep name", "REP2", left([postcode],4)=[REP2]![postcode]")
Sales_Region1.Value = varsalesregion1
Sales_Rep1.Value = varsalesrep1
End Sub

 

[RuralGuy]

Just Join the REP2 table to the RecordSource you are using for the form. When you fill in the Postcode the other two fields will just show up correctly.

 

[Chris Bellingha]

Thanks for your idea, but I'm afraid this doesnt work - it made my 451 records turn into 600000+ and then said that my recordset was not updateable. Any more ideas?

Thanks

Chris

 

[ColinEssex]

Some postcodes only have 3 or even 2 characters before the space.

Col

 

[Chris Bellingha]

Yeah I know, but in Excel there is no problem with doing this procedure. I just wonder why I can't get it to work in Access?

 

[RuralGuy]

Did you use a left Join?

 

[Chris Bellingha]

I wasnt exactly aware of how to join tables properly I dont think - having done some research it seems i created a horrible cross join. I still don't know how to set up the join correctly though, can you help?

Thanks