Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Unanswered: Need a message to pop up on a form but based on a count from a table

    Hi!

    I have a access database that has a Table called "tblClassRegistration".
    The Class Registration table captures data entered from a FORM called "frmClassReg". Each class has a particular code, or ID that is unique to that class. For instance, BLTI-MON, is the code for Ballet I class on Monday's. The codes, such as "BLTI-MON" come from another table called "tblCodes". This Codes table contains the Codes and a number that represents the maximum amount of students (LIMITS) that can be enrolled in that class code. For instance, the BLTI-MON code can only have 10 class members.

    What I need to happen is on the "frmClassReg", I need a message to appear in red that says this Class is full when the class has reached its limit.

    I have an idea how to do this, but what complicates matters is that I have to also filter it using the Session Start Date. Session Start Date is entered on the form and transfers over to the "tblClassRegistration". For instance, I may have 20 students signed up for BLTI-MON, but 10 of those may be for Session 1 and the other 10 for session 2. That's where filtering by StartDate comes in to effect.

    Any help, tips, or advice would be greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    A few of your field name in both tables would help. Is this a main form/subform setup? Is form based on qry?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you can find the number of saces available in the class form one table

    you can COUNT the number of registered attendees from another table using say DCOUNT

    personally I'd stuff it into a function
    Code:
    'returns a boolean vaue if there are spaces avaialbel on the specified course
    'this function is a wrapper for GetNoSpacesOnCourse), but instead of returnign the number of spaces it says whether there are spaces or not
    function  AnySpacesInClass(ClassID as string) as boolean
        dim returnValue as Boolean
        if GetNoSpacesInClass(ClassID) <=0 then
            returnValue = false
        else
            returnValue = true
        endif
    end function
    
    public function GetNoSpacesInClass(ClassID as string) as integer
        GetNoSpacesInClass = 0
        dim ClassCapacity as integer
        dim NoRegistrations as integer  
    ' first off find the number of slots on for this class
        ClassCapacity = dlookup("<the column holding the number of spaces>", "<the table that defines the class>", "<the column that identifies the class> = '" & classid & "'")
        if isnull(ClassCapacity) then 'we didn't details of that class
            GetNoSpacesInClass = 0
            exit function
        else  'ok so we found the capacity for this class
            'next find the number of registrations
            NoRegistrations = dcount("<a column>","<the table holding registration data>", "<the column that identifies the class> = '" & classid & "'")
            if isnull(NoRegistrations) then ' we haven't found any registrations
                NoRegstrations = 0
            ENDIF
            GetNoSpacesInClass=ClassCapacity - NoRegistrations
        endif
    end function
    code is untested, unproven and will almost certainly contain typos and errors
    you will need to replace the column and table names with your values
    if the class identifier is of type string/text then it must be quote
    eg strWhereClause = "MyClassID = '" & aStringVariable & "'"
    if its numeric you don't need to
    eg strWhereClause = "MyClassID = " & aNumericVariable

    you coudl push the dcount into another function so that if you need to you have a quick and easy way of fidnign out howmany are registered.

    having done that you then need to place calls to the function(s) as required
    at what points would you want to stop the ability to add a new record
    ..when someone tries to add a new record, when someone dispalys a new record, after someone has added a record

    id consider altering the como box so that it only displays courses that have space, but if you do you need to requery that combo box after every new record or edited record that has changed the class regsitrations

    heck you could even display the number of spaces available in the combo box
    bear in mind you may have problems with this appraoch if you are working in a multi user environment. if you are then you'd need to do some checking in the before update event.

    this strategy may not be good enough if the appliucation is intended to be live (ie handling someone's own registration or via say a telephone call. you'd need a different approach to lock the space whilst you are filling out the new record, or allocate it temporarily and deallocate if the registration fails
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2012
    Posts
    7
    The form is based on the tblClassRegistration, and has one drop down box with list of codes from the tblCodes, field name: Code.

    tblClassRegistration fields:
    RegID
    StudentID
    StartDate
    ClassCode (this gets the variable passed from value of the dropbox on the form)

    tblCodes Fields:
    Code (class codes)
    Description
    TimeStart
    TimeFinish
    Limits (this is the max number of students per class)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Dont think you need regid as a composite of studentid and classcode should do. It also has the added advantage that you cannot duplicate a student class pairing
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2011
    Posts
    413
    Your criteria or explanation for the date scenarios is not clear enough! Specifics.

  7. #7
    Join Date
    Nov 2011
    Posts
    413
    I noticed that you double posted on Utter Access??? That is generally frowned upon!
    Access Help and How-to - Microsoft Office Message On Form - UtterAccess Discussion Forums

Tags for this Thread

Posting Permissions

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