CurrentDb.Execute Question

[d9pierce1]

Hi all,
I want to try something new to me that June7 informed me on a bit. Never using it i want this to run an update qry and not real sure on how to put it into code?

Private Sub TxtDomainName_AfterUpdate()    
' If form is a new record, then no update necessary as no email addresses exist!


    If (Not Form.NewRecord) Then
    ' Run UpDate Qry to update 
        CurrentDb.Execute " CompanyDomainUpDateQry"
    End If
End Sub

Would it go as I have it above or do I need something else for it to run. Since its an update qry i dont want to practice with it, lol....
Also, would i put this in the AfterUpdate or OnChange Property?????

Thanks
Dave

[Bob Fitz]

I think what you have would work once you delete the space at the beginning of the query name.

Since its an update qry i dont want to practice with it, lol....

Why not make a copy and play about with that one. (and the backend, of course, if it's split)

[d9pierce1]

Thank you, I will give it a shot!

[d9pierce1]

Ran it as AfterUpDate and received error!

Error:
Too few parameters. Expected 2

[Bob Fitz]

post the SQL statement of the query

[d9pierce1]

The qry works great when i dbl clk it. I am wondering if i dont need a DoCmd in that Exe Code?

[d9pierce1]

This Qry changed and no longer works. It puts an expression 1 in instead of the Field and tbl name

[d9pierce1]

OK, I fixes Qry. Sorry about that. I was using a practice copy for this. Forgot to change the domain in email tbl...
I ran again after coirrections, i got same error excpet Expected 1

[Gicu]

If you get the query to work again (open in design view and reset the fields and table names where appropriate) you have two options:

1. use Docmd.OpenQuery instead of CurrentDB.Execute as that will not trigger the error (queries with references to forms will do that when used with .Execute). You will need to add Docmd.SetWarnings False before the .OpenQuery and Docmd.SetWarnings True after.

Docmd.SetWarnings False 
Docmd.OpenQuery "CompanyDomainUpDateQry"
Docmd.SetWarnings True

2. To use execute try the following:

Dim prm as Parameter
For Each prm In CurrentDB.QueryDefs("CompanyDomainUpDateQry")
      prm.Value=Eval(prm.Name)
Next prm
CurrentDb.Execute "CompanyDomainUpDateQry",dbFailOnError

Cheers,

[d9pierce1]

Thank you,
The First version worked well as expected.
I will stick with that.

[Gicu]

Glad to hear, actually the second version should be slightly different:

Dim prm as Parameter, qdf as DAO.QueryDef
Set qdf=CurrentDB.QueryDefs("CompanyDomainUpDateQry")
For Each prm In qdf.Parameters
      prm.Value=Eval(prm.Name)
Next prm
qdf.Execute dbFailOnError

Set qdf=Nothing

Cheers,