469,353 Members | 2,292 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,353 developers. It's quick & easy.

Stop Duplicates

28
Hello.
OK im new to VBA and access and need a bit of help.
I have a subform with the fields pitchID and schedule date.
I need to stop the user from entering duplicates. By this i mean
pitchID = 1 and ScheduleDate = 26/02/07 along with
pitchID = 2 and ScheduleDate = 26/02/07 would be ok

pitchID = 1 and ScheduleDate = 26/02/07 along with
pitchID = 1 and ScheduleDate = 27/02/07 would be ok but

pitchID = 1 and ScheduleDate = 26/02/07 along with
pitchID = 1 and ScheduleDate = 26/02/07 would not be ok.

I think i will need a macro on the ScheduleDate before update procedure but am not sure.

Any ideas. Thanks
ATC
Feb 26 '07 #1
39 3266
Rabbit
12,516 Expert Mod 8TB
In the before update, either through a macro or through code, you'll need:
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "[Table Name]", "pitchID= " & Me.pitchID & " AND ScheduleDate = #" & Me.ScheduleDate & "#") > 0 Then Cancel = True
Feb 26 '07 #2
atc
28
Thanks for your reply rabbit.

I now have this code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If DCount("*", "[Pitches]", "PitchID= " & Me.PitchID & " AND ScheduleDate = #" & Me.ScheduleDate & "#") > 0 Then Cancel = True
  3. End Sub
  4.  
But it doesn't seem to work. It lets me change entries on the subform (datasheet view) but I can't click outside the box to implement the change. I have to press escape this just leaves the table datasheet as it is. Also i cant add new records. So I think the datasheet is frozen as it is.

Am i doing something wrong?
Thanks ATC
Feb 26 '07 #3
NeoPa
32,184 Expert Mod 16PB
Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If DCount("*", _
  3.           "[Pitches]", _
  4.           "(PitchID=" & Me.PitchID & ") AND " & _
  5.           "(ScheduleDate=" & Format(Me.ScheduleDate,"\#m/d/yyyy\#") & _
  6.           ")") > 0 Then _
  7.   Cancel = True
  8. End Sub
