Solved: Getting a query to update a field in a form

[nathan2314]

Hello everyone!
How can I get a query to update a field in my form. I have a field created called 'Country' in a form. Another field in the form is called Trigraph. So what I'd like to do is have a person enter in the value of Trigraph (example is 'USA') and then the 'Country' field would automatically populate to 'United States'. I already have a table called 'Countries' that have all the country vs trigraph combinations.
So I build a query based on the table 'Countries' that will need the criteria field of the 'trigraph' column to be the value in the 'Trigraph' field on the form. The query work perfect if I manually put it in the query and outputs the correct matching country but I need it to automatically do it when a user enters the value in the form.
So I tried to write a macro in the 'After Update' event in the properties of the 'Trigraph' field on the form. Here is my code.
[VBA]Private Sub trigraph_AfterUpdate()
Dim trigraphcode As String
trigraphcode = Me.trigraph
DoCmd.OpenQuery "qrytrigraph", acViewNormal, acEdit
Me.Location = qrytrigraph.country
End Sub
[/VBA]
I was trying to set the string trigraphcode equal to the value the user entered in on the form and then pass that to the query (I created a parameter in the query itself so that each time it runs in prompts the user to enter a value called "trigraphcode" which then is put into the criteria field) but this approach is not working. When I run the above code, it DOES prompt me for a value "trigraphcode" (which I was hoping it would just pass the string I created into the query instead of prompting me for a value) and then it gets stuck on the last line of the code also.
Whats the best way of doing this kind of thing??
Appreciate any help!!

[CreganTur]

I would suggest scrapping the SQL approach and just use a DLookup function. Search for it in Access help for some great information.

[nathan2314]

Ok thanks for the suggestion.
I tried it and it kinda makes sense but I cant get it to work.
It either just gives the first entry in the list or gives me a "you cancelled the previous operation" error. I get this error with the following code
[VBA]Private Sub trigraph_AfterUpdate()
Me.Location.Value = DLookup("[country]", "tblcountries", "[trigraph] =" & Me.trigraph)
End Sub[/VBA]
I tried several different versions of this but still not getting it to output the correct answer. I did get it to work w/o errors but it just outputed the first country entry in the 'Countries' table ??

[CreganTur]

It's due to a syntax error. Try this:

[VBA]Me.Location.Value = DLookup("[country]", "tblcountries", "[trigraph] ='" & Me.trigraph & "'")
[/VBA]

The reason for this is that your trigraph field is formatted as text. Therefore you have to wrap "Me.trigraph" with the SQL symbols that declare it as a string. It's exactly like using VBA variables in a normal SQL string.

HTH

[nathan2314]

OHHH ya .. Now it works.
Thats great!! THanks!
Much better than my original method!

[CreganTur]

OHHH ya .. Now it works.
Thats great!! THanks!
Much better than my original method!

Glad I could help!

If your issue is resolved, please mark it as solved using Thread Tools at the top of this thread.

[nathan2314]

Oh ya..i keep forgetting to do that