Recordset and DCount issue

[Malcy]

I am trying to move some of my queries into code to try to make my db a bit more compact and manageable but I am having difficulties.
This is the code I have put in (the connection and recordset are all dimensioned and set earlier)
I am trying here to flag a field in each record when the patient (lngPtId) occurs more than once in the table.
___________________________
rst.Open "tblTmpDispLab", cnn, adOpenDynamic, adLockOptimistic
With rst
.MoveLast
.MoveFirst
Do While Not .EOF
If DCount("lngPtId", "tblTmpDispLab") > 1 Then
.Fields("bDispMult") = Yes
.Update
End If
.MoveNext
Loop
______________________________
It falls over on the .Fields("bDispMult") = Yes line.

Is it that I am trying to run a DCount off the recordset I am working with (if so does that mean working with a recordset clone which I have heard of but never used), or do I need to somehow define my DCount differently?
Any help very much appreciated
Thanks

Malcy

 

[billyr]

Need criteria.

Try adding a criteria to the DCount function to specify that you are counting Ids equal to the current record Id in rst.
If [lngPtId] is a string:
If DCount("[lngPtId]", "tblTmpDispLab", "[lngPtId] = '" & rst![lngPtId] & "'") > 1 Then...
If [lngPtId] is a long integer the criteria punctuation changes and I don't remember accurately how. Access help will show you how.

 

[Malcy]

Thanks billyr
I have played around and currently have

If DCount("[lngPtId]", "tblTmpDispLab", "[lngPtId] =" & rst![lngPtId]) > 1 Then

which I think (?!?) is right since the field is a long integer.
The fail message I keep getting is

"Multi-step OLE DB operation generated errors. Check status values. No work was done"
And right enough it doesn't do the business.
Have searched on the above error on this forum and it is mentioned twice but no replies to either so I guess I am going to have to find another way around things.
Thanks for your help
Best wishes

Malcy

 

[billyr]

Try counting another field that is sure to have data in it. I think I had some difficulty once when the field counted was also the criteria field. Surely there is a patient name field. DCount("[ptName]"...?

 

[billyr]

I checked MS Knowledge Base and found this example.
MyTotal: CLng(DCount("OrderID", "Orders", "[EmployeeID] = " & EmployeeID"))
A couple of things stand out not the least of which is that the field counted, "OrderID" is different from the criteria field. Note also that they are converting the result to a long. I wonder if that eliminates the need for "If not isnull(..."? Maybe someone else will weigh in on this.

 

[dcx693]

Why do you want to hard code a field flag? If you're doing this to mark dupes, you might want it to recalculate dynamically....but, it's your database.

I would avoid using DCounts if possible. Why not use a SQL update query instead? Batch updates usually go faster than moving through recordsets.

The general syntax would be something like:

Dim strSQL As String
    strSQL = "UPDATE tblTmpDispLab SET bDispMult=TRUE WHERE lngPtId In " & _
        "(SELECT lngPtId FROM tblTmpDispLab " & _
            "GROUP BY lngPtId HAVING Count(*)>1);"
    CurrentProject.Connection.Execute strSQL

I'm assuming that bDispMult is a boolean field. You can change the value it's set to in that statement above if that's not the case.

Of course, when working with batch updates, make sure to test the code on a backup of your data!

 

[Malcy]

Thanks guys
I tried out the SQL update and it worked fine. I was under the impression, clearly mistaken!, that it was probably better programming to use ADO type record manipulation rather than SQL.
One lives and one learns!!!
Thanks too billyr for your further help.
Will now continue building on the SQL type model wherever possible.
Best wishes

Malcy

 

[dcx693]

I was under the impression, clearly mistaken!, that it was probably better programming to use ADO type record manipulation rather than SQL.

You can sometimes achieve the same results with ADO/ADOX as you could with SQL. In that case, which method you choose will depend on other factors not the least of which is your own comfort level. SQL is just better for batch updates of data.

It's good to have some familiarity with both techniques. If for no other reason than the fact that SQL is a pretty standardized language and can be used with many other databases.

 

[Malcy]

It goes on!!!

Hi,
Can I beg a little more of your time?
One of the next updates I set up was this

"UPDATE [tblTmpDispLab] SET [tblTmpDispLab].[bytDispSeq] = 1" & _
"WHERE ((([tblTmpDispLab].[lngDrugId])=93));"

I set up the query in design and then copied the SQL version into my VBA. It runs fine as a query but when I try running it on a cnn.execute I get the following message

Syntax error(missing operator) in query expression "1Where((([tblTmpDispLab].[lngDrugId])=93))

I cannot fathom what is wrong since it seems to not be seeing the break between the 1 and the where. I have tried adding various sequences of brackets and quotation marks but to no avail. I also put all the table and field names into square brackets since one thread seemed to suggest that worked better.
What is it I am missing?
Thanks

Malcy

 

[dcx693]

The _ line break in VBA is simply that - a line break. VBA treats the two lines as if they were physically on the same line. That means that if you want any spaces in between words like

"[bytDispSeq] = 1 WHERE [i]etc...[/i]"

then you need to include a space. Try:

"UPDATE [tblTmpDispLab] SET [tblTmpDispLab].[bytDispSeq] = 1 " & _
"WHERE ((([tblTmpDispLab].[lngDrugId])=93));"

Note the space between the 1 and the " & _.

 

[Malcy]

That simple!!
Thanks very much. Will try and plough further forward until the next stumbling block! Hopefully it will not arrive too swiftly!
Best wishes from Edinburgh with even a little bit of sunshine

Malcy

 

[dcx693]

No prob. Good luck. We need some sunshine. It's a dreary day here in Brooklyn.