Display Msg box based on Query criteria

[vad77]

I have a form that displays customer sites. I have another table that tracks trouble tickets for each site and a form to display ticket history. On my main form I created a command button that opens a form and displays the ticket history for the last 12 months for the displayed site. My command button does an open form based on the site number displayed on my main form. The problem is that if a site does not have a ticket history it opens a blank Ticket History form. I want to avoid this and display a message box that displays a message "No ticket history for this site". I am thinking that I need to use an If Then statement in my command button code but I am not sure how to check the query.


This is how I have if coded now.

Private Sub cmd_TktHistoryS_Click()
If [qry_usa000_SiteTktHistory].[SiteNumber] = 0 Then
Msg = "No ticket history for this CMR#"


Else
DoCmd.OpenForm "frm_Tkt_History_Short", acNormal, , "[sitenumber]=" & [Forms]![frm_AFBSitesMain].Form![SiteNumber], , acWindowNormal
End If


End Sub

[orange]

Are your tables normalized? and do they match your business rules?

[vad77]

I have been using this forum for a few years and learned a great deal and I still consider myself a novice when it comes to VBA coding.
With the many question I have asked I never had a those two questions asked.
Please excuse my ignorance when I ask what do you mean by are my tables normalized and business rules?

[orange]

It is important to get your tables and relationships set up to meet your requirements.
see this for Normalization.



You will benefit by working through this tutorial. You have to work through it( 45-minutes-1 hour). There is a solution.

[vad77]

Thank you for this information. I understand what is meant by normalization. I do have a 1 to many relationship set up between my two tables, Site Info and Contracts, but not the new ticket table I just added. This was a new requirement that I just received after my database was built. I just need to figure out which will be the best way to add it to the relationship, it will be another 1 to many.

[orange]

Did you work through the tutorial?

[vad77]

Started it yesterday haven't finished yet.