New record information box (Access 97)

Topic

New Reply

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.

Reply | Quote

Replies

(Edited by D Willett on 10-Oct-02 11:41. )

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

Reply | Quote

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

Reply | Quote

This may give you some idea’s
Regards
Dave

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

OK, back again, my head is getting sore and my desk just cracked under the repeated blows

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….

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?

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

Thanks again

Ian

Reply | Quote

Yoohoo

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

Reply | Quote

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

Reply | Quote

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

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

Thanks for the helpful suggestions and moral support.

Reply | Quote

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

Reply | Quote