How would the Pro's do this? (1 Viewer)

[JJSHEP89]

I currently have a multi-user database that is used to track maintenance and data records for industrial tooling. The users of this database are not computer savvy individuals so all their actions are conducted through customized forms. In this database I have a table specific for "tooling notes" where the user can input valuable information on that specific tool for retrieval by others at a later date.

What I'm trying to add on is what i call a "critical Message" option where if that specific tooling note is deemed critical (checkbox on the entry form). It will pop up that specific message the next time someone pulls up the data for that specific tool. Now on to the specifics, The body of this message could be rather long so doing a simple message box wont do (I've already built the pop-up form, so that's not a problem) Also, if there are multiple critical messages for that tool it would need to automatically cycle through all of them before closing the pop-up form.

my approach is to write a function (so i can call it out specifically later) that takes the input from the user (Tool Number in this case) and outputs a customized SQL statement to run a query returning all the critical messages relevant for that tool number. But how do i get the results of that SQL to populate the text box on the Pop-up form? and how do i get it to cycle through each message automatically, a loop possibly?

For the Pro's out there, in your opinion is this the right method to go about this? is there a better way of doing this?

Thanks in advance!

 

[Minty]

Just my 2p worth here - In my experience any form of Pop up message form that is purely informational i.e. press return or tab or mouse click to get rid of it, very quickly becomes redundant, as users just click, click, click to get them out of the away.

Unfortunately I have yet to come across a reliable method of making people notice such things that isn't either a right royal pain in the ass for productivity, or just doesn't work because it isn't a pain in the ass.

 

[TJPoorman]

The way I would go about this would probably be something like this:
Bind the popup form to the notes table and the textbox to the message.
Then this code would go in the OnCurrent event of your tooling page:

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblNotes WHERE ToolID = " & Me.ID & " AND Critcal = TRUE", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
    DoCmd.OpenForm "popCritcalNotes", , , "ID=" & rs!id, , acDialog
    
    rs.MoveNext
Loop

This should accomplish what you are looking for.

 

[plog]

Agree with Minty about not being able to make people pay attention. Also, would like to add, that people who write and mark the notes will think everything is critical.

Where I work its a cat and mouse game--management has us build a new report for the web and everyone gets an email about its availability. Not enough people use it so management has us email the availability email once a week. Not enough users so management has us email the actual data from the report once a week. Not good enough so we email once a day. Not good enough, now we add red and yellow coloring to highlight specific rows of data needing attention. Technologically we have nothing more to offer, so there's a meeting and the users get yelled at. This cycle happens about once every other month with a new report. However, all along all the old daily report emails users get never are turned off, so they are bombarded with multiple daily red and yellow reports which they stop paying attention to.

From a technological point of view, TJ's answer is the best way to accomplish what you want, I just don't think what you want to accomplish is feasible given human nature.

 

[Minty]

As a further jaded cynical view but with further possible assistance, consider just displaying the notes (in reverse order of their entry, and probable importance) on a sub form where they might be referred to in everyday use.

I afraid I also agree with Plog's appraisal, that the Critical Note flag probably won't help. We (after much resistance) instigated a "Hot Site" process that flagged up customers that needed special attention.

It gets used very rarely, and as far as I can has no impact on our response to those customers. If we're going to apply murphy's law we will anyway, regardless of them being "Special" or not. We'll still **** it up

 

[JJSHEP89]

i couldn't agree more about the fact that there is no way to force someone to read the message. we've been running this system for about 2 years now and only 4 messages have been marked critical so far so im not too concerned about bombardment. But the best we can do is offer them the information, there's no way of forcing anyone to read anything but as long as we do our due diligence to provide them the information, the rest is up to them. Just to say a bit about the users as well, these guys are very detail oriented and good at their jobs, they aren't tech savvy but that doesn't mean that they aren't intelligent. if they were instructed to pay attention to the messges 95% of them probably would. my only concern is when one tool has multiple messages, they may read the first and click through the rest not realizing they're different.

 

[JJSHEP89]

consider just displaying the notes (in reverse order of their entry, and probable importance) on a sub form where they might be referred to in everyday use.

this is what we have now, and it's 'ok'. Our quality department is looking to use this system as a way to convey dimensional information about the tool to the shop as well. so much so that they provided it as a solution to a communication problem found in a recent audit, so now its a must have.

 

[static]

"The body of this message could be rather long"

Short descriptive headers that lead to the detailed text a la email would be more likely to get clicks.


"there is no way to force someone to read the message. we've been running this system for about 2 years now and only 4 messages have been marked critical"

People might be reading the messages but not marking them as critical because they don't see it as their responsibility to decide if it's critical or not.

Maybe a less committal 'was this information useful to you?' button and order messages by the number of clicks.

View attachment upvote.accdb

 

[JJSHEP89]

The way I would go about this would probably be something like this:
Bind the popup form to the notes table and the textbox to the message.
Then this code would go in the OnCurrent event of your tooling page:

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblNotes WHERE ToolID = " & Me.ID & " AND Critcal = TRUE", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
    DoCmd.OpenForm "popCritcalNotes", , , "ID=" & rs!id, , acDialog
    
    rs.MoveNext
