Lookup value in related table for unbound textbox

[vapid2323]

I did search but I can’t seem to word what I want correctly for Google to give me what I am looking to do.

Ok I am sure this is really simple, I have a form and I want to display the address for a selected record, I have a text box that has the following in it.

="Site Name: " & [centerName] & "[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Site address: " & [mainAddrLine1] & " " & [mainAddrLine2] & " " & [mainAddrCity] & ", " & [StateProvince][/COLOR][/FONT]

I want to add in the State (StateProvince) but I can’t figure it out as the state is in a related table.

I know I can make a query that combines all this data into one table but I am sure there must be a better way as I don’t want to have un needed query’s everywhere.

 

[Mr. B]

From you post it appears that you already have the two address the City values and that the only value you are needing to acquire is the "StateProvince" value.

If this is the case take a look at the Dlookup() function.

You can declare a text type variable and the the Dlookup function to return the State value for the table where it is begin stored. Then you simpley concatenate the variable to the address and city value to produce your statement.

 

[vapid2323]

Great that gave me what I needed to get google to help out!

http://webcache.googleusercontent.c...d=1&hl=en&ct=clnk&gl=us&source=www.google.com

 

[Mr. B]

Glad it helped.

Good luck with your project.

 

[vapid2323]

My solution, if anyone ever needs it:

DLookUp("StateProvince","tblStateProvince","StateProvinceID =" & [fk_StateProvinceID])

Site address: " & [mainAddrLine1] & " " & [mainAddrLine2] & " " & [mainAddrCity] & ", " & DLookUp("StateProvince","tblStateProvince","StateProvinceID =" & [fk_StateProvinceID]) & " " & [mainAddrZip]