CODEWITHSUNDEEP
vbams-accesstextboxms-access-2013
Miguel Mas
Miguel Mas

Reputation: 545

Access 2013: showing query result in a Text box

I have a subform in Access 2013 with the combo boxes CountryCode and CityCode.

I'm adding another field to the subform, a text box where to show the city name that correspond to the country code and the city code entered.

The table Location contains this columns: CountryCode | CityCode | CityName

The SQL query should be: 

SELECT CityName FROM Location WHERE CountryCode='USA' AND CityCode='NY'

'USA' and 'NY' have been chosen by the user in the combo boxes and the resulting CityName should go in the CityName textbox

So far I have done:

In the subform, in the CityName text box Control Source, I have entered:

=[GetCityName()]

Then in a vba module I have done: 

Public Function GetCityName()
x = DLookup("[CityName]", "dbo_Location", "[CountryCode] = " & CountryCode & " And [CityCode] = " & CityCode & "")
End Function

And this pops up the Enter Macro Name dialog when I run it.

Ok, it may be that I'm not getting the codes from the combo boxes so I change the x to: 

x = DLookup("[CityName]", "dbo_Location", "[CountryCode] = " & Me.CountryCode & " And [CityCode] = " & Me.CityCode & "")

And now I get the error: "The usage of the word Me is not valid"

I'm noticing that the subform is not listed under Microsoft Access Class Objects in vba. Can that be a problem?

Don't know what else to do, as you can see I'm quite new to access and vba. Please help!

Upvotes: 0

Views: 378

Answers (1)

BData
BData

Reputation: 189

try this :

x = DLookup("[CityName]", "dbo_Location", "[CountryCode] = '" & Me.CountryCode.Value & "' And [CityCode] = '" & Me.CityCode.Value & "'")

Upvotes: 0

Related Questions