Help with Dcount! (1 Viewer)

reglarh

Registered User.
Local time
Today, 04:21
Joined
Feb 10, 2014
Messages
118
I am trying a simple update task to count the number of records in linked table that belong to one record in a master table. The master table is of Members, the linked table, GroupMembers, is a list of groups that each member belongs to. A simple one to many join.

I have tried the following code:

DoCmd.RunSQL "Update Members Set GroupCount = DCount("*", "GroupMembers", "GroupMembers.GMMember=Members.MemberNumber")"

The visual basic editor keeps wanting an end statement before the last conditional statement in the Dcount expression.

What am I doing wrong?
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 07:21
Joined
Jun 21, 2011
Messages
5,901
Not sure I understand your DCount() as you can't *link* tables together like that in a statement. You need to create a query that has those two tables in the use the DCount() against the query.

That said you should get the value of the DCount() and then place that in your UPDATE statement, i.e.

Dim MyCount As Long

MyCount = DCount statement

Then you could say UPDATE Members SET GroupCount = " & My Count &"

And thinking out loud... why do you want to update that value to a table when you can get it anytime on the fly?
 

June7

AWF VIP
Local time
Today, 03:21
Joined
Mar 9, 2014
Messages
5,423
Agree with Gina. Saving calculated data especially aggregate data is usually bad design. Saved value can get 'out of sync' with raw data.

Aside from that, the SQL won't construct with inner quote marks as you have them.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:21
Joined
May 21, 2018
Messages
8,463
As stated there is absolutely no reason to do this. This can be displayed very easily with an aggregate query in real time. If you wanted to do this all at once it is not so easy. If using a dlookup, aggregate query, or sub query to get the totals the query is not update able so you will have trouble doing an update query. Any update query based on an aggregate query is not updateable.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:21
Joined
May 7, 2009
Messages
19,169
concatenation is the problem. shoul be:

DoCmd.RunSQL "Update Members Set GroupCount = DCount(" & chr(34) & "*" & chr(34) & "," & chr(34) & "GroupMembers" & chr(34) & "," & chr(34) & "GroupMembers.GMMember=Members.MemberNumber" & chr(34) & ")"
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:21
Joined
Jan 20, 2009
Messages
12,849
concatenation is the problem. shoul be:

DoCmd.RunSQL "Update Members Set GroupCount = DCount(" & chr(34) & "*" & chr(34) & "," & chr(34) & "GroupMembers" & chr(34) & "," & chr(34) & "GroupMembers.GMMember=Members.MemberNumber" & chr(34) & ")"

That incorrect concatenation is the immediate problem. However the query string would still be invalid whatever was done with the quotes.

The intermediate problem is they are attempting to join tables in the criteria parameter of a Domain function.

The ultimate problem is that they are attempting to store something that should be calculated.
 

reglarh

Registered User.
Local time
Today, 04:21
Joined
Feb 10, 2014
Messages
118
Wow - that tells me!

I accept all that has been said in the above replies. In my defence, when I am producing new forms and reports that require repeated use of a calculated field I store it for speed. Not good practice but I am now where I am.

And yes, the saved value has been corrupted so that some (not all) records that should have a value of zero have a value of 1. And it is these records I want to delete. So my first quick fix was to correct the incorrect data, hence an update to count the number of linked records and update this figure in the master members records, A second change will be to remove the calculated stored value and replace it in the many forms and reports in which it is used,

The consensus seems to be that no matter how I express the Dcount function, it will not work with my SQL Update command.

So how do I achieve what I want to do?

Either correct the stored count item, when my existing delete function will work correctly or to write code to ignore the stored value and delete the Member record when there are no matching records on the linked table. Both of these approaches fail on the use of the Dcount function.
 

GinaWhipp

AWF VIP
Local time
Today, 07:21
Joined
Jun 21, 2011
Messages
5,901
It will work if you use a query where you join those two tables and use a query instead. Are you saying that's not possible?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:21
Joined
May 21, 2018
Messages
8,463
delete the Member record when there are no matching records on the linked table
Code:
DELETE members.memberid 
FROM   members 
WHERE   members.memberid  NOT IN (SELECT member_id FROM  groupmembers)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:21
Joined
May 7, 2009
Messages
19,169
so it wont update, saying that
it need an updateable query.

the workaround is to create a Public Function
in a Standard Module:
Code:
Public Function fncCount(Member As Variant) As Long
	If (VarType(Member) = vbLong) Then
		fncCount = Nz(DCount("1", "GroupMembers", "GMMember=" & Member), 0)
	Else
		Member = Member & ""
		fncCount = Nz(DCount("1", "GroupMembers", "GMMember=" & Chr(34) & Member & Chr(34)), 0)
	End If
End Function

now back to your query, revised it to this:
Code:
DoCmd.RunSQL "Update Members Set GroupCount=fncCount(Members.MemberNumber)"
 

June7

AWF VIP
Local time
Today, 03:21
Joined
Mar 9, 2014
Messages
5,423
I am now confused. Explain again why you want to delete record? Exactly what is relationship of tables? What is nature of data? Which is master and which is dependent (ex: Orders and OrderDetails).
 

reglarh

Registered User.
Local time
Today, 04:21
Joined
Feb 10, 2014
Messages
118
Thanks to all who those who have helped. I took the recommendations to remove the field and calculate it when needed. I hope I have found all the places where it is used!
 

Users who are viewing this thread

Top Bottom