Error 2342 - Check duplicates statement

[micang]

Access 2003

Hi All,

I am running the code below, and get an error:

Private Sub Command2_Click()

Dim sqlmv As String

sqlmv = "SELECT First(SVT_ALL.MVRIS_MAKE_CODE) AS [MVRIS_MAKE_CODE Field], First(SVT_ALL.MVRIS_MODEL_CODE) AS [MVRIS_MODEL_CODE Field], Count(SVT_ALL.MVRIS_MAKE_CODE) AS NumberOfDups FROM SVT_ALL GROUP BY SVT_ALL.MVRIS_MAKE_CODE, SVT_ALL.MVRIS_MODEL_CODE HAVING (((Count(SVT_ALL.MVRIS_MAKE_CODE))>1) AND ((Count(SVT_ALL.MVRIS_MODEL_CODE))>1));"
DoCmd.RunSQL sqlmv

End Sub

It is giving me run time error 2342 " A RUNSQL action requires an argument of consisting of an SQL statement." Then in debug it highlights "DoCmd.RunSQL sqlmv" in yellow.

The query runs fine in design view (it checks for duplicates on a combination of two fields), but in VBA it does not.

Could anyone shed some light as to why it wont run in VBA?

Many thanks.

Michael

 

[PHV]

why it wont run in VBA
The RunSQL method allows only action queries.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[micang]

Thanks PH, I was not aware of that.

I ahve been doing reading up since your post. I can't seem to find how to run the above in VBA. I see I can use the openquery to run the saved query, but is this the only option for these type of queries?

Thank you for your input thus far.

Michael

 

[PHV]

What do you want to do with the result set ?
You may consider a ListBox having this SQL as RowSource, or a Form with this SQL as RecordSource, or play with a Recordset, or ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[micang]

At the moment all that needs to be done is to check if there are duplicates, if there are, they have to be looked at and fixed manually.

Thank you for your suggestions, much appreciated.

Michael