Using Sql in form field

[wsexton]

I'd like to run a sql statement after a user enters info in a form field. This is what I have so far:
Private Sub Reporting_Unit_AfterUpdate()
Dim sql As String
Dim Provider_Code As String
Dim Legal_Entity As String

sql = "SELECT dbo_PROVIDER_MASTER.CDS_PROVIDER_CODE as PROVIDER_CODE, dbo_PROVIDER_MASTER.LEGAL_ENTITY as LEGAL_ENTITY"
sql = sql & "FROM dbo_PROVIDER_MASTER"
sql = sql & "WHERE dbo_PROVIDER_MASTER.REPORTING_UNIT= " & Me.Reporting_Unit


CurrentDb.Execute sql
Me.Legal_Entity = Legal_Entity
Me.Provider_Number = Provider_Code

End Sub

Currentdb.Execute sql doesn't work. I'm not sure how to run the sql statement. I want the user to enter information in this field that will populate other fields on the form. Any help would be appreciated.

 

[dhookom]

Assuming Reporting_Unit is numeric, try replace all your code with:
Me.Provider_Number = DLookup("CDS_PROVIDER_CODE", "dbo_PROVIDER_MASTER", "REPORTING_UNIT= " & Me.Reporting_Unit)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]