Autofilling controls on a form

[yuvraj.date]

Hi,
I am creating a form to return specific rates for an origin to destination location match. I have a table for all Zip codes that have their corresponding location names. Another table has an origin and destination zip code and its specific rate. Then there is query which calculates the rate and a form based of that query. Now, I could manage to return rate using a command button on the form that pulls the rate info when i enter the origin and destination zip codes in two text boxes. The rate is displayed in a list box. what I want now is the location for that origin/destination zip code to be autofilled on the same form. How do I achieve this? I am relatively new to access programming and cannot perform complicated macros. Please help.


Thanks

[Missinglinq]

I'm having a hard time following your exact scenario here; sorry!

Are you physically (i.e. thru the keyboard or copying and pasting it in) entering the origin and destination zip codes in the same Form where you want the the origin and destination locations to be filled in?

If so, and the ZipField is defined as a Text Datatype:

Private Sub txtDestinationZIP_AfterUpdate()
 Me.DestinationLocation = DLookup("LocationField", "YourZipLocationTable", "[ZipField] = '" & Me.txtOriginZip & "'")
End Sub

Private Sub txtOriginZIP_AfterUpdate()
 Me.OriginLocation = DLookup("LocationField", "YourZipLocationTable", "[ZipField] = '" & Me.txtOriginZip & "'")
End Sub

If ZipField is defined as a Number Datatype

Private Sub txtDestinationZIP_AfterUpdate()
 Me.DestinationLocation = DLookup("LocationField", "YourZipLocationTable", "[ZipField] = " & Me.txtOriginZip)
End Sub

Private Sub txtOriginZIP_AfterUpdate()
 Me.OriginLocation = DLookup("LocationField", "YourZipLocationTable", "[ZipField] = " & Me.txtOriginZip)
End Sub

You'll have to replace Field, Control and Table names in the code with your actual names, of course.

BTW, you'll seldom get Macros from anyone here! We're a VBA sort of crowd!

Linq ;0)>

[yuvraj.date]

Yes, I am filling in the origin and destination zip codes physically thru a keyboard. I will check if the code you gave works and let you know. Thanks anyways for the start!

[yuvraj.date]

Hi,

Thanks for the start, however, I don't understand why I see only the first location from the zip table for origin and destination zip code entries in the form. Is it because the datatype for zipcode in my zip table is text and the location field datatype is text too. But, the datatype for origin zipcode in my rate table is number and so is the destination zipcode along with the rate. Do i have to have similar datatypes for all these fields?

Table: Zip - Fields (Zipcode = text, Location = text)
Table: Rate - Fields (Origin = number, Destination = number, Rate = number)
Query: Get Rate (based of the rate table, returns rate)
Form: Rate

Please guide. Thanks.

[yuvraj.date]

I have attached the sample file for reference in case you need more details. Thanks for all the help. Appreciate it.

[Missinglinq]

Access is tolerant, at times, when it comes to recognizing/using a Field as a Number when it is defined as Text, if it looks like a Number, but this tolerance is not consistent, so you really need, I think, to have all of your 'zip' Fields defined in the same way. Personally, I define any Field that is composed entirely of Digits, but never used for mathematical operations, as Text. And since you appear to want to display your Zips with leading Zeros, if appropriate, it would simplify things to do so. But the main thing is that their Datatypes match.

Your problem, though, is that you're comparing apples and oranges in the WHERE clauses of the DLookups, and the Zip code in the Zip Table, the one you're using with the DLookups, is defined as Text, but you used the code I gave you for a Numeric Zip code.

Here's the correct code, for both DLookups, with the Field named [Zip code] defined as a Text Datatype:

Private Sub ozip_AfterUpdate()
 Me.List28.Requery
 Me.OriginLocation = DLookup("Location", "Zip", "[Zip code]= '" & Me.ozip & "'")
End Sub

Private Sub dzip_AfterUpdate()
 Me.List28.Requery
 Me.DestinationLocation = DLookup("Location", "Zip", "[Zip code]= '" & Me.dzip & "'")
End Sub

I would also change the names in the Rate Table, for the Zipcodes, from your current Origin and Destination, to something taht lets you know it's a Zip, like OriginZip and DestinationZip. I suspect that this was how you ended up comparing apples and oranges in the first place!

Linq ;0)>

[yuvraj.date]

Hi,

Thanks a ton! I realized where I was messing up and now I can see the locations as well as the rates! This is the beginning though and I would need help for other features in near future.

Really appreciate your help!

Thanks again.

[Missinglinq]

Glad we could help!

Good luck on your project!

Linq ;0)>

[yuvraj.date]

Hi there,

How do I add another field in my existing Zip table which gives me the city associated with that zipcode and populate that in the form? Do I need to add that field name in the dlookup used for origin zip and destination zip fields of which the region is currently populated? If yes, how do I insert that field in that dlookup?

[Missinglinq]

If I understand you correctly, after modifying your Table by adding the Field, you'll need to run a separate DLookup, simply replacing Location with the name of the 'city' Field.

Private Sub dzip_AfterUpdate()
 
 Me.List28.Requery
 
 Me.DestinationLocation = DLookup("Location", "Zip", "[Zip code]= '" & Me.dzip & "'")
 Me.DestinationCity = DLookup("CityFieldName", "Zip", "[Zip code]= '" & Me.dzip & "'")

End Sub

You'll need to replace DestinationCity with the name of your Textbox on the Form, and CityFieldName with the name of the city Field, in the Table. Then do the same for the origin city.

Linq ;0)>

[yuvraj.date]

Awsome! I actually put the code in the on focus event of the city filed and it worked! I had not seen your post till then...It worked and now I have changed it to the afterupdate event of the ozip and dzip fields and it works too!

Thanks so much!

[Missinglinq]

Congratulations! That's called learning!

When doing this kind of thing you really need to tie it to the Field(s) that are part of the expression being used, such as the dzip_AfterUpdate event. In this manner, you know that the dzip data is available for use in the DLookup function.

Also, you can seldom rely on the end user actually moving to a given Control. With the code in the OnFocus event of the city Field, it would never be populated unless the user actually tabbed/clicked into the Textbox!

Linq ;0)>

[yuvraj.date]

Absolutely! Thanks for everything!

Now another hiccup; I had a rate table where there were origin zip (3 digit) and dest zip (3 digit) and a specific rate associated with that combination.

But now I have a 5 digit zipcode in my zip table with the region and city associated with it in that table. My query uses a 3 digit zip search from the rate table. Since I have changed the 3digit zip to 5 digit zip in zip table I cannot return a rate in the form. I tried truncating the origin and dest zip that user inputs (now as a 5digit field) to return the rate, but somehow it leaves the filed blank. There is no error but somewhere the link is missing!

My query

SELECT Rate.Rate
FROM Rate
WHERE (((Left([Rate]![Origin],3))=[Forms]![Rate]![ozip]) AND ((Left([Rate]![destination],3))=[Forms]![Rate]![dzip]));

Thanks!

[yuvraj.date]

Hey, I figured that out!!!