Populate a Text box with a value

[Ice Rhino]

I have many text and combo boxes on my form. But for the purposes of this question I am only referring to three.

Text1 = Asset_Number
Text2 = Site_Ref
Text3 = Location

Text 2 and Text 3 contain relevant information in a table called 'tbl_locations'

OK, based on an After_Update event of Text1, a bit of code pulls Left (Me.Asset_Number, 2) as a string and places the result in Text2.

What I want to happen after it has placed the value in Text2 is that the two figure ref will be looked up in the tbl_locations table and then whatever the Location is that has that two digit code will be placed in Text3.

Anybody got any little gems of info that can help in this situation?

 

[pono1]

Ice Rhino,

If each record in the location table has a location and site_ref field, then this might be the answer. Experiment with the following...

Dim GetLocation as String

'NOTE: assuming that site_ref is a string...
GetLocation = DLookup("[LocationInTblLocations]", _
"Tbl_Locations","[SiteRefFieldinTbl] = '" _
& Forms![YourFormName]![Site_Ref] & "'")

' last 4 characters of last line above: " ' " )
'end of 2nd line above: apostrophe, quote, underscore
' mildly annoying, the punctuation marks in Dlookups

Me.Location = GetLocation

'Add'l remarks:
' [LocationInTblLocations] -- field name in a rec from which you want to retrieve info

' Tbl_Locations -- table that holds all recs

' [SiteRefFieldinTbl] = criteria field in same rec that you're looking for

Regards,
Tim

 

[Ice Rhino]

Off to go at that now.

Many Thanks