ACCESS FEED: Re: [MS_AccessPros] Selection on subform returns a count

Jumat, 30 Mei 2014

Re: [MS_AccessPros] Selection on subform returns a count

Its actually a combination. I am not sure if this logic will work.

dim NumRecords as integer

numRecords = dcount("*", "qryAllChoices", "communitiesID = " & nz(Me.sfrmCommunitySelectSubform,0) AND dcount("*", "qryAllChoices", "SelectionTypeID= " & nz(Me.sfrmCommunitySelectSubform,0)

Does that make any sense? The filter is being set by the communitiesID and selectiontypeid fields coming from subform.

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

alorenzini@crhanetwork.org

"Only those who will risk going too far can possibly find out how far one can go."

On Friday, May 30, 2014 11:37 AM, "Peter Poppe plpoppe@gmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Not sure if you store on the subform the communitiesID or only the communityName. But I think you just need to do a simple dcount on a some simple select query. Do not need to do the counts in the query. Build a query that has all the appropriate records for all the communities, then your dcount will filter it by community.

dim NumRecords as integer

numRecords = dcount("*", "yourqueryName", "communitiesID = " & nz(Me.someControlOnSubform,0))

NumRecords = dcount("*","yourqueryName", "communityName = '" & nz(Me.someControlOnSubform,"") & "'")

On Fri, May 30, 2014 at 9:36 AM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Close but not quite. There will only be one record on the subform but given what ever is in that record on need to count the records that already exist in the table tblCommunitySelect that are the same as the record on the subform. I created the query to pre-calculate the counts because I thought it might make it easier.

With Warm Regards,

Arthur D. Lorenzini

IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
alorenzini@crhanetwork.org
"Only those who will risk going too far can possibly find out how far one can go."

On Thursday, May 29, 2014 8:23 PM, "Peter Poppe plpoppe@gmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

It is very unclear what you are saying. I interpret this to be that there are 645 records in the subform, there is a field called selectiontype and the value "First" is selected, and there is a field community with "Eagle Butte" selected. So something like this in the after update of the community field.

Me.Parent.SomeTextBox.value = me.SelectionType & " Selection " & me.Community & " " & me.recordset.recordcount & " selections."

On Thu, May 29, 2014 at 7:08 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have my main form called frmApplicantDetail and on it is a subform called sfrmCommunitySelect.

The subform sfrmCommunitySelect is comprised of the following fields from the table tblCommunitySelect:

CommunitySelectID - PK
ApplicantID - Applicant ID
SelectTypeID - Lookup into the tlkpSelectType table (a dropdown list on the subform)
CommunityID - Lookup to the tlkpCommunity table (a dropdown list on the subform)

Back on the mainform I have a textbox called txtFirstSelectionCount in which I would like to populate with the number of selection already in the system based on what is selected in the subform. I have query written that counts the selection already called CommunitySelectionCount:

SELECT Count(tblCommunitiesSelected.CommunitiesID) AS CountOfCommunitiesID, tlkpCommunity.CommunityName, tlkpSelectionType.SelectionTypeName, tblApplication.IsCurrent
FROM ((tblCommunitiesSelected LEFT JOIN tlkpCommunity ON tblCommunitiesSelected.CommunityID = tlkpCommunity.CommunityID) LEFT JOIN tlkpSelectionType ON tblCommunitiesSelected.SelectionTypeID = tlkpSelectionType.SelectionTypeID) INNER JOIN tblApplication ON tblCommunitiesSelected.ApplicationID = tblApplication.ApplicationID
GROUP BY tlkpCommunity.CommunityName, tlkpSelectionType.SelectionTypeName, tblApplication.IsCurrent
HAVING (((tblApplication.IsCurrent)=True));

Now the scenario - The user select First from the SelectionType field in the subform and then selects Eagle Butte from the Community field in the subform. Based on that selection I want to return to the textbox on the main form "First Selection Eagle Butte 645 selections" or something of that nature.

Any ideas?

Thank you
Art Lorenzini
Sioux Falls, SD