What's an alternative for DCount? (1 Viewer)

[lmcc007]

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?

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

 

[HiTechCoach]

Have you tried a totaling query.

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

 

[lmcc007]

Have you tried a totaling query.

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

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.

 

[gemma-the-husky]

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

 

[lmcc007]

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

Error 3188 -- Couldn't update. Currently locked by another session on this machine

 

[The_Doc_Man]

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)...

 "[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

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

when faced with that situation.

 

[lmcc007]

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)...

 "[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

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

when faced with that situation.

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).

 

[boblarson]

Why a query for each activity? What is different that you need a query for each?

 

[lmcc007]

Why a query for each activity? What is different that you need a query for each?

Total query.

Total for each activity.

 

[boblarson]

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?

 

[lmcc007]

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?

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]

Check out my crosstab query in the example (it is based on Query1).

 

[lmcc007]

Check out my crosstab query in the example (it is based on Query1).

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!

 

[HiTechCoach]

Glad to see that Bob helped you out with a Crosstab Query. I use them a lot. This might help:

Dynamic Report based on a Crosstab query

 

[lmcc007]

...check your locking parameters for the form. You want it to use optimistic locking if the answer to my question is "Yes."

How do check the locking parameters??

 

[lmcc007]

Glad to see that Bob helped you out with a Crosstab Query. I use them a lot. This might help:

Dynamic Report based on a Crosstab query

Thanks, I'll read it later when my brain settles down.

 

[lmcc007]

Check out my crosstab query in the example (it is based on Query1).

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.

 

[boblarson]

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?

 

[lmcc007]

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?

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.

 

[lmcc007]

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?

I just found out from Access 2007 Bible that Crosstab queries are not updateable, so that's not what I need for this form.