Dlookup

[Guest]

I am trying to populate an Access 2003 form field based on the input from
another field on the form. Basically I want to draw the location sector of
town based on the zipcode. I have a table:

tblLocationByZip
-------------------
intzip
txtlocation

and I want the form to populate the location field on the customer table
from the location control using dlookup after typing the zip code into the
zip control.

tblCustomer
----------------
.....addr
txtcity
txtstate
intzip
txtlocation ....

I am using afterUpdate_zip
Me![txtlocation] = DLookup("[txtlocation]", "tblLocationByZip", "[intzip]="
& Me![intzip])

I seem to be always returning the 1st record of the tblLocationByZip
regardless what I put in the zip field.
I am sure I am missing something silly but can't seem to get it right.
Perhaps dlookup is not the proper function to use.

 

[Jeff Boyce]

Brian

I might be able to save you some work ...

Don't do that! If you already have a table with the txtlocation associated
with intzip, then you DON'T NEED to add txtlocation to any other tables.
Just keep the zipcode and use a query to "see" the txtlocation.

By the way, if your naming convention means that you are treating zip codes
as integers, you are in for a rude surprise. Zip codes on the East Coast of
the US start with 0's. Leading 0's are dropped when you save an integer
value. Anyway, you'd only need to be saving zip codes as integers (i.e.,
numbers) if you intended to do math (add, subtract, ...) on them. If you
don't need to add zip codes, store them as text.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for the tip about zip codes - I really didn't think about it because
we deal with just local zips - but why tempt fate - I can easily change it to
text.

Now to the real problem - how would I set up the query to return the
location by entering in the zip code. I am not sure how to set up the
parameter query to take input from a form field.

Brian

I might be able to save you some work ...

Don't do that! If you already have a table with the txtlocation associated
with intzip, then you DON'T NEED to add txtlocation to any other tables.
Just keep the zipcode and use a query to "see" the txtlocation.

By the way, if your naming convention means that you are treating zip codes
as integers, you are in for a rude surprise. Zip codes on the East Coast of
the US start with 0's. Leading 0's are dropped when you save an integer
value. Anyway, you'd only need to be saving zip codes as integers (i.e.,
numbers) if you intended to do math (add, subtract, ...) on them. If you
don't need to add zip codes, store them as text.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to populate an Access 2003 form field based on the input from
another field on the form. Basically I want to draw the location sector
of
town based on the zipcode. I have a table:

tblLocationByZip
-------------------
intzip
txtlocation

and I want the form to populate the location field on the customer table
from the location control using dlookup after typing the zip code into the
zip control.

tblCustomer
----------------
....addr
txtcity
txtstate
intzip
txtlocation ....

I am using afterUpdate_zip
Me![txtlocation] = DLookup("[txtlocation]", "tblLocationByZip",
"[intzip]="
& Me![intzip])

I seem to be always returning the 1st record of the tblLocationByZip
regardless what I put in the zip field.
I am sure I am missing something silly but can't seem to get it right.
Perhaps dlookup is not the proper function to use.

 

[John Vinson]

Now to the real problem - how would I set up the query to return the
location by entering in the zip code. I am not sure how to set up the
parameter query to take input from a form field.

Use a criterion

=[Forms]![NameOfYourForm]![NameOfAControl]

John W. Vinson[MVP]

 

[Guest]

Ok, I guess I am being thick here but once I get the query set up, how do I
get the results to post to the location field in the form ie when I enter the
zip code into the zip form field it populates the location field with the
proper location sector.

Now to the real problem - how would I set up the query to return the
location by entering in the zip code. I am not sure how to set up the
parameter query to take input from a form field.

Use a criterion

=[Forms]![NameOfYourForm]![NameOfAControl]

John W. Vinson[MVP]

 

[John Vinson]

Ok, I guess I am being thick here but once I get the query set up, how do I
get the results to post to the location field in the form ie when I enter the
zip code into the zip form field it populates the location field with the
proper location sector.

Reread Jeff's message.

You quite possibly do not NEED to - in fact should not - store the
Location field redundantly.

If you have a real need to do so, you can use a little VBA code in the
Zip code combo's AfterUpdate event to "push" the location field into
the bound control:

Private Sub cboZip_AfterUpdate()
Me.txtLocation = Me.cboZip.Column(2)
End Sub

This will copy the value in the *third* (it's zero based) field in the
combo box cboZip's RowSource query into the textbox txtLocation.

John W. Vinson[MVP]

 

[Guest]

Ooooohhhhhhh,

Talk about being blinded by tunnel vision !

I get it now, Duh !! Thanks, this works much better

Brian