Popup message in form when queries criteria is not met 1

[ArtistRhetta]

How can I create a macro that has a popup message that warns the user that the inspection date,location and inspection type he has chosen is already taken and a new inspection date can only be entered after two weeks from the date of that particular location and inspection type. Is this possible!



Always searching for answers!!!!
ArtistRhetta

 

[TheAceMan1]

How are ya ArtistRhetta . . . . .

A little hard to decide on code since we don't know your table structure. But I believe the controls are all in one table. So the following code is based on this premise and is not tested yet!

Put the code in the [blue]BeforeUpdate Event[/blue] of the form. Note: You substitute proper names in [purple]purple[/purple].

[blue]   Dim tblName As String, Criteria As String
   Dim oldDate As Date, nxtDate As Date
   Dim NL As String, DL As String
   Dim Msg As String, Style As Integer, Title As Integer
   Dim namLoc As String, namTyp As String, namDate As String
   
   tblName = "[purple][b]YourTableName[/b][/purple]"
   namLoc = "[[purple][b]LocationName[/b][/purple]]"
   namTyp = "[[purple][b]LocationTypeName[/b][/purple]]"
   namDate = "[[purple][b]LocationDateName[/b][/purple]]"
   NL = vbNewLine
   DL = NL & NL
   
   [green]'Hold latest date for location for 2week reference[/green]
   Criteria = "[" & Loc & "] = " & Me(namLoc)
   oldDate = DMax(namDate, tblName, Criteria)
   newdate = oldDate + 14 [green]'two week interim[/green]
   
   If Me(namDate) < newdate Then
      Msg = "Location Dates can only be entered in " & _
            "two week interims!" & DL & _
            "Your date " & Me(namDate) & " is less than " & _
            " the next available date of " & newdate & "!" & DL & _
            "Try another date, or you'll have to wait . . ."
      Style = vbCritical + vbOKOnly
      Title = "Two Week Interim Violation! . . ."
      MsgBox Msg, Style, Title
      Cancel = True
      Me.Undo
   Else
      Criteria = "[" & namDate & "] = #" & Me(namDate) & "# And " & _
                 "[" & namLoc & "] = " & Me(namLoc) & " And " & _
                 "[" & namTyp & "] = " & Me(namTyp)
      
      If Not IsNull(DLookup(namLoc, tblName, Criteria)) Then
         Msg = "A record with Location of " & Me(namLoc) & _
               ", Type of " & Me(namTyp) & " and " & _
               "Date of " & Me(namDate) & " already exist!" & DL & _
               "Duplicates are not allowed!" & DL & _
               "Try again with different data."
         Style = vbInformation + vbOKOnly
         Title = "Duplicate Record Error! . . . ."
         MsgBox Msg, Style, Title
         Cancel = True
         Me.Undo
      End If
   
   End If[/blue]

Thats it . . . give it a whirl and let me know . . .

See Ya! . . . . . .

 

[ArtistRhetta]

TheAceMan1,
I am doing fine. I will give this a whirl. It looks like it will work. I really appreciate your input. Can't wait to see the results.
Thanks,
ArtistRhetta