Dates have to be handled specially in SQL to guarantee they work as expected.
See (Literal DateTimes and Their Delimiters (#).) for a fuller explanation.
Feb 26 '07 #4
atc
28
Thanks for your reply.
I have tried
Expand|Select|Wrap|Line Numbers
  1. "(ScheduleDate=" & Format(Me.ScheduleDate, "\#d/m/yyyy\#") & _
  2.  
and
Expand|Select|Wrap|Line Numbers
  1. "(ScheduleDate=" & Format(Me.ScheduleDate, "\#dd/mm/yyyy\#") & _
  2.  
as i am in the UK but am still getting the same behaviour as before.
Any more ideas?
Thanks
ATC
Feb 27 '07 #5
NeoPa
32,184 Expert Mod 16PB
The ANSI standard for dates in SQL (see link posted earlier) defines the format to be M/D/Y in line with the American date system. This is not affected in any way by the Regional Settings. I am from South London myself so I do understand the British Date layout.

Can you describe the behaviour you get when running through the code I posted. What happens when (at which line in your code)?
Feb 27 '07 #6
atc
28
Sorry for my mistake. I am now using this code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If DCount("*", _
  3.           "[Pitches]", _
  4.           "(PitchID=" & Me.PitchID & ") AND " & _
  5.           "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & _
  6.           ")") > 0 Then _
  7.   Cancel = True
  8. End Sub
  9.  
I am using datasheet view for the subform.

It will let me edit records but wont let me click out of the cell to save the change if you know what i mean. (I can click different cells in that record but not other records). I have to press escape and this reverts back to the orginal record.

This also happens when I try to add a new record.

No error messages are displayed at any time.

I'll try to explain further if you need it but it is hard to put into words, sorry.
Thanks for your continued help.
ATC
Feb 27 '07 #7
NeoPa
32,184 Expert Mod 16PB
That actually makes perfect sense.
It is the routine we've just created which will have exactly that effect.
Try using this slightly modified version :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Dim strWhere As String
  3.  
  4.   strWhere = "(PitchID=" & Me.PitchID & ") AND " & _
  5.              "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  6.   If DCount("*", "[Pitches]", strWhere) > 0 Then
  7.     Cancel = True
  8.     Call MsgBox(strWhere)
  9.     Debug.Print strWhere
  10.   End If
  11. End Sub
Let us know how differently this behaves (as precisely as you can manage).
If the messagebox does come up then we'll talk about copying the message so it can be posted here.
Feb 27 '07 #8
Thanks for your help.
With this code I can not click out of cells as before. When I try to i get a message of "(PitchID=1) AND (ScheduleDate=#2/27/2007#)" for example when I enter a PitchID of 1 and a ScheduleDate of 27/02/2007.

This means I can not add or edit records as before as all I can do is press ok on the message box and then press escape to revert to the record as it was before.

Hope this makes sense ill try to explain more if you need it.
Thanks again
ATC
Feb 27 '07 #9
NeoPa
32,184 Expert Mod 16PB
That's exactly what I would expect to happen. It's following the code as entered. It does rather imply that the record already exists though. Have you checked for this?
As far as I understand it this is what you were asking for (Obviously you would want a more helpful error message).
Feb 27 '07 #10
atc
28
Hello again.
Im sorry to say that it gives this message even if there is not a record the same as it.
Any more ideas?
ATC
Feb 28 '07 #11
NeoPa
32,184 Expert Mod 16PB
Try this then and feed back the details as before :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Dim strWhere As String
  3.   Dim varKey As Variant
  4.  
  5.   strWhere = "(PitchID=" & Me.PitchID & ") AND " & _
  6.              "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  7.   varKey = DLookup("[PitchID] & Format([ScheduleDate],' \- d mmm yyyy')", _
  8.                    "[Pitches]", _
  9.                    strWhere)
  10.   If Not IsNull(varKey) Then
  11.     Cancel = True
  12.     Call MsgBox(strWhere & vbCrLf & varKey)
  13.     Debug.Print strWhere & vbCrLf & varKey
  14.   End If
  15. End Sub
Feb 28 '07 #12
atc
28
When this code is in use I cant add or edit records as before. I get this message this time "(PitchID=4) AND (ScheduleDate=#2/28/2007#) 1 - 28 Feb 2007" when trying to enter pitch 4 and a date of 28/02/07.
ATC
Feb 28 '07 #13
NeoPa
32,184 Expert Mod 16PB
In that case you're using the wrong name somewhere for the [PitchID].
Check your controls and the fields in your record source and/or table(s).
Feb 28 '07 #14
atc
28
I have now checked names.
Now have this code
Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleDate_AfterUpdate()
  2.    If Not IsNull(Me.CourtID) Then
  3.       CheckDate
  4.    End If
  5. End Sub
  6.  
  7. Private Sub CourtID_AfterUpdate()
  8.    If Not IsNull(Me.ScheduleDate) Then
  9.       CheckDate
  10.    End If
  11. End Sub
  12.  
  13. Function CheckDate()
  14.   Dim strWhere As String
  15.   Dim varKey As Variant
  16.  
  17.   strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
  18.              "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  19.   varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
  20.                    "[Courts]", _
  21.                    strWhere)
  22.   If Not IsNull(varKey) Then
  23.    Call MsgBox(strWhere & vbCrLf & varKey)
  24.    Debug.Print strWhere & vbCrLf & varKey
  25.    Me.ScheduleDate = Null
  26.   End If
  27.  
  28. End Function
  29.  
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
Mar 1 '07 #15
NeoPa
32,184 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. Field; Type; IndexInfo
  3. StudentID; Autonumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Mar 1 '07 #16
NeoPa
32,184 Expert Mod 16PB
I have now checked names.
Now have this code
Expand|Select|Wrap|Line Numbers
  1. Private Sub ScheduleDate_AfterUpdate()
  2.    If Not IsNull(Me.CourtID) Then
  3.       CheckDate
  4.    End If
  5. End Sub
  6.  
  7. Private Sub CourtID_AfterUpdate()
  8.    If Not IsNull(Me.ScheduleDate) Then
  9.       CheckDate
  10.    End If
  11. End Sub
  12.  
  13. Function CheckDate()
  14.   Dim strWhere As String
  15.   Dim varKey As Variant
  16.  
  17.   strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
  18.              "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  19.   varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
  20.                    "[Courts]", _
  21.                    strWhere)
  22.   If Not IsNull(varKey) Then
  23.    Call MsgBox(strWhere & vbCrLf & varKey)
  24.    Debug.Print strWhere & vbCrLf & varKey
  25.    Me.ScheduleDate = Null
  26.   End If
  27.  
  28. End Function
  29.  
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.
Mar 1 '07 #17
atc
28
Hello again. I am now using your code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   Dim strWhere As String
  3.   Dim varKey As Variant
  4.  
  5.   strWhere = "(CourtID=" & Me.CourtID & ") AND " & _
  6.              "(ScheduleDate=" & Format(Me.ScheduleDate, "\#m/d/yyyy\#") & ")"
  7.   varKey = DLookup("[CourtID] & Format([ScheduleDate],' \- d mmm yyyy')", _
  8.                    "[Courts]", _
  9.                    strWhere)
  10.   If Not IsNull(varKey) Then
  11.     Cancel = True
  12.     Call MsgBox(strWhere & vbCrLf & varKey)
  13.     Debug.Print strWhere & vbCrLf & varKey
  14.   End If
  15. End Sub
  16.  
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
Mar 3 '07 #18
NeoPa
32,184 Expert Mod 16PB
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?
Mar 3 '07 #19
atc
28
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 BookingsTimeSubform.