Loop

This should accomplish what you are looking for.

just a few questions on this, to help me learn a bit about VBA, I understand the logic but what is the recordset dimension? i've seen alot of people reference it but when in the VBA editor "recordset" is not an available dimension in the autofill list..? I know Recordset is a property within the form as well. Also "CurrentDb" is one i see frequently but i dont quite understand its use either... can someone enlighten me?

 

[TJPoorman]

A recordset is just that, a set of records. You specify what records with the SELECT statement just like a form. The recordset lives in memory while the code is running.

CurrentDB is just a reference to the connection.

So the code is saying in the database that I'm in, open a set of records with this SELECT statement.

 

[JJSHEP89]

Ok so i read up a bit further on the recordset object, from the link below, what it does and how it functions but when i step through my code it hangs up and gives me a compile error on the dimension statement. was the recordset object removed in later verisons of access? the article itself is archived from 2007. Im running access 2013 here.

https://msdn.microsoft.com/en-us/library/bb177501(v=office.12).aspx

here's my code, essentially the same as above just with different object names. the varToolNumber is a global variable set when the user opens the main form.

Private Sub Form_Current()
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ToolingNotes WHERE TN_ToolNumber = " & varToolNumber & " AND TN_Critical = TRUE", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
    DoCmd.OpenForm "CriticalMessage", , , "TN_ToolNumber =" & varToolNumber, , acDialog
    
    rs.MoveNext
End Sub

 

[TJPoorman]

What is the compile error?

 

[JJSHEP89]

What is the compile error?

Compile error:
User-defined type not defined

 

[MarkK]

You need to set a reference to the Object Library that provides the DAO.Recordset class. In Access 2013 I would guess that this will be called...

Microsoft Office 15.0 Access database engine Object Library.

Then, to open multiple instances of the same form, you need to look into using what are called "non-default instance"(s).

 

[JJSHEP89]

You need to set a reference to the Object Library that provides the DAO.Recordset class. In Access 2013 I would guess that this will be called...

Microsoft Office 15.0 Access database engine Object Library.

Then, to open multiple instances of the same form, you need to look into using what are called "non-default instance"(s).

i've been reading up on the DAO reference, its not something im familiar with, but i see references to it quite frequently here so it must be something important.

 

[TJPoorman]

Then, to open multiple instances of the same form, you need to look into using what are called "non-default instance"(s).

This code will not open multiple instances as called by the acDialog option.

 

[liddlem]

I may be a bit late to the party here, but why not output the message to a report so that the user can print it our if they wish? You could add photos as well if required.

 

[The_Doc_Man]

Whenever you get a "user defined type not available" you didn't define something.

So... what ways are there to define something?

EITHER you define public constants, public subroutines, or public functions in your general modules
OR from the VBA code page you do a Tools >> References and the select a library that contains public constants, public subroutines, or public functions in the library file.

In the specific case you showed, it was probably "Recordset" that wasn't defined because there are TWO common types of them, a DAO.Recordset and an ADO.Recordset, and they are not the same. You would have had to make a reference to the DAO library and then declared a DAO.Recordset object. (Or, if you really needed it, an ADO library reference and an ADO.Recordset object variable.)

Now, as to the OTHER donneybrook mentioned here, getting users to click through a bunch of notices ain't the solution. Instead, put a couple of unbound text boxex on the form and put the count and highest severity in those boxes. Then have a button that opens the list as a sub-form or something like that. If EVERYBODY has to click through the pile of poop, nobody will pay any attention to it. But if you give them the option and a visual (but not obnoxious) indication, someone to whom that message means something will look at it.

Then, if people are not using the product according to management's wishes, have the management team do some anatomical adjustment with a posthole auger. You provide the techie solution but if there is a policy that says "Thou shalt pay attention to this dreck when you are working on this machine" - then they have to answer to the boss. And if they screw something up, they learn quickly.

 

[liddlem]

I did reply earlier, but dont know what happend to my post.
I would create a report and display that instead. (You could add pictures too)

 

[JJSHEP89]

You need to set a reference to the Object Library that provides the DAO.Recordset class. In Access 2013 I would guess that this will be called...

Microsoft Office 15.0 Access database engine Object Library.

Then, to open multiple instances of the same form, you need to look into using what are called "non-default instance"(s).

yes this reference in there. i've added the DAO. reference to the code as well, still same error.


So here's what I've tried so far.... I've removed the reference to my global variable and replaced with a local and input box, to eliminate that as the issue, still the same error

Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varTN As Variant

varTN = InputBox("Enter a Tool Number or Part Number.")

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ToolingNotes WHERE TN_ToolNumber = " & varTN & " AND TN_Critical = TRUE", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
    DoCmd.OpenForm "CriticalMessage", , , "TN_ToolNumber =" & varTN, , acDialog
    
    rs.MoveNext
    db.Close
    Set db = Nothing
    
End Sub

Is there a way to do this without the DAO library?, just with using the MAO library, its what im more familiar with.