Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New record information box (Access 97)

    Hello again people. I've put my database onto the company's network recently. One of the requests I've had is that it alerts one group of users to any new records in a certain table. Is there a method I can use to check this table periodically for new records and then have a form pop up to let them know a new request has been made?

    The database is being used as a Maintenance Management system and I want to inform the Maintenance Team of new work requests. I'm not a programmer so I'd like to be able to use a query or macro if this is possible. If it's not then point me at the information I'd need to write a short piece of code, it's well past time I started to improve my ability in this area....

    My thoughts so far are this: -

    Add a new field to my table

    Create an update query to check for this field being null, if it is then open a form.

    On the form I was going to put a button that would change the field value so that it would not be pulled out in future.

    Use the 'on timer' property in the other forms to run the query.

    This will obviously take some refining, if it will work, but am I thinking along 'sensible' lines?

    Thanks in advance for the help.

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    <P ID="edit" class=small>(Edited by D Willett on 10-Oct-02 11:41. )</P>You could try adding a field [CreationDate]:Format Date-Time and set its default to =Now()
    Add another field [NewRecord] set it to yes/no

    Your query can now be based on this: [RecordID],[CreationDate],[NewRecord]

    See the attached, using the yes/no field is is possible to filter out any records already reviewed.

    Hope this helps you

    Dave

    Oops, Sorry here's the 97 version
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    This is the sort of thing I was thinking of, it's nice to know that I'm not barking up the wrong tree.

    Thanks

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    This may give you some idea's
    Regards
    Dave
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    Thanks. I've got the forms and query working fine now. All I need to do now is get the 'on timer' property to only open the form if there are records in the yes/no field that are still set to no. I thought I had an idea about this, and it didn't work out. At the moment tjhe form will open every x minutes depending on the timer setting, can some one please suggest either some SQL, or code that I can put into the 'where condition' property in the openform command I've used in the macro that runs from the timer event?

    Thanks again.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    Being honest, I don't like the timer event much at all, it becomes very annoying.
    I'd rather use an after update or load-unload or button.
    You may be-able to put some code together to accomplish what you're trying to do though.
    Maybe something like below will set you on your way. Everybody has different idea's
    Good luck
    Dave



    If IsNull([YourYesNoField]) Then
    Dim LinkCriteria As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    LinkCriteria = "[YourField] = '" & Me![YourField] ' This links your two forms by a common field, ID or something.
    stDocName = "YouFormNameToOpen"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
    ' Do Nothing
    End If
    End Sub

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: New record information box (Access 97)

    Out of curiousity. If two people are trying to see when new records were published to the particular table, and person one uses the db daily and person two weekly, how will the system know which records to post a message for.

    How do you define a new record?
    Although I am not familiar with your database, if the data is saved via a control such as save record, then at the end of the on click event you could launch the notification process.

    Otherwise, use the on timer event to run a query at a set interval and look for new records within x hours of that time. (i.e., If you run the query every 3 hours, have the query look for records timestamped from Now() less three hours.) and then send the message.

    If you are using the autonumber to increment records, you could store and check for new numbers and then re-store the current max number as the number to check against.

    Just some more ideas.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    Thanks, I'll give that code a try, the 'on timer' property I had set would have been annoying as the form opened regardless of new jobs or not.

    The database is a poor mans CMMS (computerised maintenance management system) and is used by our Maintenance Team, they cover a 24 hour period and the person seeing the job should act on it, so no need for a daily / weekly option. I have other forms for the Manager to view what has been done so the weekly option is covered there. The Maintenance Team only click the Yes/ No box when they have acted on and completed the activity requested.

    I'll let you know how I get on with the code.

    Thanks again for thr help and suggestions.

  9. #9
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    OK, back again, my head is getting sore <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> and my desk just cracked under the repeated blows <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I tried the code suggested, and didn't have any luck, possibly as the forms aren't linked, guess this should have been explained (by myself), and that I should have realised what the code was trying to do before the headache went critical.... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    So, I tried to do something along similar lines using the help in Access, this is what I've got at the minute: -

    Private Sub Form_Load()
    If IsNull([New Job?]) Then
    docmd.close acForm, "New Work Request", acSaveYes
    Else

    End If


    End Sub

    Now it compiles OK, but when used with the 'on load' property of the form, the form does not close, regardless of whether there are any records in the table or not!!!! let alone whether the yes/no property is yes or no??

    If I move the docmd.close to the else section of the code, it always closes my form, so I'm guessing that the problem may be with something else in the database, so here is more detail.

    The table I'm querying to put data into the form has an autonumber field to create an index, it is called 'job number'. Now even if the table has no records in it, there is still a check box that is a null value due to the autonumber field, could it be this that is causing my problems? <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    I'm going to try and prevent the query from picking up on the autonumber field uinless it has a value in, but I'm not sure how to go about this, think I'll try is not null first.....??

    The best thing to come out of this so far is that I have finally got over my fear of using code in the database, this has got me to the point where I've actually ordered a book to teach myself Access VBA with, the dummies book (just right for my level of (in)competence) will be with me on Tuesday <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Thanks again

    Ian

  10. #10
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    Yoohoo <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Solved it. I run a macro to open the pop up form in the 'on timer' property of those forms that are most used by the Maintenance team, I'm going to try and be brave and write a 'class module' to cover all eventualities for this. Then, in the 'on load' property I have this code: -


    Private Sub Form_Load()
    If IsNull([New Job?] = True) Then
    docmd.Beep
    Else
    If IsNull([Equipment Name] = False) Then
    docmd.close acForm, "New Work Request", acSaveYes
    End If
    End If

    End Sub

    I don't think this is the best way of doing it, but it works for now.

    Thanks for the shove towards using modules, I'm going to try and use a few more of them now <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    Well done.
    Just a pointer, which was pointed out to me this week.
    Your close statement,
    docmd.close acForm, "New Work Request", acSaveYes

    If its the form with current focus, you may be better with

    DoCmd.Close acForm, Me.Name

    If it isn't, then leave it.
    If your form went into design mode and some-one messed with it, it will save the form in its re-designed state.

    This advice was given to me, so I'll pass it on.

    Good Luck and Regards

    Dave

  12. #12
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    Thanks for the tip, the form is opened in dialogue box form with no possibility of swapping to design mode. As I haven't got any 'real' security on the forms I use this to stop the shop floor from altering things. The back end is password protected, which along with a few property settings on data input forms, stops people from accidentally deleting, or altering, information entered.

    Now I've got it working I want to improve it.... Isn't that just the way [img]/forums/images/smilies/smile.gif[/img]

    Can I use a 'class module' to open the form from any point in the database? If so, how do I start the 'class module' when the database is launched?

    As you may have noticed, I'm past the fear point on coding in Access, so now want to get some more practice in where it will be useful [img]/forums/images/smilies/smile.gif[/img]

    Thanks for the helpful suggestions and moral support.

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New record information box (Access 97)

    I suppose you could create a module.
    My VBA isn't that good and I rely on the kind members of this forum to help me out.
    But I do know a way if its any use to you, as follows.

    Create a new macro called "AutoKeys" without the quotes.
    Then create a key combination (Function Key), One that Access doesn't automatically assign, lets say F2.

    In the macro name put this {F2} with the brackets, then in the Action, OpenForm.

    This will give you the options at the bottom for the form you want to open.
    This will enable you to open the form from any where in the DB.
    Another little tip while on the subject of Autokeys. If you want to disable an Access key, Say F11 which takes the user to the DB window, Do the same {F11} only put CancelEvent in the action section.

    Hope this helps.
    Dave

Posting Permissions

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