Tags: access, address, bases, codes, database, databases, delivery, dlookup, microsoft, mysql, office, oracle, sql, type
access 2003 Dlookup
I am using two databases. One has address information and the other has post office delivery codes. The two data bases are one to many type.
I am using the Form function and Dlookup to find the correct delivery code, but I can not save the data, or export it.
When I use the same dlookup code, in the query function, the program does not recognize the fields.
Is there a way to save the results I am obtaining from the Foms / Dlookup operation?
The code I use is as follows.
=DLookUp("[zip Plus 4]![Field20]","[zip Plus 4]","[FFXPD]![Street Name] = [zip Plus 4]![Field8] And [FFXPD]![Street Type]= [zip Plus 4]![Field9] And [FFXPD]![Street Number] between [zip Plus 4]![Field11] And [zip Plus 4]![Field12]")
The text field that this code is in is unbounded. But I am not sure how to bound it to a field that can capture the data.
I have tried some of the suggestions on the similar threads, I have been unsuccessful.
Thanks in advance.
By the way, I am not a programmer, so I will need a considerable hand-holding.
Leave a comment...
- 1 Comments
Ok, I assume that the lookup is based on the address entered in the address field. All you need to do is put your dlookup expression in the code behind your form in the Exit event of the address field. By having the code behind your form as shown below, it enables you to bind the zip textbox control on your form to the zip code field in your table. This is accomplished because you can reference the table field in the control source property of the textbox because the textbook is no longer bound to the Dlookup expression.
In the code below, I am assuming for illustrative purposes that the name of your address textbox on your form is txtAddress and the name of the zip textbox is txtZip.
Private Sub txtAddress_Exit()
Me!txtZip.Value = DLookUp("[zip Plus 4]![Field20]","[zip Plus 4]","[FFXPD]![Street Name] = [zip Plus 4]![Field8] And [FFXPD]![Street Type]= [zip Plus 4]![Field9] And [FFXPD]![Street Number] between [zip Plus 4]![Field11] And [zip Plus 4]![Field12]")
End Sub#1; Fri, 04 Jan 2008 22:57:00 GMT