Hope this helps.
John
Mar 3 '07 #20
NeoPa
32,184 Expert Mod 16PB
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?
Mar 3 '07 #21
atc
28
It's in the subform called BookingSubform at the moment.
John
Mar 3 '07 #22
NeoPa
32,184 Expert Mod 16PB
Is this currently the MetaData for your table named Courts?
Expand|Select|Wrap|Line Numbers
  1. Table Name=Courts
  2. Field; Type; IndexInfo
  3. CourtID; Autonumber; PK
  4. CourtName; String
  5. 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.
Mar 3 '07 #23
NeoPa
32,184 Expert Mod 16PB
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.
Mar 3 '07 #24
atc
28
NeoPa
I have sent a another copy to mary. Try that one.
john
Mar 4 '07 #25
NeoPa
32,184 Expert Mod 16PB
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.
Mar 5 '07 #26
NeoPa
32,184 Expert Mod 16PB
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).
Mar 6 '07 #27
atc
28
Do you want me to resend as a zip or is mary going to resend as a zip?
john
Mar 6 '07 #28
NeoPa
32,184 Expert Mod 16PB
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.
Mar 6 '07 #29
NeoPa
32,184 Expert Mod 16PB
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.
Mar 7 '07 #30
NeoPa
32,184 Expert Mod 16PB
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.
Mar 7 '07 #31
atc
28
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
Mar 7 '07 #32
NeoPa
32,184 Expert Mod 16PB
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.
Mar 7 '07 #33
NeoPa
32,184 Expert Mod 16PB
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.
Mar 7 '07 #34
atc
28
Ok thanks for the advice. Sorry that it is making your task to find a solution harder.
John
Mar 7 '07 #35
NeoPa
32,184 Expert Mod 16PB
Ok thanks for the advice. Sorry that it is making your task to find a solution harder.
John
I got there eventually.
Try :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_BeforeUpdate(Cancel As Integer)
  5.     Dim strWhere As String
  6.  
  7.     strWhere = "([CourtID]=" & Me!CourtID & ") AND " & _
  8.                "([ScheduleDate]=" & _
  9.                Format(Me!ScheduleDate, "\#m/d/yyyy\#") & ")"
  10.     If Not IsNull(DLookup("Schedule.ScheduleID", "[Schedule]", strWhere)) Then
  11.         Cancel = True
  12.         Call MsgBox("Your message here!")
  13.     End If
  14. End Sub
  15.  
  16. Private Sub Form_Current()
  17.     On Error Resume Next
  18.     'Will only work if running as subform of Bookings
  19.     Me.Parent![ScheduleLinkID] = Me![ScheduleID]
  20. End Sub
Make sure to copy and paste.
Mar 7 '07 #36
NeoPa
32,184 Expert Mod 16PB
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. ScheduleID; AutoNumber; PK
  3. ScheduleDate; Date/Time
  4. CourtID; Number; FK
Mar 7 '07 #37
NeoPa
32,184 Expert Mod 16PB
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.
Mar 7 '07 #38
atc
28
Thanks for all your help NeoPa.
The problem is solved.
I'll take your advise on board.
ATC
Mar 7 '07 #39
NeoPa
32,184 Expert Mod 16PB
You're welcome John.
An interesting, if complicated, problem to solve.
Mar 7 '07 #40

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by vsgdp | last post: by
3 posts views Thread by ryan.paquette | last post: by
Thekid
3 posts views Thread by Thekid | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.