Display message

[josros60]

Hi,

I have customer form and a Review Rate button to open RateDisplay form so when they are looking at a company they can review the rate for this company by clicking the Review Rate button.

This is the code:

Private Sub ReviewRate_Click()
' This code created in part by Command Button Wizard.
On Error GoTo Err_ReviewRate_Click

    Dim strMsg As String, strTitle As String
    Dim intStyle As Integer
    Dim strDocName As String, strLinkCriteria As String

    ' If CompanyName control is blank, display a message.
    If IsNull(Me![Combo54]) Then
        strMsg = "Select the Company/company record whose rates you want to see, then press the Review Rate button again."
        intStyle = vbOKOnly
        strTitle = "Select a Company"
        MsgBox strMsg, intStyle, strTitle
        Me![Combo54].SetFocus
    Else
    ' Otherwise, open Rate Display form, showing products for current supplier.
        strDocName = "RateDisplay"
        strLinkCriteria = "[CompanyID] = Forms![CUSTOMERS]![COMPANYID]"
        DoCmd.OpenForm strDocName, , , strLinkCriteria
        DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
      
    End If
    
Exit_ReviewProducts_Click:
    Exit Sub
    
Err_ReviewProducts_Click:
    MsgBox Err.Description
    Resume Exit_ReviewRate_Click
End Sub

how can i add to this code another line of code to display a message there's not rate for this company and don't open the RateDisplay form, when this company doesn't have rate yet?

thank you

 

[HiTechCoach]

One option is to sue the Dcount() to see if there will be ably records to display in the form.

    Else

    If DCount (<enter the correct parameters here >) > 0 then 

    ' Otherwise, open Rate Display form, showing products for current supplier.
        strDocName = "RateDisplay"
        strLinkCriteria = "[CompanyID] = Forms![CUSTOMERS]![COMPANYID]"
        DoCmd.OpenForm strDocName, , , strLinkCriteria
        DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
    Else       
      MsgBox "No Rate data to display."
    End If

    End If
</pre>

You will need to complete the parameters for the DCount(). Unfortunately there is not enough information in your post for me to be able to help you with that part.

 

[josros60]

Thank you.
What information do you need to help me in that part?

Thank you again for your help

 

[HiTechCoach]

What you will want to do with the DCount() is count the record that will be displayed when you ope then form "RateDisplay". I would use the same query that is in the record source property as the form "RateDisplay". You will use the same criteria as you will to filter the form.

Give the DCount() a try.

Let us know how it goes.

 

[josros60]

Hi,

I used the Dcount functon nothing happen, still show the rate view form even if there's not rate.

Code:

Private Sub ReviewRate_Click()
' This code created in part by Command Button Wizard.
On Error GoTo Err_ReviewProducts_Click

    Dim strMsg As String, strTitle As String
    Dim intStyle As Integer
    Dim strDocName As String, strLinkCriteria As String

    ' If CompanyName control is blank, display a message.
    If IsNull(Me![Combo54]) Then
        strMsg = "Select the Company/company record whose rates you want to see, then press the Review Rate button again."
        intStyle = vbOKOnly
        strTitle = "Select a Company"
        MsgBox strMsg, intStyle, strTitle
        Me![Combo54].SetFocus
    Else
    ' Otherwise, open Rate Display form, showing products for current supplier.
        strDocName = "RateDisplay"
        strLinkCriteria = "[CompanyID] = Forms![CUSTOMERS]![COMPANYID]"
        DoCmd.OpenForm strDocName, , , strLinkCriteria
        DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
       

[COLOR=Blue][B]If DCount("[COMPANYID]", "COMPANY") > 0 Then

[/B][B]     ' Otherwise, open Rate Display form, showing products for current supplier.
        strDocName = "RateDisplay"
        strLinkCriteria = "[CompanyID] = Forms![CUSTOMERS]![COMPANYID]"
        DoCmd.OpenForm strDocName, , , strLinkCriteria
        DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
    Else
      MsgBox "No Rate data to display."
    End If[/B][/COLOR] 

    End If

    
    
Exit_ReviewProducts_Click:
    Exit Sub
    
Err_ReviewProducts_Click:
    MsgBox Err.Description
    Resume Exit_ReviewProducts_Click
End Sub

thank you

 

[Bert1]

Thanks for the great information.its very useful to me.

 

[HiTechCoach]

Hi,

I used the Dcount functon nothing happen, still show the rate view form even if there's not rate.

Code:

Private Sub ReviewRate_Click()
' This code created in part by Command Button Wizard.
On Error GoTo Err_ReviewProducts_Click
 
    Dim strMsg As String, strTitle As String
    Dim intStyle As Integer
    Dim strDocName As String, strLinkCriteria As String
 
    ' If CompanyName control is blank, display a message.
    If IsNull(Me![Combo54]) Then
        strMsg = "Select the Company/company record whose rates you want to see, then press the Review Rate button again."
        intStyle = vbOKOnly
        strTitle = "Select a Company"
        MsgBox strMsg, intStyle, strTitle
        Me![Combo54].SetFocus
    Else
    ' Otherwise, open Rate Display form, showing products for current supplier.
        strDocName = "RateDisplay"
        strLinkCriteria = "[CompanyID] = Forms![CUSTOMERS]![COMPANYID]"
        DoCmd.OpenForm strDocName, , , strLinkCriteria
        DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
 
 
