Using SQL Query to fill in field in Form

[Dylan Snyder]

I want to set the "Fee" Field on a form to the penalty value in the Penalty table where Penalty = "Non-Compliant Docs" This is what I came up with but it keeps telling me there is an error in the From clause. This is my first time using SQL on Forms. Any glaring flaws. Would this even work?

Dim strSQL As String

strSQL = "SELECT Penalties.[Penalty Value]" & _
"FROM Penalties" & _
"WHERE (((Penalties.Penalty)='Non-Compliant Docs'));"

DoCmd.RunSQL strSQL
Me.Fee = strSQL

 

[pbaldy]

No, it wouldn't even work. Try a DLookup instead. Here's a handy reference on the syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[FoFa]

You can actually put the dlookup (prefixed with an equal sign) in the default value of the field on the form.
=DLOOKUP("Penalty Value","Penalties","'Penalty = 'Non-Compliant Docs'")

 

[Dylan Snyder]

Thanks,
That worked like a champ. Am I right in assuming that Dlookup can only lookup the value in a table and not preform a query? Even so, this is a cool tool to have. Thanks

 

[FoFa]

It can access a query (prewritten of course) as well as a table. You can not specify SQL in the dlookup function.