Warn about conflicting entries and offer choice to edit conflicting entry or current (2 Viewers)

[guinness]

Thanks vbainet. Why can't you read the code? When I go to set the property for the before update event it creates the line

Private Sub Form_BeforeUpdate(Cancel As Integer)

The person who wrote the air code that I am adapting had the first line as

 Function ISITOK(Trainer_Name, Start_Date, NewRec)

So how could I add the function to the before update event?

 

[vbaInet]

It's not a legible block of code because it has no indents. Without this you can't tell the end of a block or even the start.

Try this untested code:

    Dim intCheck As Integer
    Dim strDuration As String
    Dim strCondition As String
    Dim strMessage As String
    Dim intResponse As Byte
    
    Const BTWN_DATES As String = " BETWEEN [[COLOR="blue"]Start_Date[/COLOR]] AND [[COLOR="blue"]End_Date[/COLOR]]"
    
    If Me.Duration = "All Day" Then
        strDuration = " AND [Duration] Not Is Null"
    Else
        strDuration = " AND [Duration] IN ('All Day','" & Me.Duration & "')"
    End If
    
    strCondition = "(" & Me.Start_Date & BTWN_DATES & strDuration & ")" & _
                   " OR " & _
                   "(" & Me.End_Date & BTWN_DATES & strDuration & ")"
    
    intCheck = DCount("*", "[[COLOR="blue"]TableName[/COLOR]]", strCondition)
    
    If intCheck > 0 Then
        Cancel = True
        
        strMessage = "This event clashes with" & IIf(intCheck > 1, " an ", " ") & _
                              "existing event" & IIf(intCheck > 1, "s", "") & _
                     vbNewLine & vbNewLine & _
                     "Do you wish to view and edit the event" & IIf(intCheck > 1, "s?", "?")
        
        intResponse = MsgBox(strMessage, vbYesNo + vbQuestion, "Event clash")
        
        If intResponse = vbYes Then
            DoCmd.OpenForm "[COLOR="Blue"]FormName[/COLOR]", , , strCondition
        End If
    End If

Note that [Start_Date] and [End_Date] are the field names in the table. The other ones with the "Me." reference is the textbox name.

Amend the bits in blue and use the code in the Before Update event of the form.

 

[guinness]

Thanks vbaInet

As soon as I enter a start date the debugger starts. I get an error message see picture attached.

It then highlights this line of code
[code
intCheck = DCount("*", "[Resourcing]", strCondition)
[/code]

:banghead:

 

[vbaInet]

It works with both Start and End date. Both must be filled in.

Plus replace the strCondition line with the following:

    strCondition = "(#" & Me.Start_Date & "#" & BTWN_DATES & strDuration & ")" & _
                   " OR " & _
                   "(#" & Me.End_Date & "#" & BTWN_DATES & strDuration & ")"

 

[guinness]

As always I really appreciate your time on this

The issue I have is that the error occurs as soon as I enter a start date so I don't get the chance to enter an end date. Perhaps the screenshot will help. This is the form that the user is inputting to.

 

[vbaInet]

There's no way the Before Update event of the form will fire before unless you're:

1. Moving to another record
2. Hit a save button
3. Forced it to fire in code

You need to check where it's being called.

 

[vbaInet]

And when you copy and paste code, you must always Debug > Compile. Look at the menus for this.

 

[guinness]

Have compiled but still get the same. As soon as I tab out of start date I get the error and when I debug it goes to the following line of code:

intCheck = DCount("*", "[Resourcing]", strCondition)

I would send a copy of the db but it's 3.5mb when zipped

 

[vbaInet]

The line of code it errors at this point doesn't matter. What matters is the fact that the form's Before Update event is firing when it shouldn't.

 

[guinness]

See if this works

 

[guinness]

The form in question is called Resourcing

 

[vbaInet]

Look in the After Update event of the Start Date textbox.

 

[guinness]

No idea what that was doing in there.

However the syntax error now appears after I click 'create record' having created a duplicate entry. The first pop up tells me I'm about to append rows (which I expect) then the error pops up

 

[vbaInet]

If you're updating records through another means, the form's Before Update or After Update events will not fire.

By the way, change this line:

strDuration = " AND Not [Duration] Is Null"

 

[guinness]

Eureka!!!!!

Well almost. Now I get the popup but it won't let me program anyone for 02/01/2014 saying everything is a duplicate entry. Do I need some sort of code to let it know that th duplicate combination should be fired when Trainer_Name and Start_Date and End_Date result in duplicates?

 

[guinness]

Actually the way the records exist in the Resources table It would need to check Trainer_Name, Start_Date and Duration for each date within the range.

 

[vbaInet]

Actually the way the records exist in the Resources table It would need to check Trainer_Name, Start_Date and Duration for each date within the range.

You forgot to include End_Date.

strCondition is the line of code that needs to be amended to include that extra condition.
So Debug.Print strCondition, run a test, examine the output of strCondition in the Immediate Window to see which part that needs amending.

 

[guinness]

Thanks vbaInet....I think

End_Date actually isn't that important. When the user enters a date range in the resourcing form it actually creates a number of records with incrementing start dates but the end date is blank. Basically it creates a record for every single day rather than a date range. This allows things to change at a later date as you just amend the record for that particular day.

As to the other part of your answer I think you are forgetting how little I actually know about code. Any chance you could spell it out for an imbecile?

 

[vbaInet]

End_Date actually isn't that important. When the user enters a date range in the resourcing form it actually creates a number of records with incrementing start dates but the end date is blank. Basically it creates a record for every single day rather than a date range.

It creates a number of records with incrementing start dates up until the end date correct? The number of records it creates relates to the difference in days between the start and end dates. Isn't this correct?

 

[guinness]

That's exactly right.

So a conflict would be any event that had the same combination of Trainer_Name, Start_Date and Duration within the range of start and end dates entred.

Hope that makes sense outside of my own head