[COLOR=blue][B]If DCount("[COMPANYID]", "COMPANY") > 0 Then[/B][/COLOR]
 
[COLOR=blue][B]  ' Otherwise, open Rate Display form, showing products for current supplier.[/B][/COLOR]
[B][COLOR=blue]     strDocName = "RateDisplay"[/COLOR][/B]
[B][COLOR=blue]     strLinkCriteria = "[CompanyID] = Forms![CUSTOMERS]![COMPANYID]"[/COLOR][/B]
[B][COLOR=blue]     DoCmd.OpenForm strDocName, , , strLinkCriteria[/COLOR][/B]
[B][COLOR=blue]     DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)[/COLOR][/B]
[B][COLOR=blue] Else[/COLOR][/B]
[B][COLOR=blue]   MsgBox "No Rate data to display."[/COLOR][/B]
[B][COLOR=blue] End If[/COLOR][/B] 
 
    End If
 
 
 
Exit_ReviewProducts_Click:
    Exit Sub
 
Err_ReviewProducts_Click:
    MsgBox Err.Description
    Resume Exit_ReviewProducts_Click
End Sub

thank you

You forgot to add the third parameter to the DCount() for the criteria.

Try:

    [COLOR=blue] ' moved [B]strLinkCriteria to here so that it can be used in the DCount() and to open the form[/B][/COLOR]
[COLOR=purple] [/COLOR]
[B][COLOR=#0000ff]  [/COLOR][COLOR=black]    [COLOR=red]strLinkCriteria[/COLOR] = "[CompanyID] =" [COLOR=red]& [/COLOR] Forms![CUSTOMERS]![COMPANYID][/COLOR][/B]
[COLOR=black] [/COLOR]
[COLOR=black] [/COLOR]
[B][COLOR=black]If DCount("[COMPANYID]", "COMPANY", [COLOR=red]strLinkCriteria[/COLOR]) > 0 Then[/COLOR][/B]
[COLOR=black] [/COLOR]
[B][COLOR=black]   ' Otherwise, open Rate Display form, showing products for current supplier.[/COLOR][/B]
[B][COLOR=black]      strDocName = "RateDisplay"[/COLOR][/B]
[B][COLOR=black]      DoCmd.OpenForm strDocName, , , [/COLOR][COLOR=red]strLinkCriteria[/COLOR][/B]
[B][COLOR=black]      DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)[/COLOR][/B]
[B][COLOR=black]  Else[/COLOR][/B]
[B][COLOR=black]    MsgBox "No Rate data to display."[/COLOR][/B]
[COLOR=black][B]  End If[/B] [/COLOR]
[COLOR=black] [/COLOR]

 

[josros60]

This the new code, but still showing form blank when there's not rate but doesn't display message: "No Rate data to display"

Revised code with your additional help:

Private Sub ReviewRate_Click()
' This code created in part by Command Button Wizard.
On Error GoTo Err_ReviewRate_Click

    Dim strMsg As String, strTitle As String
    Dim intStyle As Integer
    Dim strDocName As String, strLinkCriteria As String

    ' If CompanyName control is blank, display a message.
    If IsNull(Me![Combo54]) Then
        strMsg = "Select the Company/company record whose rates you want to see, then press the Review Rate button again."
        intStyle = vbOKOnly
        strTitle = "Select a Company"
        MsgBox strMsg, intStyle, strTitle
        Me![Combo54].SetFocus
    Else
    ' Otherwise, open Rate Display form, showing products for current supplier.
        strDocName = "RateDisplay"
        DoCmd.OpenForm strDocName, , , strLinkCriteria
        DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
      
        ' moved strLinkCriteria to here so that it can be used in the DCount() and to open the form

    strLinkCriteria = "[CompanyID] = Forms![CUSTOMERS]![COMPANYID]"
   
If DCount("[COMPANYID]", "COMPANY", strLinkCriteria) > 0 Then

   ' Otherwise, open Rate Display form, showing products for current supplier.
      strDocName = "RateDisplay"
      DoCmd.OpenForm strDocName, , , strLinkCriteria
      DoCmd.MoveSize (1440 * 0.78), (1440 * 1.8)
  Else
    MsgBox "No Rate data to display."
  End If
    End If
    
Exit_ReviewRate_Click:
    Exit Sub
    
Err_ReviewRate_Click:
    MsgBox Err.Description
    Resume Exit_ReviewRate_Click
End Sub

Thank you

 

[HiTechCoach]

You are always opening the form. That is because there is a OpenForm command before the DCount(). You will need to remove the .Openform code before the DCount(). You any need it is the If statement.

DCount("[COMPANYID]", "COMPANY", strLinkCriteria)

Your DCount() is using the Comapny table. I would expect this to be the child/related table that holds the Rates data by customer.

What table holds the rate information?

Does the form RateDisplay use only the table "COMPANY" as the record source?

 

[josros60]

The rate information is held in Rate table.

I have 2 tables related:

1. Company
2. Rate

linked with CompanyID

thank you