Office Assistant pop up

oxicottin

oxicottin

Learning by pecking away....
Local time
Today, 13:58
Joined
Jun 26, 2007
Messages
889
Message box help

Hello, I found the neat demo that uses the office assistant for your pop up errors boxes ect. I wanted to implement it into my DB but I can’t figure out how to use it with my date validation for my reports. I have an included an example. In the example there is a button (Office Assistant with OK button) if clicked it brings up the office assistant with an Ok button. I want to implement this into my validating of the date using the "Open Form" button in my example. Thanks!
 

Attachments

Last edited:
Just change the line:

MsgBox strMsg, vbOKOnly, "Date Entry Error"

To this line:

MsgOK strMsg, "Date Entry Error"

.
 
CyberLynx, I tried just replacing that line and the other two and all the correct Msg boxes come up but the form still opens after each Msg box is closed. Thanks!

Code: 
Private Function ValidDates() As Boolean

    Dim strMsg As String
    
    ValidDates = True
    
    If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
        If Me.txtStartDate > Me.txtEndDate Then
        [COLOR="SeaGreen"]'strMsg = "Start Date must be EQUAL TO or LESS THAN End Date."[/COLOR]           
        MsgOK "Start Date must be EQUAL TO or LESS THAN End Date."
        End If
    Else
       [COLOR="seagreen"] 'strMsg = "Both Start and End Dates are Required for the Reports."[/COLOR]        
        MsgOK "Both Start and End Dates are Required for the Reports.", "Required"
    End If
    
    If Len(strMsg) Then
        [COLOR="seagreen"]'msgbox strMsg, vbOKOnly, "Date Entry Error"[/COLOR]        
        MsgOK strMsg, "Date Entry Error"
        ValidDates = False
    End If
        
End Function
 
Not on my computer system it doesn't

First off, get rid of the unnecessary MsgOK calls. They're not needed. Establish the Return Value of this function (True or False) at the end of the Function one all the conditional processing is done. Replace the ValidDates() function with what is below.

Code: 
Private Function ValidDates() As Boolean
    Dim strMsg As String
    
    If IsDate(Me.txtStartDate) = True And IsDate(Me.txtEndDate) = True Then
        If Me.txtStartDate > Me.txtEndDate Then
           strMsg = "Start Date must be EQUAL TO or LESS THAN End Date."           
        End If
    Else
        strMsg = "Both the Start Date and End Date are required to display a Reports."        
    End If
    
    If Len(strMsg) = 0 Then
        Validates = True
    Else 
        MsgOK strMsg, "Date Entry Error"
        ValidDates = False
    End If
End Function

