Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2005
    Posts
    7

    Unanswered: Access/vb code help

    Hello everyone,

    Couldnt decided to post in access or in vb, i selected access as you can see

    could any one please help me with this problem i'm having in access? i'll set the scene

    What i'm trying to do is search though a table called 'tblDelegate' and the field called 'iDelegateId', and compaire iDelegateId to iCourseID.Value (this is a field from a form, its an int) if they are the same add 1 to my counter, and do this for all the number of fields in iDelegateid.

    So in pseudo/java'ish code

    int iCounter =0;
    iNumRecords = getNumrecords()

    for (int i =0; i++; i < iNumRecords)
    {
    currentValueOfiDelegateId=getvaluemethod()
    if (currentValueOfiDelegate == iCourseID) { iCounter++; }

    }

    This is my VB code

    Code:
            'this gets the number of records
        iNoRecs = DCount("iDelegateId", "tblDelegate")
    
        Dim iCounter As Integer
        'var to hold number times occurs
        iCounter = 0
        Dim sVal As Variant
        Dim iLoop As Integer
        For iLoop = 1 To iNoRecs
    
            sVal = DLookup("iCourseID", "tblDelegate", "iDelegateId =iLoop")
            If vVal = iCourseID.Value Then iCounter = iCounter + 1
            
        Next iLoop
    And this is the error i get?

    "Run-time error '2001'
    you canceled the previous operation"

    Does any one have any ideas what i'm doing wrong? or if there a better way to do this?

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tutty tut moley - good job you came to the Access forum. You want to use a set based operation not a loop.

    Code:
     iCounter = DCount("iCourseID", "tblDelegate", "iDelegateId = " & Forms!MyFOrm!MyComboWithTheCourseIDInIt")
    could, I think, replace all your code.

    HTH
    Last edited by pootle flump; 11-17-05 at 08:22.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just noticed it is your first post - welcome to the forum
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2005
    Posts
    7
    based operation? do you mean function/method or am i getting lost

    Code:
        iCounter = DCount("iCourseID", "tblDelegate", "iDelegateId = " & "Forms!course!iCourseID.value")
        MsgBox iCounter
    This always returns one, even though i have 3 tblDelegateId's that match it, is this what you meant about putting in a based operation?

    Thanks for you help already

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - I left in a "
    Code:
     iCounter = DCount("iCourseID", "tblDelegate", "iDelegateId = " & Forms!course!iCourseID.value)
    The DCount is merely a function that returns a value from a set based operation. It is effectively a query without the predicate keywords (SELECT, FROM and WHERE).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - set based means working with the set (the records in the table) rather than a record at a time. More SQL then VB.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2005
    Posts
    7
    figured out where i'm going wrong, the method you posted is taking iCourseID from the table tblDelegate, which is what obviously should be doing ("tblDelegate") and this is why its only return 1, is it possible do something like this:

    iCounter = DCount("tblCourse.iCourseID", "tblDelegate", "iDelegateId = " & Forms!course!iCourseID.Value)

    This code doesnt work, i get the error i had before about:

    "Run-time error '2001'
    you canceled the previous operation"

    is it possible two tables after the 1st comma, i've tried but it eirther doesnt work or i have the systax wrong?

    Thanks for saying welcome by the way in the other post, and thanks for your help this have had me stuck for over a day!

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Could you post the entire sub again up to the point where it errors?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by molestheman
    figured out where i'm going wrong, the method you posted is taking iCourseID from the table tblDelegate, which is what obviously should be doing ("tblDelegate") and this is why its only return 1, is it possible do something like this:

    iCounter = DCount("tblCourse.iCourseID", "tblDelegate", "iDelegateId = " & Forms!course!iCourseID.Value)

    This code doesnt work, i get the error i had before about:

    "Run-time error '2001'
    you canceled the previous operation"

    is it possible two tables after the 1st comma, i've tried but it eirther doesnt work or i have the systax wrong?

    Thanks for saying welcome by the way in the other post, and thanks for your help this have had me stuck for over a day!
    No probs and I get the problem now. To be sure - please pop this sql into a query and run it. Are the counts looking about right for each course?

    Code:
    SELECT COUNT(C.iCourseID) AS NoOfDels, C.iCourseID 
    FROM tblCourse AS C INNER JOIN tblDelegate AS D 
    ON C.iCourseID = D.iCourseID
    GROUP BY C.iCourseID
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2005
    Posts
    7
    Yeah that gives the correct answer, i'm lookin for! How would i do that in code?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Something like (DAO code):

    Code:
    Dim rst as DAO.Recordset
    Dim strSQL as String
    Dim iCounter as Integer
     
    strSQL = "SELECT COUNT(C.iCourseID) AS NoOfDels " & _
    "FROM tblCourse AS C INNER JOIN tblDelegate AS D " & _
    "ON C.iCourseID = D.iCourseID " & _
    "Where C.iCourseID = " & Forms!course!iCourseID.value
     
    Set rst = Application.CurrentDB.OpenRecordSet(strSQL, dbOpenForwardOnly)
     
    rst.MoveNext
     
    If not rst.EOF Then
    iCounter = rst(0)
    Else
    'There is no course of that number - maybe display a message?
    End If
    HTH
    Last edited by pootle flump; 11-17-05 at 10:58. Reason: Oops - forward only rst - can't use that method.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2005
    Posts
    7
    that gives me the following error:

    Run-time error '91':

    Object variable or with block variable not set


    it falls over on this line

    rst.MoveFirst

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - I've made a couple of edits - I posted air code originally and forgot a thing (or two...) check whats on there now.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2005
    Posts
    7
    the code runs, but iCounter never changes from zero?

    I've uploaded my access file if you wanna take a look at it?

    http://www.indecentmonkey.com/bookings.mdb

    Thanks for you help again

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Will do. I for one was disappointed not to see any indecent monkeys
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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