nathan2314
01-13-2009, 11:17 AM
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.
Private Sub trigraph_AfterUpdate()
Dim trigraphcode As String
trigraphcode = Me.trigraph
DoCmd.OpenQuery "qrytrigraph", acViewNormal, acEdit
Me.Location = qrytrigraph.country
End Sub
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!!:)
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.
Private Sub trigraph_AfterUpdate()
Dim trigraphcode As String
trigraphcode = Me.trigraph
DoCmd.OpenQuery "qrytrigraph", acViewNormal, acEdit
Me.Location = qrytrigraph.country
End Sub
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!!:)