Stop Duplicates

[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.

Right.
Some anomalies explained.
DLookup(), for reasons that don't make sense to me, is able to refer to items on the current form as well as fields from the Domain (Generally table or query) specified in the second parameter. What this DLookup() is returning then, is the controls from the form.

There is no ScheduleDate field in the [Courts] table.
Before I can give you a replacement, I need you to decide exactly what needs to be done because clearly, checking against a non-existent field doesn't make any sense.
Try to explain to me in simple English, what you want to be going on here.

[atc]

Thanks for your time on this NeoPa
Ok i want to try and not allow duplicates to be entered as you know. By this a mean a list of records like this would be ok:
court 1,indoor court, 07/03/2007
court 2,indoor court,07/03/2007
court 5,outdoor court,07/03/2007
court 1,indoor court,08/03/2007
court 2,indoor court, 08/03/2007
etc

A list like this would not be ok:
court 1,indoor court,07/03/2007
court 2,indoor court,07/03/2007
court 1,indoor court,07/03/2007
court 2,indoor court,07/03/2007

So basically I want the form not to allow duplicates that have the same court and date entry.
Hope this explains what I wish to not allow. If you need any more explaination I try and give it.
Thanks again
ATC

[NeoPa]

John,
After I posted I realised that, with the db in my possession I could work this out (your requirement) by a little deductive reasoning.
When I work out what you need I'll post that up first so the thread still makes sense to others, then I'll come back with a solution which doesn't stumble over the strange DLookup() side-effects.

[NeoPa]

A point worth taking note of :
Wizards will often create objects whose names default to the same names as those items they are built from. So, for instance, a form will be built on a table and controls will be named to match the table field names that they're bound to.
This is actually cr*p!
M$ themselves, recommend naming items in a consistent and standard way. Controls on a form should be named with a three-letter code at the front to indicate which type of control it is. This is why you will see ComboBoxes starting with cbo; TextBoxes starting with txt; Labels starting with lbl; ListBoxes starting with lst; etc etc.
Forms (frm); Reports (rpt); Tables (tbl); and Queries (qry) are not excluded from this and use of this 'introducers' can make working on database so much easier to understand. equally, of course, the problem discovered here with DLookup() would never have appeared in these circumstances either.
It's worth renaming any objects created by a wizard then, to avoid subsequent problems and complications.

[atc]

Ok thanks for the advice. Sorry that it is making your task to find a solution harder.
John

[NeoPa]

Ok thanks for the advice. Sorry that it is making your task to find a solution harder.
John

I got there eventually.
Try :

Option Compare Database
Option Explicit

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

    strWhere = "([CourtID]=" & Me!CourtID & ") AND " & _
               "([ScheduleDate]=" & _
               Format(Me!ScheduleDate, "\#m/d/yyyy\#") & ")"
    If Not IsNull(DLookup("Schedule.ScheduleID", "[Schedule]", strWhere)) Then
        Cancel = True
        Call MsgBox("Your message here!")
    End If
End Sub

Private Sub Form_Current()
    On Error Resume Next
    'Will only work if running as subform of Bookings
    Me.Parent![ScheduleLinkID] = Me![ScheduleID]
End Sub

Make sure to copy and paste.

[NeoPa]

The [Schedule] table is the one that needs checking.
Although the default titles of the columns have been changed from the field names, and the value displayed for [CourtID] is actually the court's name, I was able to determine the MetaData as :
Table Name=Schedule

[i]Field; Type; IndexInfo[/i]
ScheduleID; AutoNumber; PK
ScheduleDate; Date/Time
CourtID; Number; FK

[NeoPa]

NB. If a form is running as a subform on another form, the .Parent property is set to point to the 'Parent' form.
I changed your Form_Current() procedure to use this instead of your previous code. The old code still works and this is not connected with the other changes, so you can choose to stick with your earlier version if you want to.

[atc]

Thanks for all your help NeoPa.
The problem is solved.
I'll take your advise on board.
ATC

[NeoPa]

You're welcome John.
An interesting, if complicated, problem to solve.