DLookUp Case

[Haytham]

Hi All...
In a form, I have a date field and PostOffice Field. I want the entry to be restricted to one entry daily for every PostOffice.
I tried the code as follows:

Private Sub txtPostOffice_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[txtdate]", "StampsOrderQ", "[Date]= " & [txtDate] & "")) Or Not IsNull(DLookup("[txtpostoffice]", "StampsOrderQ", "[Postoffice]= '" & [txtPostOffice] & "'")) Then

MsgBox "Remember ... Post Office Should Be Entered Once Daily Only", vbCritical
DoCmd.RunCommand acCmdUndo
DoCmd.CancelEvent
DoCmd.SetWarnings False
Exit Sub
End If
DoCmd.SetWarnings True

But it's still accepting the entry of similar office in the same day.
I'm sure something is wrong in my code.
Will anybody check it for me please...

StampsOrderQ is the Query Name and the rest are the names of the fields in the table and form.

Thanks in advance.

 

[David R]

I believe you may find Dcount faster for this, since you don't actually care what the value is, just that it already exists. Fill in the fields as needed.

If (DCount("[PostalOfficeID]", "YourTableOrQuery", "[PostalOfficeID]= '" & Me!PostalOfficeControl & _
   "' AND [DateField] = #" & Me.DateFieldControl & "#") = 1) Then
   MsgBox "Remember...only one entry per post office per day."
   Cancel = True
End If

The rest is unnecessary. You can set the field to blank with Me.Undo I think but you might catch yourself in a loop.

This is vaporcode so typos are possible.

HTH,
David R

[This message has been edited by David R (edited 02-12-2002).]

 

[Haytham]

Hi David, Thank you for reply. Well, I tried the code as follows:

If (DCount("[PostOffice]", "StampsOrderQ", "[txtPostOffice]= '" & Me.txtPostOffice & "' AND [Date] = #" & Me.txtDate & "#") = 1) Then
MsgBox "Remember...only one entry per post office per day."
Cancel = True
End If


And also

If (DCount("[PostOffice]", "StampsOrderQ", "[txtPostOffice]= '" & Me!txtPostOffice & "' AND [Date] = #" & Me!txtDate & "#") = 1) Then
MsgBox "Remember...only one entry per post office per day."
Cancel = True
End If
but still record is saved successfully.
I put my code in Form level BeforeUpdate as well.
I'm still seeking your help.
Thank you.

 

[David R]

Okay, I actually went through and coded this in a test db today. One thing that springs to mind is that your date field should not be named [Date], as that is a reserved word in Access.

Here's what's behind the second of the two 'unique' fields' BeforeUpdate event (the Form's BeforeUpdate would work, but it's better to catch the error as soon as possible):

Private Sub DateDone_BeforeUpdate(Cancel As Integer)
If (DCount("[PostOffice]", "[PostOfficeQuery]", "[PostOffice]= '" & Me!PostOffice & _
    "' AND [DateDone] = #" & Me.DateDone & "#") = 1) Then
        MsgBox "Remember...only one entry per post office per day."
        Cancel = True
End If
End Sub

 

[Haytham]

Hi David R,
Sorry to disturb you but still my code is not working, and further seek your help.

My FieldName in Query is Date and PostOffice.
In Form, the control name is txtDate and txtPostOffice.
PostOffice is Text and not Integer.

In BeforeUpdate of My txtPostOffice Field this is my code:

Private Sub txtPostOffice_BeforeUpdate(Cancel As Integer)
If (DCount("[PostOffice]", "StampsOrderQ", "[PostOffice]= " & Me!txtPostOffice & " AND [Date] = #" & Me.txtDate & "#") = 1) Then
MsgBox ""
Cancel = True
End If
End Sub

When I run my code, it gives me Runtime Error 2471

The expression you entered as a query parameter produces this error:
The object doesn't contain the automation object "...." and specifies my entry e.g. New York Branch

Hope to hear from you again, and sorry for inconvenience
Thanks a lot…


[This message has been edited by Haytham (edited 02-13-2002).]

 

[David R]

One thing that springs to mind is that your date field should not be named [Date], as that is a reserved word in Access.

The code you showed me didn't correct this. If you're still having troubles after that give posting another try. I did with my copy of your code exactly what you're proposing (if I understand the question correctly).

David R

 

[Haytham]

Hi David R,
May I send you my db

 

[David R]

I would prefer if you corrected that field and then see if it works. If not, post back here again. That way you get the benefit of the help of more than one person.

David R

 

[Haytham]

Yes Dave I named my field as OrderDate and changed it in the code as well.
Still it gives me same error.

 

[David R]

Did you change the control name in your form as well? Ahh wait...

Sorry, my eyes aren't what they used to be.
If your [PostOffice] field is a text field, you'll need to put single quotes around the value (much like you surrounded the date field with #'s to show that it was a date field).
"[PostOffice]= '" & Me!txtPostOffice & "' AND [Date] = #" & Me.txtDate & "#") = 1)

It's a small, subtle difference, and it still trips me up sometimes. Try putting a debug line in your code so you can see what the where clause actually looks like.
I usually use Msgbox "WHATEVER MY WHERE CLAUSE IS" right before the actual DCount line when I'm having trouble.
Numbers compared should have no symbols. Text should have single or double quotes around the value. Dates should have #'s.

HTH,
David R

 

[Haytham]

Sometimes I feel myself confident of writing the codes in Access, while when I stuck, I feel that I'm so stupid...
Ok Dave, it works fine. I'm very thankful for all your help. But then, if I enter same PostOffice Name, MsgBox Appear and then I have another MsgBox says:
The value in the field or record violates the validation rule......
If I press ok and then Escape, still record can be saved...
I tried to get rid of so MsgBox by
DoCmd.SetWarnings False
but still it appears..
If you have any suggestions Dave for this problem, it'll be highly appreciated.

At last, I'm very thankful for your kind help

 

[David R]

Getting rid of messageboxes by setting DoCmd.SetWarnings False/True is only a good idea when you know what the message is and can safely disregard it. If it's warning you the record cannot be saved, there is probably something in your code that needs to be reexamined.

I'm not sure what you mean by 'Hit OK and then escape'. Do you mean Click OK and then Press the Esc key, or that clicking 'OK' gets you out of the message box? What validation rule, if any, do you have set on your PostOffice field?

 

[Haytham]

'Hit OK and then escape' yes means that I Click OK and then Press Esc key.
If I do so, it accepts my entry even if it's duplicated.

 

[David R]

Are you sure it's saving it the second time? Take a look at your underlying table to see if the data is actually duplicated. I have no idea why you're hitting the Esc key, but you may be bypassing the check on your index being unduplicated.
Is this happening when you actually try to enter "New York Branch" and #2/13/2002# a second time, or when you type "New York Branch", before you even get to put in #2/14/2002# (for example)?

 

[Haytham]

Actually, I set the Date Field in my form to Date(). My PostOffice Field is a LookUp Field picking a data from other table with restriction: Limit To List : Yes.
So, when I open a form to enter a new data, Today's date is selected, and I have to select the office name from the list only.
Here, the violation message pop-up after my MsgBox. I click Esc which may happen when a user try to get rid of that message. So, I want the user to enter data according to my rules only. I assume if a user know how to play with a database, he/she may pit the Esc button.
Again this data is stored in my underlying table, which violates the rules.

 

[David R]

In the meantime I've done something else in another topic you may find helpful: http://www.access-programmers.co.uk/ubb/Forum3/HTML/003010.html, read the last posting by me, about how to create a unique multi-field index.
This will absolutely allow no duplication of these two fields in your table, but it won't catch it until you try to save the record (much like duplicating a PK). The DCount() code above is designed to do that immediately after entering the second value, but for whatever reason is not working in your database.

Good luck,
David R

 

[Haytham]

I'll have a look at the topic.
Thanks a lot David R for all your kind help.
Yours