What's an alternative for DCount?

L

lmcc007

Registered User.
Local time
Today, 17:03
Joined
Nov 10, 2007
Messages
635
I am using the below code to fill some information. I keep having write conflicts and Error 3188 with this form; therefore, I am wondering is there a better way of getting this information?

Code: 
Private Sub Form_Load()
    Me!txtAuthorizations = ActivityCount(1)
    Me!txtAttachments = ActivityCount(2)
    Me!txtHostedJobFair = ActivityCount(3)
    Me!txtPhoneCalls = ActivityCount(19)
    Me!txtConductedInterviews = ActivityCount(8) + ActivityCount(9) + ActivityCount(10) + ActivityCount(11)
    Me!txtMeeting = ActivityCount(16)
    Me!txtNotes = ActivityCount(17)
    Me!txtClientComplaints = ActivityCount(18)
    Me!txtConductingPhoneInterviews = ActivityCount(21)
    Me!txtSearchedOnline = ActivityCount(24)
    Me!txtSends = ActivityCount(5) + ActivityCount(7) + ActivityCount(15)
    Me!txtTesting = ActivityCount(25)
    Me!txtToDos = ActivityCount(27)
    Me!txtTraining = ActivityCount(28)
End Sub

Public Function ActivityCount(ByVal strEventType As Integer) As Integer
    ActivityCount = DCount("[ActivityID]", "Event", _
        "[CompanyID] = " & Forms!frmViewEventHistory!txtCompanyID & " And " & _
        "[ActivityID] = " & strEventType)
End Function
 
Have you tried a totaling query.

You could display the data in a continuous form. Probably No VBA code required.
 
HiTechCoach said:
Have you tried a totaling query.

You could display the data in a continuous form. Probably No VBA code required.
Click to expand...

If I am understanding what you're saying, I was doing a query for each activity--e.g.:

Field = CompanyID
Table = Event
Total = Group By

Field = ActivityType
Table = tlkpActivity
Total = Count

Field = ActivityID
Table = Event
Total = Group by
Criteria = 23

But, I was wondering would that be too many queries to create and then try to join with Event to create the form/report.

I just want the best and most accurate way of doing it.
 
the code looks OK - i doubt if any write errors are an issue with the dcount, anyway

what error are you getting exactly?

--------
just to test your code and make sure the error isnt connected with the dcount , I would either use a breakpoint, or put some msgboxes after each step temporarily
 
gemma-the-husky said:
the code looks OK - i doubt if any write errors are an issue with the dcount, anyway

what error are you getting exactly?

--------
just to test your code and make sure the error isnt connected with the dcount , I would either use a breakpoint, or put some msgboxes after each step temporarily
Click to expand...

Error 3188 -- Couldn't update. Currently locked by another session on this machine
 
Your problem might be a self-lock because those DCounts are all on the same source. This is difficult to determine from what you presented, but here's the question that would tell the real result: Is the form you are using (the one implied by "Me") using the same table from which you are extracting those counts? If so, check your locking parameters for the form. You want it to use optimistic locking if the answer to my question is "Yes."

Also, I'm going to lay HUGE odds that this snippet fails silently (if you are LUCKY it will be silently)...

Code: 
 "[ActivityID] = " & strEventType)

strEventType is clearly an integer as noted in your formal argument passage declaration. But in that context, you must supply it as text - not because [ActivityID] is a text field... but because the criteria clause is text. Myself, I would use

Code: 
 "[ActivityID] = " & CStr(strEventType))

when faced with that situation.
 
The_Doc_Man said:
Your problem might be a self-lock because those DCounts are all on the same source. This is difficult to determine from what you presented, but here's the question that would tell the real result: Is the form you are using (the one implied by "Me") using the same table from which you are extracting those counts? If so, check your locking parameters for the form. You want it to use optimistic locking if the answer to my question is "Yes."

Also, I'm going to lay HUGE odds that this snippet fails silently (if you are LUCKY it will be silently)...

Code: 
 "[ActivityID] = " & strEventType)

strEventType is clearly an integer as noted in your formal argument passage declaration. But in that context, you must supply it as text - not because [ActivityID] is a text field... but because the criteria clause is text. Myself, I would use

Code: 
 "[ActivityID] = " & CStr(strEventType))

when faced with that situation.
Click to expand...

Thanks for responding The_Doc_Man,

Actually, I copied this code from here--I thought I understood it, but apparently I don't.

Yes, everything is coming from the Event table.

Locking parameters--Optimistic locking? Where do I find this?

EventType is integer. (I think that's what you're asking me).
 
Why a query for each activity? What is different that you need a query for each?
 
Did you know you can have more than one total for various activities in a single query? But I'm not seeing an Activity table in your sample you uploaded. So, perhaps with a little more information I can give you a sample of what I mean. What fields are you using or talking about? Or was the example from yesterday not with the extra table?
 
boblarson said:
Did you know you can have more than one total for various activities in a single query? But I'm not seeing an Activity table in your sample you uploaded. So, perhaps with a little more information I can give you a sample of what I mean. What fields are you using or talking about? Or was the example from yesterday not with the extra table?
Click to expand...

tlkpEventType is the same as Activity (different wording in different table).

No, I didn't know that. But I just tried it and it comes up blank. Meaning, I put

Field = CompanyID.....EventTypeID.....EventTypeID..... EventTypeID
Table = Event...........Event...............Event..............Event
Total = Group By.......Count..............Count................Count
Criteria = 1...............2....................3.....................4
​
 
boblarson said:
Check out my crosstab query in the example (it is based on Query1).
Click to expand...

Wow, this is exactly what I am trying to do. I have not done a Crosstab Query. Let me read up on Crosstab and play with it and then I'll get back with you.

I'm feeling pretty stupid right now creating all the total queries. Boy 'o boy a half day waisted.

I need to take a lunch break and come back to it.

Thanks boblarson!
 
boblarson said:
Check out my crosstab query in the example (it is based on Query1).
Click to expand...

Now, I can't get it to work with my form. I created subformforFORM using Query1_Crosstab1 query and it's not working. Meaning:

1. Keep losing the master and child links

2. I tried using Nz([EventTypeID], 0), but it does nothing. Basically, I want all the fields to show whether it is blank or not.

When in frmCompany I click the EventHistory button to see a list of events, which opens FORM. FORM is the first form and subformforFORM is the subform.

What am I doing wrong now?

Attach if the db.
 

Attachments

Why are you trying to use this on a form? I thought you were using it on a REPORT, not a form. What is the point of having this on a form?
 
boblarson said:
Why are you trying to use this on a form? I thought you were using it on a REPORT, not a form. What is the point of having this on a form?
Click to expand...

To get a quick glimpse of all the events. Then once the form is working; they can double click an event from the list to see the details.
 
boblarson said:
Why are you trying to use this on a form? I thought you were using it on a REPORT, not a form. What is the point of having this on a form?
Click to expand...
I just found out from Access 2007 Bible that Crosstab queries are not updateable, so that's not what I need for this form.
 
You must log in or register to reply here.

Similar threads

L
DCount not totaling
Replies
3
Views
872
pbaldy
pbaldy
L
Unexplained Error Message
Replies
7
Views
1,575
RuralGuy
RuralGuy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom