Results 1 to 12 of 12

Thread: popup table

  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: popup table

    Hi all,
    I am brand-new to forum and relatively new to access programming but need some help:
    What I am looking for is a way that when users log into the database that a pop-up reminder form comes up that lists information from a table as to when it is expiring. I can figure out the query to filter out the results. What I need help with is the popup window and calling it up.
    So what would happen when the user logs in is that a window would pop up (be it a form, table, or what-not) that listed all the clients that were coming up on renewal for their products and it should show expiration dates from anywhere from 3 weeks from now and less including if the expiration date has passed. Once they are updated we would change the expiration date and the next logon shoudl update the query, form, popup, etc.
    I can provide our database if needed but would need to strip out client info as we have very sensitive data in it.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How does the users log into the system? Is it through standard Access security mechanism or do you use a custom login form?

    In the second case it would be easy to have some code opening the pop up form you're talking about.

    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    No login. Standard access security. Using access 2007. Only 4 users.

  4. #4
    Join Date
    Dec 2003
    Posts
    138
    You could use a macro to open on click, or close, or however you want to do it - the form you want to open.

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    my biggest problem is when i did an append query and everytime i opened the db, it would ask me for confirmation to perform the actions. that is going to irritate the users and i cannot figure out how to just have it do it's work in the background without confirmation.
    i cannot remember why i did not use update query; i think somebody said it was better to use append query by deleting all records out of table (which is where the confirmation comes into play at) and then append from query into the table. is there a better way?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try: DoCmd.SetWarnings False

    Have a nice day!

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just don't forget to set it True again afterwards.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One thing I like to do when it comes to popup reminder type procedures is to call a function (on the OnOpen event of my "background" form since this form opens only once when the user first opens the mdb - otherwise I could have it call the function on any form's OnOpen event) and have that function return a true or false on whether it should open the reminder form or not.

    For example, I create a module and my function in that module might look like this (from memory so possible syntax errors) - and also this is ADO code versus DAO:

    Public Function UserHasReminders() as boolean
    UserHasReminders = False
    Dim rs as adodb.recordset
    Set rs = new adodb.recordset
    Dim strSQL as string
    strSQL = "MyQueryNameWhichReturnsRemindersForUser"
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    'There are no records in the reminder query for that user
    UserHasReminders = False
    else
    'There ARE records in the reminder query for that user
    UserHasReminders = True
    end if
    rs.close
    set rs = nothing

    Then in the OnOpen event of my background form, I'll have some code like this:

    If UserHasReminders = True then
    'Open my form to show the reminders here
    docmd.openform "UserReminderForm"
    end if

    I can also call this function from any place I want to (ie. a button on the main form) and simply show the "reminder" form for that user.

    To expand on how I show reminders for a specific user...

    1. I'll have a ReminderFor field in the reminder type table which holds the LoginID of the user the reminder is for (I grab the loginID automatically using the getuser routine in the codebank). I'll use that in the criteria of my query to determine if the user has reminders.
    2. I'll have some kind of date field indicating when the reminder is activated (and criteria in the query for strSQL above).
    3. I'll have a Yes/No field which allows the user to turn the reminder off (which is also put in the query criteria).
    4. An something to distinguish the type of reminder it is (and importance).

    There probably a few other things you'll have which you use in the query criteria.

    Once the query with the criteria is designed (ie. MyQueryNameWhichReturnsRemindersForUser), it's just a matter of calling the function. If 1 or more records are returned in the query, then calling the UserHasReminders function will return true. If it returns a true, I'll then open the form to show the reminders (note: I could also put the docmd.openform "MyReminderForm" in the UserHasReminders function itself but I prefer to control this in the form events instead (as I may not always want to open the same Reminder type form - for example, I may just do a msgbox "Note: You have reminders!!" versus opening a form.)

    I've also found that reminder type forms should be "captivating" if they are important items. (so I may change the background color on the form OnCurrent event depending on the importance level field value.)

    I hope that's the kind of feedback you were looking for.
    Last edited by pkstormy; 04-14-09 at 22:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding the delete or append query...

    If I want to prompt the users, I'll have code like this below. If I don't want to prompt the users, I'll then just have the code between the '**** (note: I like to have my own prompt messages to delete/append versus the standard MSAccess message):

    Private Sub cmdDelete_Click()
    on error goto doContinue
    if not isnull(me!MyPrimaryKeyField) then
    Dim QI as integer
    QI = msgbox("Are you sure you want to delete this record?",vbyesno)
    if QI = vbyes then

    '****
    Docmd.setwarnings = false
    'Code to delete below
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    'Note: or I'll call my delete code
    'Note: or I'll have my append code and change the messages appropriately - ie.
    'docmd.openquery "MyAppendQuery"

    'Don't forget this next line - very important to turn the warnings back on
    Docmd.setwarnings = true
    '****


    end if

    end if
    exit sub
    doContinue:
    'Again, note - we first want to turn the warnings back on if there was an error.
    docmd.setwarnings = true
    msgbox "ERROR: an error happened where this record could not be deleted (perhaps it's related to a record in another table or you need to first delete the sub-records within this record itself.) Contact dbAdministration for assistance."
    End Sub
    Last edited by pkstormy; 04-14-09 at 22:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Awesome posting pk
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Apr 2009
    Posts
    4
    Quote Originally Posted by pkstormy
    Regarding the delete or append query...

    If I want to prompt the users, I'll have code like this below. If I don't want to prompt the users, I'll then just have the code between the '**** (note: I like to have my own prompt messages to delete/append versus the standard MSAccess message):

    Private Sub cmdDelete_Click()
    on error goto doContinue
    if not isnull(me!MyPrimaryKeyField) then
    Dim QI as integer
    QI = msgbox("Are you sure you want to delete this record?",vbyesno)
    if QI = vbyes then

    '****
    Docmd.setwarnings = false
    'Code to delete below
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    'Note: or I'll call my delete code
    'Note: or I'll have my append code and change the messages appropriately - ie.
    'docmd.openquery "MyAppendQuery"

    'Don't forget this next line - very important to turn the warnings back on
    Docmd.setwarnings = true
    '****


    end if

    end if
    exit sub
    doContinue:
    'Again, note - we first want to turn the warnings back on if there was an error.
    docmd.setwarnings = true
    msgbox "ERROR: an error happened where this record could not be deleted (perhaps it's related to a record in another table or you need to first delete the sub-records within this record itself.) Contact dbAdministration for assistance."
    End Sub
    So I have an update query that is working but has the prompts that I do not want. So I put the code into the "open" of my form as indicated below:

    Private Sub Form_Open (Cancel As Integer)
    DoCmd.SetWarnings = False
    DoCmd.OpenQuery "Subscriptions Query"
    DoCmd.SetWarnings = True
    End Sub
    --------------------------------------------------

    I get the following error message (this is access 2007):
    compile error:
    Argument not optional

    HELP

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sounds like a problem with your query itself. Have you tested the query running it manually?

    Otherwise, does your code compile? You might have a compile error elsewhere. You should always compile your code (Debug -> Compile when in the coding screen.)

    Also, if this is against the same recordset the form is based upon, you may want to do this on another event (in the event on the form prior to this form that opens this form (before the openform command on the other form)) as this could be causing an issue with the recordset for the form trying to load at the same time you're trying to update the data for that same recordset. If this is the startup form, I'd have a "dummy" form load first which runs the code and then opens this form.

    Your code looks good otherwise and shouldn't be the cause of your error.

    (but I really, really hate query, field, table or other names with spaces in them.)

    - Thanks ST for the compliment on the previous post.
    Last edited by pkstormy; 04-20-09 at 19:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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