DCOUNT result not being passed to tab control caption (error)

Question

I've create a form with a tab control in MS-Access.

On one of the tabs I have 2 subform controls showing records from the same table. This table has a field I've called "Status" and can have an entry of either "Open" or "Closed"; the 2 subforms simply show these 2 options filtered as separate groups on the tab control.

I then have a button underneath each of the subforms; the user selects (clicks into) one of the records in either of the subforms and then the button will open a new "Edit" form at the selected record in subform so they can see more information and also toggle the "Status" field to "Open" or "Closed".

When the user presses the "Save and Close" button this saves the changes, closes the "Edit" form, and re-queries the 2 subforms.

What I'm trying to do is get a count of the number "Open" records still showing after the "Edit" form is closed. The count number is then to be passed in to the tab caption for this tab so users can see at a glance how many "open" records are against the record on this main form.

Here's my code so far:

Private Sub cmdAdminIssue_Edit_SaveClose_Click()

' save and close currently open edit form
    DoCmd.Close acForm, "tblAdminIssue_Edit", acSaveYes

' requery the subforms to show the effect of the edits made via the edit form   
    Forms![tblJobs]![tblAdminIssue_Sub_Open].Form.Requery
    Forms![tblJobs]![tblAdminIssue_Sub_Closed].Form.Requery

' count number of records still open and pass the number through to the tab control caption property    
    Dim AdIssOpenCount As Long

        AdIssOpenCount = DCount("JobID", "qryAdminIssue_Open", "JobID = '" & Me![JobID] & "'")
        Forms![tblJobs]![tab_AdIssues].Caption = "Admin Issues (" & AdIssOpenCount & ")"

End Sub

The above code triggers the error: "The expression you entered refers to an object that is closed or doesn't exist", highlighting my DCount expression in debug.

However I can get the DCount to work independently of the other code above it; they seem to be interfering with each other, but I don't quite understand how.

Referring to the error message above it seems to be suggesting that DCount needs the query I referenced to open in order to run... this doesn't make much sense to me as I thought these kinds of functions do not require you to explicitly open in code an object it's trying to get data from... I'm probably misinterpreting what this error actually means though.

Any explanation of the error and a possible workaround would be much appreciated. Thanks.

John Bingham John Bingham · Accepted Answer · 2013-11-01T02:00:12

While you've clearly done your best to explain the situation it seems pretty complicated.

I'm going to try and break it down a bit, and suggest something you might do.

"I've create a form with a tab control in MS-Access.
On one of the tabs I have 2 subform controls showing records from the same table. 
This  table has a field I've called "Status" and can have an entry of either "Open" 
or "Closed"; the 2 subforms simply show these 2 options filtered as separate groups on 
the tab control."

So, you have a form, with 2 subforms. That they're on a tab page shouldnt really be relevant. I'm going to reference the form as frm & the subforms as sf_o & sf_c.

Button underneath each subform (opening the detail form with a double-click might be nicer) - the "save & close" button is presumably in the popup screen; This button is currently performing the save of it's own data, and doing everything else as well.

This is very messy, as it means that the contents of frm cannot be modified without having to make changes to the logic of the save/close button as well. A better approach would be to separate the logic for refreshing the main form from the editting form using a public method in the main form (frm):

Public Sub RefreshData()
    sf_o.Form.Requery
    sf_c.Form.Requery
    'other logic
End Sub

then, you have:

Private sub cmdAdminIssue_Edit_SaveClose_Click()
    'save & close logic...

    Forms(frm).RefreshData
End sub

Okay now, the other logic. You want to find out how many records are now "Open" and put this information somewhere. In this case, somewhere is a tabpage caption, but this isnt really material.

I never use DCount (DLookup etc) as it is very slow. An alternative (and in this case, much simpler) method would be to use the forms own properties:

tab_AdIssues.Caption = "Admin Issues (" & trim(sf_o.Form.RecordsetClone.RecordCount) & ")"

How's that? Then you dont need to try and figure out what's gone wrong in all that pre-compiled logic which is microsoft access.

Hope this helps

DCOUNT result not being passed to tab control caption (error)

1 Answers