I have also found from time to time (I don't know why) that Functions such as IsNull(), IsDate(), etc. provide better success if the literal True/False is actually queried after them rather than relying on the IF/THEN to determine it. I have had problems in the past with this sort of thing. Most times it works but every once in a while, it doesn't, so now I don't even take the chance. Its like:

If Len(strMsg) Then

Not this guy...I would use:

If Len(strMsg) > 0 Then

or

If Len(strMsg) = 0 Then

So with that in mind, try changing the Code under the Command Button:

Code: 
If ValidDates() Then
   stDocName = "Form1"
   DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

To this

Code: 
If ValidDates()[B] = True [/B]Then
   stDocName = "Form1"
   DoCmd.OpenForm stDocName, , , stLinkCriteria
End If


.
 
CyberLynx, The whole point was to use the MsgOK so it brings up the office assistant instead of the boring MS msg box. The MsgOK uses the module "Main" to bring up the assistant. I havent tried your code but I am right now and i will post back in a few minutes... Thanks!
 
CyberLynx, sorry im new to this :D It is bringing up the office assistant. But Now when I get dates in that are correct and click the button the form wont open. I have done everything exactly as you have show :confused:

Thanks!
 

Attachments

I'm Sorry....My mistake.

The reason why it doesn't open the Form is because there is a Variable in the ValidDates Function that is not Declared. That variable is Validates. It Should be the name of the Function which is ValidDates. In other words, Validates = True should be ValidDates = True

This is all the more reason why you should have the Option Explicit statement at the Top of the Module (should be in every module). When in a Module, this statement forces you to Declare ALL variables.

You should do that now. Add the two words Option Explicit to the top of the Form's code Module directly under the Option Compare Database statement.

Now, if you go into the Debug menu and select Compile MESSAGEBOX, you will get a Compile Error box that indicates that a variable was not defined. The variable in question will be highlighted. This means that yet another Variable was not Declared. You must declare that variable as well and the variable I'm talking about is the strLinkCriteria String variable located within the OnClick event of the Open button (Command14).

Add this line under the Dim stDocName As String line: Dim stLinkCriteria As String

So what you will have is:

Dim stDocName As String
Dim stLinkCriteria As String

Both of these are Variable Declarations and they are both Declared as String Data Types. This means that they can store both Text and Numbers.

Your code should work just fine after the above changes.

.
 
CyberLynx, I just found that "ValidDates" and tried it and it worked then I came to post what I had found and you beat me to it :p. Works like a charm now and I inserted your other sugestions as well. I need help with one more msg box that im using and its a yes no message box but instead of using the
MsgOK like we did before I now have to use MsgYN to get the yes no office assistant selection. Here is what I have or am using and it works with the basic Ms Msg box. What do I need to change to use the MsgYN? Thanks!

Code: 
Private Sub txtEmployeeTime_AfterUpdate()
Dim strMsg As String

strMsg = "Was overtime worked?  If so, " & _
               "OT Status box will be checked"
  If Me.txtEmployeeTime <> 8 Then
    If msgbox(strMsg, vbYesNo) = vbYes Then
        Me.chkStatus = True
        Me.txtDTRegular = Round((txtAccTimeWorked) - (txtMinutes / 60), 2)
        Else

                End If
            End If

End Sub
 
Use it the same way you did with the MsgOK function. The only difference with the MSgYN function and the MsgOK function is that the MsgYN function will return a one of two values, either vbYes (Integer 6) or vbNo (Integer 7).

So, in other words:
Code: 
If MsgYN("Well...what is it, Yes or No", "My Message Title") = vbYes Then
   MsgOK "HEY...You selected YES!", "Right On..."
Else
   MsgOK "Hey...You selected NO!", "Darn You..."
End If

Notice how the parameters for the MsgYN function are enclosed within parentheses (brackets). If you want the returned value from the function then you need to do this. If you deleted the brackets within the above code you would get an Error. Yet, if you run the MsgYN function on a line of its own, you wont have a problem. Try it, experiment with it.

Any function that is to return a value that will be utilized must have its' parameters enclosed in parentheses.

With all this in mind now, to work in your code, just replace the MsgBox function name with MsgYN and where the vbYesNo is located, replace it with a title you might want to use in your Assistant message box. You don't need to use the vbYesNo because that is what the MsgYN function takes care of, a selection of either Yes or No. You don't need to supply a Title to the MsgYN function either, that is optional.

There are two parameters for the MsgYN function. The first parameter is the display string which is a requirement. The second parameter is the message Title and it is optional. Parameters are always separated by a comma. For exmple:

If MsgYN(strMsg, "My Title") = vbYes Then .......

or this could very well be:

If MsgYN(strMsg) = vbYes Then .......

which will not display a Title in the Assistant message box.

.
 
CyberLynx, I thank you so much!!!! your help has been greatly appreciated :D You have taken the time to explain every detail and I thank you for that! Everything works great and below is what I came up with from your instructions.....

Code: 
Private Sub txtEmployeeTime_AfterUpdate()
Dim strMsg As String

strMsg = "Was overtime worked?  If so, select Yes and the OT Status box will be checked"
 
 If Me.txtEmployeeTime <> 8 Then
    If MsgYN(strMsg) = vbYes Then
        Me.chkStatus = True
        Me.txtDTRegular = Round((txtAccTimeWorked) - (txtMinutes / 60), 2)
        Else
      MsgOK "You selected NO! Which means eather the operator was moved or they left early."
    End If
  End If

End Sub
 
Looks good except one thing....

If Me.txtEmployeeTime <> 8 Then ....

This is not specific enough. This is the same as saying:

If the employee's time is Less than or Greater than 8 hours then ....
Overtime is based on hours worked over 8 hours (normally - depending upon the agreement).

It's the same as saying, anything other than 8 then....

What if I entered 6 hours. The code within the If/Then statement would fire.


If Me.txtEmployeeTime > 8 Then ....

would be better.

.
 
Well there is a reason for that but its alot of explaining :p This is how I need it set up for the way data is collected for OT. I am however working on another criteria or msg box using the OK. I want to see if I can get it on my own first using the infomation you provided before I post the code. I will let you know tomorrow eve....

Thanks,
Chad
 
well I got my last message box to work but its not the way I would like it. I wanted each text box cbowidth, cbogauge and txtTotalLBS to have its own error but this wors also. Here is what I came up with. Thanks!

Code: 
Private Sub cmdPreviewRpt_LBStoMLFConversion_Click()
On Error GoTo Err_cmdPreviewRpt_LBStoMLFConversion_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
   
    
        If IsNull(Me.cboWidth) Or IsNull(Me.cboGauge) Or IsNull(Me.txtTotalLBS) Then
          MsgOK "You must make sure all three criteria's are filled in before proceeding.", "Selection Error"
            
      Exit Sub
      
  Else
    stDocName = "rptLBStoMLFConversion"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdPreviewRpt_LBStoMLFConversion_Cl:
    Exit Sub

Err_cmdPreviewRpt_LBStoMLFConversion_Click:
    msgbox Err.Description
    Resume Exit_cmdPreviewRpt_LBStoMLFConversion_Cl
  End If
End Sub
 
Well....if you really want to provide conditional error messaging for each of the three Controls on Form then you could have merely done three individual If/Then statements or utilized the ElseIf statement. Like this:

Code: 
Private Sub cmdPreviewRpt_LBStoMLFConversion_Click()
   On Error GoTo Err_cmdPreviewRpt_LBStoMLFConversion_Click

   Dim stDocName As String
   Dim stLinkCriteria As String
   Dim MsgStrg As String

   If IsNull(Me.cboWidth) = True Then
      MsgStrg = "You must make sure the Width is properly supplied before proceeding."
   ElseIf IsNull(Me.cboGauge) =True Then
      MsgStrg = "You must make sure the Gauge value is properly supplied before proceeding."
   ElseIf IsNull(Me.txtTotalLBS) = True Then
      MsgStrg = "You must make sure the Total Weight is properly supplied before proceeding."
   End If

   If Len(MsgStrg) > 0 then
      MsgOK Msgtrg, "Selection Error"
  Else
    stDocName = "rptLBStoMLFConversion"
    DoCmd.OpenReport stDocName, acPreview
  End if

Exit_cmdPreviewRpt_LBStoMLFConversion_Cl:
    Exit Sub

Err_cmdPreviewRpt_LBStoMLFConversion_Click:
    msgbox Err.Description
    Resume Exit_cmdPreviewRpt_LBStoMLFConversion_Cl
End Sub

The method you have chosen to take is better in my opinion for the simple reason that it is far less code and therefore more efficient. The change I would make in your code (if you don't mind me saying) is not to enclose the Error Handling within the If/Then statement. Put it so that it is separate, like this:

Code: 
Private Sub cmdPreviewRpt_LBStoMLFConversion_Click()
   On Error GoTo Err_cmdPreviewRpt_LBStoMLFConversion_Click

   Dim stDocName As String
   Dim stLinkCriteria As String
   
   If IsNull(Me.cboWidth) Or IsNull(Me.cboGauge) Or IsNull(Me.txtTotalLBS) Then
      MsgOK "You must make sure all three criteria's are filled in before proceeding.", "Selection Error"
      Exit Sub
   Else
      stDocName = "rptLBStoMLFConversion"
      DoCmd.OpenReport stDocName, acPreview
   [B]End If[/B]

Exit_cmdPreviewRpt_LBStoMLFConversion_Cl:
    Exit Sub

Err_cmdPreviewRpt_LBStoMLFConversion_Click:
    MsgOK Err.Description
    Resume Exit_cmdPreviewRpt_LBStoMLFConversion_Cl
End Sub

You also don't need the Dim stLinkCriteria As String variable declaration since you are not using it anywhere in your procedure....or will you? Is it not the idea for checking the condition of these controls for the sole purpose of providing a WHERE clause for the OpenReport function?

If this is the case then more needs to be done with your code. You will need to generate a Where Clause based on the input placed into the three controls. Perhaps like this:

Code: 
Private Sub cmdPreviewRpt_LBStoMLFConversion_Click()
   On Error GoTo Err_cmdPreviewRpt_LBStoMLFConversion_Click

   Dim stDocName As String
   Dim stLinkCriteria As String
    
   If IsNull(Me.cboWidth) Or IsNull(Me.cboGauge) Or IsNull(Me.txtTotalLBS) Then
      MsgOK "You must make sure all three criteria's are filled in before proceeding.", "Selection Error"
      Exit Sub
   Else
      stLinkCriteria = "[[I][COLOR="Red"]WidthTableFldName[/COLOR][/I]]=" & Me.cboWidth & " AND [[COLOR="Red"][I]GaugeTableFldName[/I][/COLOR]]=" & Me.cboGauge & " AND [[COLOR="Red"][I]TotalLBSTableFldName[/I][/COLOR]]=" & Me.txtTotalLBS
      stDocName = "rptLBStoMLFConversion"
      DoCmd.OpenReport stDocName, acPreview, ,stLinkCriteria
   [B]End If[/B]

Exit_cmdPreviewRpt_LBStoMLFConversion_Cl:
    Exit Sub

Err_cmdPreviewRpt_LBStoMLFConversion_Click:
    MsgOK Err.Description
    Resume Exit_cmdPreviewRpt_LBStoMLFConversion_Cl
End Sub

The stLinkCriteria shown within the code above is assuming that the table fields in question are of a numerical Data Type like Integer, Long Integer, Single, Double, etc..

If the table fields are of the Text Data Type then the stLinkCriteria string needs to change a little. We'll need to add apostrophes to denote that a String is in use for comparison. The stLinkCriteria string would therefore look like this:

Code: 
stLinkCriteria = "[[I][COLOR="Red"]WidthTableFldName[/COLOR][/I]]=[B][COLOR="Red"]'[/COLOR][/B]" & Me.cboWidth & "[B][COLOR="Red"]'[/COLOR][/B] AND [[COLOR="Red"][I]GaugeTableFldName[/I][/COLOR]]=[B][COLOR="Red"]'[/COLOR][/B]" & Me.cboGauge & "[B][COLOR="Red"]'[/COLOR][/B] AND [[COLOR="Red"][I]TotalLBSTableFldName[/I][/COLOR]]=[B][COLOR="Red"]'[/COLOR][/B]" & Me.txtTotalLBS & "[COLOR="Red"][B]'[/B][/COLOR]"

Notice the apostrophes in bold red? Also...the items in the above Where Clause that are shown in Italic Red need to be replaced with the proper Table Field names.

.
 
Wow! thanks for all the info.... I think im going with my origional thought because its like you said basic and to the point! I did however change it a bit to this:
Code: 
If IsNull(Me.cboWidth) Or IsNull(Me.cboGauge) Or IsNull(Me.txtTotalLBS) Then
          MsgOK "You must make sure all three criteria's" & Chr(13) & _
          "are filled in before proceeding." & Chr(13) & _
          "    - Enter a Total LBS" & Chr(13) & _
          "    - Select a Steel Gauge" & Chr(13) & _
          "    - Select a Steel Width", "Selection/Entry Error"
            
      Exit Sub

Looks and works well. One last question? Is there a way I can use the office assistant with a basic password protected button that uses an input? I have the code below thats behind a button on my form. I have also included the Office Assistant DB that I found on the net. It includes lots more examples of what can be done.

Code: 
Private Sub cmdOption5_Click()
On Error GoTo Err_cmdOption5_Click

    Dim stDocName As String
    
    If InputBox("Enter the password", "Enter Password") = "soggycashew" Then
    stDocName = "frmUpdateEmployees"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Me.Visible = False 'Hides the switchboard
    Else
        msgbox "Incorrect password", vbOKOnly, "Beat It"
    End If

Exit_cmdOption5_Click:
    Exit Sub

Err_cmdOption5_Click:
    msgbox Err.Description
    Resume Exit_cmdOption5_Click
End Sub
 

Attachments

CyberLynx, I have a problem :( If I have the DB on my desktop everything works fine but as soon as I open and try one of the msg boxes out I then get an error. Here is a .jpg of the error msg.
 

Attachments

  • error.JPG
    error.JPG
    12.9 KB · Views: 119
With regards to using the Assistant for Input (Passwords, etc), it can be done....I just can't remember. It may not be worth the effort.

With regards to you last post....the Error is because the the computer you are running the application on does not have the Microsoft Office 10.0 Object Library (mso.dll) referenced. This is done through the VBA IDE. Select the Tools menu item then select References.... If the reference is not available to the other compuer then you will need to upgrade it.

Keep in mind...The MS-Office Assistant is a neat little toy but not all systems can use it. I'm not even sure if Office 2007 supports it anymore (to lazy to check). Bottom line...you may be working vigorously on something that is either now obsolete or may very well be in the very near future.

.
 
CyberLynx, thanks! I decided to make my own and came up with this but I have it all boogered up! I cant get it to work right.... I just want it to read the word "soggycashew" if it was typed in the txtpassword box correct it would open the frmUpdateEmployees and if not it would show the image behind that one for a few seconds and close that form frmPasswordRequired.

Im lost...

Thanks,
Chad
 

Attachments

Focus needed to be applied to the control. works now. I highly recommend you read the comments in the code.

.
 

Attachments

CyberLynx, Thank You It works great! But, the code is way over my head so to be honist I dont really understand whats going on. Im just starting out and self taught as well so I learn by what I read and people like yourself explaining why and what they did. Thats why I really appreciate you taking the time to explain what your doing or what you did.... Oh, what was wrong with what I was trying to do? I had one image behind that one if you didnt see it. :p that second image was suposed to show if the password was incorect and close the form. Thanks!
 
You must log in or register to reply here.

Similar threads

D
Solved Running a public sub when form gets focus
Replies
2
Views
330
dobseh
D
Uncle Gizmo
Does Microsoft Access 2024 (Professional Plus 2024) Export Forms as Text for Re-Import?
Replies
14
Views
841
Josef P.
J
J
Creating ACCDE from ACCDB via GitHub or Azure DevOps Workflow – Any Experience?
Replies
14
Views
852
Josef P.
J
bsacheri
Access crashes when switching Form View to Datasheet View
Replies
2
Views
337
MajP
MajP
A
Tab Index of fields NOT reacting in Order
Replies
5
Views
414
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom