Not very good at delimiters, operators etc

G

Garindan

Registered User.
Local time
Today, 18:01
Joined
May 25, 2004
Messages
250
Hi all, I'm trying to prevent duplicate entries in a field with a DCount and custom message box, but the code isn't quite right... keep getting Syntax Error (missing operator) in query expression '[CommentAlarm]='

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblCustomerComments", "[CommentAlarm]=" & Me.txtCommentAlarm) > 0 Then  ' It is a duplicate
    Cancel = True
    MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
    Me.Undo  '<----Remove this if you don't want to erase form input
End If
End Sub

Also, if I prevent duplicate entries in this way do i still need the table field set to index no duplicates or can i have no index and this will prevent duplicates for me? (As long as this is the only entry for this field obviously). Cheers
 
Is commentalarm a string by any chance? if so, you need to enclose it in single quotes
Code: 
If DCount("*", "tblCustomerComments", "[CommentAlarm]=[COLOR="red"][B]'[/B][/COLOR]" & Me.txtCommentAlarm & "[B][COLOR="Red"]'[/COLOR][/B]") > 0 Then
 
Thanks for the reply David. No, I have included it in single quotes and now i get data type mismatch in criteria expression. CommentAlarm is a Date/Time field in General Date format, any help?
 
Then change the single quotes to #'s

Text needs single quotes
dates need hashes
number need nothing
 
Code: 
If DCount("*", "tblCustomerComments", "[CommentAlarm]=#" & Me.txtCommentAlarm & "#") > 0 Then

Syntax error in date in query expression?
 
Is there a date in the TxtcommentAlarm Textbox? If so what does it look like?
 
Sorry David its behaving strange. The code i currently have is
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[CommentAlarm]", "tblCustomerComments", "[CommentAlarm]= #" & Me![txtCommentAlarm] & "#") > 0 Then  ' It is a duplicate
    Cancel = True
    MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
    Me.Undo  '<----Remove this if you don't want to erase form input
End If
End Sub

If a date is in the text box its showing me the error message I have set. If i clear the date in the text box and try to move to another record it then says runtime error 3075 - syntax error in date query expression '[CommentAlarm]= #'.

The date in the text box is 30/03/2011 15:49:00, I have it set to General Date
 
Couple of things

First you need to check that there is a date in the field before doing the count

Second the date need to be set to dd/mm/yyyy

Whats the odds of finding a match to the second

Code: 
[B]If IsDate(Me.TxtCommentAlarm) Then[/B]
If DCount("[CommentAlarm]", "tblCustomerComments", "[CommentAlarm]= #" & Me![txtCommentAlarm] & "#") > 0 Then  ' It is a duplicate
    Cancel = True
    MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
    Me.Undo  '<----Remove this if you don't want to erase form input
End If

[B]End If[/B]
 
Ok so 'If IsDate(Me.TxtCommentAlarm) Then' checks to see if there is anything in the field, and prevents the error I was getting when the field was blank. I understand that bit now, thank you! And I no longer get that error.

Second the date need to be set to dd/mm/yyyy

Whats the odds of finding a match to the second
Click to expand...

I don't quite understand what you mean here? :o The code seems to think a matching date is in the field, even though it is the only CommentAlarm date entered in the whole table at this point (the rest are blank) but I still get the message box etc. Is that what you mean? I wanted to prevent duplicate date/time's being entered as the 'CommentAlarm' field is going to be used for a pop up alarm form, and I didn't want more than one trying to pop up at once. I wanted to limit it so if an alarm was already set for a date and time the user would be prompted and could set an alarm for a minute later, or five minutes later etc etc. Am I going about it the wrong way or will it work this way?

Sorry and thanks again!

Heres my curent code:-
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsDate(Me![txtCommentAlarm]) Then
    If DCount("[CommentAlarm]", "tblCustomerComments", "[CommentAlarm]= #" & Me![txtCommentAlarm] & "#") > 0 Then  ' It is a duplicate
        Cancel = True
        MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
        Me.Undo  '<----Remove this if you don't want to erase form input
    End If
End If
End Sub
 
Looks like I left an extra paren on the end after #mm\/dd\/yyyy#))

Take the extra off and I would change to asterisk (I missed that) so


