Stop Duplicates

[NeoPa]

I need to get home now so I'll pick this up again later if that's ok. Don't lose confidence though, I was able to predict that the name was wrong wasn't I? We'll get there :)
In the mean time, get some table MetaData together as that won't hurt.
Please use this example format as I've found it to be most helpful and clear.

[b]Table Name=tblStudent[/b]
[i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[NeoPa]

I have now checked names.
Now have this code

Private Sub ScheduleDate_AfterUpdate()
   If Not IsNull(Me.CourtID) Then
	  CheckDate
   End If
End Sub

Private Sub CourtID_AfterUpdate()
   If Not IsNull(Me.ScheduleDate) Then
	  CheckDate
   End If
End Sub

Function CheckDate()
  Dim strWhere As String
  Dim varKey As Variant

  strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
             "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
                   "[Courts]", _
                   strWhere)
  If Not IsNull(varKey) Then
   Call MsgBox(strWhere & vbCrLf & varKey)
   Debug.Print strWhere & vbCrLf & varKey
   Me.ScheduleDate = Null
  End If

End Function

With this code in place (entering the data courtID of 1 and a date of 01/03/2007 - not a duplicate) I get the same message as before but when I click ok it keeps the courtID of the new record, deletes the schedule date and moves to courtID of a new record below.
I also get the same result if the data is a duplicate.

Shall i revert back to your code, keep this code or start over.

PS Pitches got changed to courts due to working on two systems that are the same just have differnt resources

John

Yes, go back to the version I gave you (The PitchID can be changed to CourtID though) and continue from there (I need to know exactly what happens).
Your changes are intelligent, but if I have to work out what the code that I designed is doing when all I have to go on is your feedback, that's plenty hard enough without having to reverse engineer your changes and interpret them as well. My code is designed to give me the information I need to determine what is going on. If you think about it, anything different from that is going to make the job more complicated.

Anything you've done needn't be discarded, just make a copy. Most of what I've suggested is not for the final version, it's simply a debug rig to give me information you'd probably tell me yourself if you knew well enough what to look for.

When you do get to that stage, your design is good, but in case the operator changes a field by clearing it, you should check both fields on the update of either. This is a common concept and, to handle multiple fields in a similar fashion, it's a good idea to create a single common procedure to do the 'work' and call this from each of the AfterUpdate event procedures. That's all for later though, we need to stay focused on finding why your checking code isn't working as expected.

[atc]

Hello again. I am now using your code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim strWhere As String
  Dim varKey As Variant

  strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
             "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
                   "[Courts]", _
                   strWhere)
  If Not IsNull(varKey) Then
    Cancel = True
    Call MsgBox(strWhere & vbCrLf & varKey)
    Debug.Print strWhere & vbCrLf & varKey
  End If
End Sub

I get the same response as before. For example when i enter court 1 for 03/03/2007 (not a duplicate). I get a message of "(CourtID=1 ) AND (ScheduleDate=# 3/3/2007) 1 - 3 Mar 2007". I then click OK but I cant click out of the cell so have to press escape and this takes the datasheet back to what it was at the start. If you need any more description i'll try and provide it.
Thanks for any help
ATC

[NeoPa]

That looks confusing John. Not what I would have expected.
The reported result either indicates a problem or is typoed, so I need you to do one more thing for me on that. Run it again, but this time follow these instructions as closely as possible in the order given :

1. Open the database and navigate to the form.
2. Using Alt-F11, open the VBA Window.
3. Using Ctrl-G switch to the Immediate Pane where the Debug.Print results are stored.
4. If it's not currently empty then, using the key sequence Ctrl-Home; Shift-Ctrl-End; Delete ensure that this window is empty.
5. Using Alt-F11, switch back to the main database window.
6. Using the form, try to enter first a record you know to exist.
7. When you've escaped out of this one, try again with a record you know not to exist.
8. Using Alt-F11; Ctrl-G switch back to the Immediate Pane in the VBA window and copy the contents from there.
9. Paste this into a new post explaining which data you used etc.

This will give me precise information which I can build on knowing every individual character is exactly as shown. That way I'll know whether it was a typo or something more unusual.
As a separate, concurrent, process (we have to be careful here but as long as we stay synchronised we should be ok), please let me know :

1. Has a copy of this particular database found it's way to Mary at all (at all)?
2. What's the name of the form we're working on?

[atc]

NeoPa
Here are the results from the immediate section:

(CourtID=1) AND (ScheduleDate=# 2/2/2007#)
1 - 2 Feb 2007
(CourtID=4) AND (ScheduleDate=# 2/2/2007#)
4 - 2 Feb 2007

First I tried court 1 for 02/02/2007 (this is a duplicate). Then I tried court 4 for 02/02/2007 (not a duplicate).

Answer to A: yes mary was sent a copy by e-mail
B: The main form is called Bookings. It has two subforms. The subform concerned here is called BookingsSubform , the other is called BookingsTimeSub form.

Hope this helps.
John

[NeoPa]

I've asked Mary for a copy of the database so I should soon be in a position to find out what we're all missing here.
One more question for now - Is this code that we're working on found in the main form called Bookings, or is it in a form called BookingsSubform which is used as a SubForm of Bookings?

[atc]

It's in the subform called BookingSubform at the moment.
John

[NeoPa]

Is this currently the MetaData for your table named Courts?

[b]Table Name=Courts[/b]
[i]Field; Type; IndexInfo[/i]
CourtID; Autonumber; PK
CourtName; String
CourtTypeID; Numeric; FK

If so, I see no [ScheduleDate] field in the table at all.
I'm afraid the version I have may be a little out-of-date too as I don't have any of the code from this thread in there at all.

[NeoPa]

If this doesn't give you a 'Road to Damascus' moment, enabling you to fix this problem, then could you send another copy of your db to Mary as before and I'll test it again. Unfortunately the older copy had none of what we've been discussing in it so I couldn't test it in any way.

[atc]

NeoPa
I have sent a another copy to mary. Try that one.
john

[NeoPa]

Thank you John. I'll have to look at it when I get home.
I presume that means you didn't get any "RtD" moment then?
Never mind - we'll get to the bottom of it shortly I'm sure.

[NeoPa]

Thank you John. I'll have to look at it when I get home.
I presume that means you didn't get any "RtD" moment then?
Never mind - we'll get to the bottom of it shortly I'm sure.

Bit of a glitch I'm afraid.
My Outlook client blocked it at source :( I'm waiting for it to be resent in Zip format (A good general idea btw as it's easier to download then anyway).

[atc]

Do you want me to resend as a zip or is mary going to resend as a zip?
john

[NeoPa]

I've left a request with Mary which should be fine but I had to leave my post (go to bed) before I received it yesterday. I'm hoping to find it there and ready when I get home today.

I would always recommend zipping in future though, with anything greater than 100KB in an e-mail.

[NeoPa]

John,

I've got the latest database and I'm looking at it now.
Can you please look carefully at post #23 and give me your comments.
I'm trying to work out what I have to do to reproduce the error you were getting. I haven't done so yet.