Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling All Pro's (2000)

    I need direction ( I know it needs to be a Public Function) .....

    I have a qry that is pulling records...not sure if that is the correct approach...or should go with a recordset count....at any rate....

    The qry determines if certain records meet a criteria...i.e. is it Time to Follow Up on Something....OK...no problem here....what I need now is when the db opens, I want a msg box to inform the user, that he/she has a Follow Up and which one.

    Now...I think that's the easy part...here is the tougher part....the users log into the system (NOT by Access Security) and their ID is stored into a LogIn tbl and their ID is also in the table that holds information for the specific projects. When that User logs in and IF that user has a record to be followed up on ONLY then does the msg pop up. In othe words If user EM has an outstanding Followup today but doesn't log in but user LR does...no msgbox....but if EM does login then the msgbox....

    And yes...the userID in the LogIn tbl equals staffID in projects table....if that is an issue.

    The following is what I have so far for a Public Function....I'm missing quite a bit....but not sure (I think I will need a LOOP....)how to complete it.

    Dim intNotify As Integer
    Dim dteDateDue As Date
    Dim dtePresent As Date
    Dim strReminder As String, strProject As String, strUser As String, strStaff As String

    Dim dbs As Database
    Dim rst As Recordset

    Set rst = dbs.OpenRecordset("tblInitialTable") 'The table that hold project information

    strStaff = rst.Fields("UserID") 'This is the Security Login User ID
    strProject = rst.Fields("Project") 'This is the Project #
    intNotify = rst.Fields("Follow Up Calculator") 'This is the # of days that the User needs to be notified in that a follow up is required
    dteDateDue = rst.Fields("Due Date") 'This is the date when the Follow Up is required
    dtePresent = Date

    strUser = DLookup("[UserID]", "tblSecurity", "[UserID]=strStaff") 'This is the Security Login in Table...compare this ID to StaffID to ensure proper staff is getting FollowUp Msg.

    If dtePresent = dteDateDue - intNotify Then 'The calculation for Pop Up Message

    SHOULD BE AN "AND" HERE SOMEWHERE TO VERIFY USER

    MsgBox strProjcet & vbCrLf & "is" & "now" & dteDateDue


    End If

    Set rst = Nothing
    Set dbs = Nothing

    End Function

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling All Pro's (2000)

    Can there be more than one project due for a given user? If so, do you want the user to be presented with multipe message boxes, or with one message box listing all reminders? If one message box with multiple reminders gets too crowded, you could display a popup form instead.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling All Pro's (2000)

    Hans,

    Yes there can be more than one project due for a given person and yes I want a msg w/all projects listed (was thinking...more in the line of "USERID please view the following report for project follow ups" and when user clicks OK a report opens).

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling All Pro's (2000)

    Sorry to keep on asking questions. Dou you want to open one report for all projects when the user clicks OK, or should the user select a project from a list, then open a report for that specific project?

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling All Pro's (2000)

    Hans,

    The report that opens will show all projects that require a follow up.

    Also....as an added thought in the last few mins...can an email be sent to the respective Staff as well? If this is too complicated....not necessary!

    Thanks...
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling All Pro's (2000)

    Why send an e-mail when the user opens the database? Seems superfluous. Moreover, you'd have to get around the draconic security update for Outlook, if you're using that.

    I don't understand the role of the login table. How do you know which record is for the current user?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling All Pro's (2000)

    Hans,

    In tblProjects is a field "UserID". On the form when the user selects his/her name from a combo box (cboStaff) I have an AfterUpdate to Update this field to = the UserID. Because both UserID and staffID are the same.

    The Login table only stores which user and what time he/she logged in and out. In my code, or my feeble attempt anyway, I'm using the login table to verify who is logging in so that ONLY the respective people get their Follow Up Messages. i.e. if LR logs in but don't have any follow ups, the she doesn't get the message, but if EM logs in and he has follow ups, then he should get the message. Better said I guess is I don't want others seeing messages for those who have outstanding work.

    I don't think omitting tblSecurity is an option here. There has to be a means of verifying who is logging in.

    Hope this answered your question.

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling All Pro's (2000)

    Since multiple users can log in to the database, looking in the login table won't help to identify the user. You will have to call the code from the same form where the user "logs in" (selects a name from the combo box).

    I have attached some air code that is intended to run from a command button on that form. you will have to adapt it in places.
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling All Pro's (2000)

    Hans,

    I've been working on this for sometime now....looked it over and made the changes where needed....and from copy & paste to this point..I keep getting "item not in this collection" msg.

    This usually means a reference in the code isn't on the form....but I've double checked for sometime now....can't find anything amiss....and it's not highlighting any line when it errors out....any suggestions?

    Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling All Pro's (2000)

    It probably means that one of the field names is not correct, either the Me.UserID, or one of the field names of the recordset.

    To test, temporarily comment out the line On Error GoTo ErrHandler. The offending line will then be highlighted.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling All Pro's (2000)

    Thanks Hans,

    Before I read your post, I found the error....thanks tho....AND AS ALWAYS...WORKS PERFECTLY!!!!!!!!! SUPERB!!!!!!!!!!!!!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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