If DCount("*", "tblCustomerComments", "[CommentAlarm]= " & Format(Me.txtCommentAlarm, "#mm\/dd\/yyyy#) > 0 Then
 
If DCount("*", "tblCustomerComments", "[CommentAlarm]= " & Format(Me.txtCommentAlarm, "#mm\/dd\/yyyy#") > 0 Then

You missed the closing double quote off it Bob.
 
Sheesh! I sure hope I can get the money together for those new glasses. I need them bad.
 
Still can't get this right!! I've got:-

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsDate(Me![txtCommentAlarm]) Then
    If DCount("*", "tblCustomerComments", "[CommentAlarm]= " & Format(Me.txtCommentAlarm, "#mm\/dd\/yyyy#")) > 0 Then ' It is a duplicate
        Cancel = True
        MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
        Me.Undo  '<----Remove this if you don't want to erase form input
    End If
End If
End Sub

and it says
 
Also, can I combine these two?? Sorry, I AM learning a lot :D

Code: 
Private Sub Form_AfterUpdate()
If Not IsNull(Me![txtCommentAlarm]) Then
    Me![txtComputerName] = Environ("Computername")
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsDate(Me![txtCommentAlarm]) Then
    If DCount("*", "tblCustomerComments", "[CommentAlarm]= " & Format(Me.txtCommentAlarm, "#mm\/dd\/yyyy#")) > 0 Then ' It is a duplicate
        Cancel = True
        MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
        Me.Undo  '<----Remove this if you don't want to erase form input
    End If
End If
End Sub
 
Garindan said:
Still can't get this right!! I've got:-

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsDate(Me![txtCommentAlarm]) Then
    If DCount("*", "tblCustomerComments", "[CommentAlarm]= " & Format(Me.txtCommentAlarm, "#mm\/dd\/yyyy#")) > 0 Then ' It is a duplicate
        Cancel = True
        MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
        Me.Undo  '<----Remove this if you don't want to erase form input
    End If
End If
End Sub

and it says
Click to expand...

Add a

Debug.Print Me.txtCommentAlarm

to the code just before the DCount so you can see what the input is going to the code. It looks to me like it isn't a valid date that was input.
 
Garindan said:
Also, can I combine these two?? Sorry, I AM learning a lot :D

Code: 
Private Sub Form_AfterUpdate()
If Not IsNull(Me![txtCommentAlarm]) Then
    Me![txtComputerName] = Environ("Computername")
End If
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsDate(Me![txtCommentAlarm]) Then
    If DCount("*", "tblCustomerComments", "[CommentAlarm]= " & Format(Me.txtCommentAlarm, "#mm\/dd\/yyyy#")) > 0 Then ' It is a duplicate
        Cancel = True
        MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
        Me.Undo  '<----Remove this if you don't want to erase form input
    End If
End If
End Sub
Click to expand...
Yes, you can combine the two.
 
The 'Debug.Print Me.txtCommentAlarm' isn't doing anything, it doesn't show me anything :(

I am getting the error message after the date has been entered and when I try to click into another text box/form, so I guess this is when the BeforeUpdate event is run?

The date/time is entered using a pop up Calendar and code, but the date displays fine and seems to be correct?

Edit - the error appears when I try and click into another record, not when I click into another text box on the same record.
 
Garindan said:
The 'Debug.Print Me.txtCommentAlarm' isn't doing anything, it doesn't show me anything :(
Click to expand...
You looked in the IMMEDIATE WINDOW?

attachment.php



I am getting the error message after the date has been entered and when I try to click into another text box/form, so I guess this is when the BeforeUpdate event is run?
Click to expand...
That would be a before update for the control, yes.

The date/time is entered using a pop up Calendar and code, but the date displays fine and seems to be correct?
Click to expand...
That is why we need to see if the value is coming through okay using the IMMEDIATE WINDOW and the Debug.Print code.

Edit - the error appears when I try and click into another record, not when I click into another text box on the same record.[/QUOTE]
 

Attachments

  • immediatewindow.jpg
    immediatewindow.jpg
    77.1 KB · Views: 111
You must log in or register to reply here.

Similar threads

A
Restrict the number of records entered in form
2
Replies
29
Views
1,131
gemma-the-husky
gemma-the-husky
J
Continuous form problem.
Replies
2
Views
387
John Sh
J
D
Solved Setting a field to null on Dirty
Replies
10
Views
470
dullster
D
D
Solved How to quickly duplicate Tables & associated subforms within a main form
2 3 4
Replies
71
Views
2,185
dibblermail
D
M
Solved Duplicate value check - complicated.
Replies
16
Views
1,457
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom