Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Question Unanswered: dcount problems pls help

    Hi, i have a main form and a subform, 1 to many relationship. On the main form i want a text box to show how many subrecords there are in the subform. I wrote a query that looked for the taskid in the main form and looked for the taskid in the underlying table of the subform. I then used a Dcount on the query and looped through each record in the main form to find out how many subrecords each main record had. The looping through each record works ok but it's not doing a dcount on each main record. It appears to only do a Dcount on the first record it finds and then fills in my text box for all records with this value. thx in advance.

    Dim rs As DAO.Recordset
    Dim intcount As Variant
    Set rs = Me.RecordsetClone

    If Not rs.BOF And Not rs.EOF Then
    rs.MoveFirst
    Do Until rs.EOF
    intcount = DCount("[taskid]", "qryanysubtasks")

    If intcount >= 1 Then
    Me.txtrecords = intcount
    Else: Me.txtrecords = "0"

    Exit Do
    End If
    rs.MoveNext

    Loop
    rs.Close
    Set rs = Nothing

  2. #2
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    i am right in assuming that your mail form is a continious one? (i think from how you described it it is!)

    if this is the case the reason this happens it that access doesn't handel control arrays well(if at all!). each instance of the text box is called them same thing, therefore when you use the code you are using to assign a value to that text box it does it to all of them, as access thinks they are the same thing. does that make sense?

    anyway, the way to avoid this problem to do the following:

    base you form on a query and not a table (if that is how you have done it)

    add to that query an extra column as follows:

    RecordCount: DCount("[taskid]", "qryanysubtasks")

    then base the text box on the value of this result

    this should sort out the problem. if it doesn't work get back to me (it's been a while since i did something like this and my syntax might be a bit off!)

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    Hi Emma, thx for the reply. You asummed right, my main form is based on a query and it is datasheet view. I did what you said and the principle works fine, much quicker than using VBA but i am still only getting the dcount of the first record in the main form. It's not treating each record seperatly. It looks like the query is only been run once, not once for each main record. I tried using the dcount function on an unbound txtbox on the mainform. This actually worked and i thought great. But when i move to another record it crashes access.
    Any thoughts

  4. #4
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    yes, thinking again i can see why that is happening, i'm not sure how to solve it though. i'll have a think and get back to you.
    any chance of posting your db on the site, i might have a brainwave if i take a look at the data! i think it might have to do with the query you running the dcount on not knowing what record you are currently on - might need some creative query linking!

  5. #5
    Join Date
    Sep 2003
    Posts
    5
    Hi Emma, i can give you the table structures

    tblTask (main form)
    TaskID
    tasked_for
    what_task
    Task_created
    Task_completed

    tblGroupTask (subform)
    ID
    TaskID
    tasked_for
    what_task
    Task_created
    Task_completed

    Query looks like this (synax could be wrong)
    SELECT DISTINCTROW Count(*) AS RecordCount
    FROM tasks INNER JOIN tblgrouptasks ON tasks.TaskID = tblgrouptasks.TaskID
    HAVING (((tblgrouptasks.TaskID)=[forms]![job_no subform].[tasks].[form]![taskid]));
    This gives me a results like (which is what i want)

    TaskID RecordCount
    2000 2
    2001 1
    2003 4

    looking at the query more closly i think its there WHERE statement. IT is looking on my main form for TASKID. The first instance of TASKID it finds it uses. How to get round this i have no idea yet.
    cheers for all your help by the way

  6. #6
    Join Date
    Sep 2003
    Posts
    5
    Hi, i finally got it to work how i want. On my subform i have a bound txtobx to the recordcount field in my query. The problem is the query is not updatable because it contans an agrregate function. how can i achieve the same results but with a editable query.
    This is my main query

    SELECT qryAnysubTasks.RecordCount, tasks.TaskID, tasks.JobID, tasks.Tasked_for, tasks.What_task, tasks.Task_due, tasks.last_contact, tasks.next_contact, tasks.task_completed, tasks.priority_level, tasks.task_notes
    FROM tasks LEFT JOIN qryAnysubTasks ON tasks.TaskID = qryAnysubTasks.TaskID
    WHERE (((tasks.task_completed)="no"))
    ORDER BY tasks.Task_due;


    This is my subquery that does the recordcount
    SELECT DISTINCTROW Count(*) AS RecordCount, tblgrouptasks.TaskID
    FROM tasks INNER JOIN tblgrouptasks ON tasks.TaskID = tblgrouptasks.TaskID
    GROUP BY tblgrouptasks.TaskID, tblgrouptasks.task_completed
    HAVING (((tblgrouptasks.task_completed)="no"));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •