Index related question

[arage]

well Alexandre, i more or less did it (w/your & others help of course) this is
ofcourse a tentative solution until
i test it for any loopholes but it seems
to be working for now.

check this out:

If DCount _
(DLookup("Event_form_Event_", "defaultquery", "event_form_event_=" & Me.[Event_form_Event_]), _
"defaultquery", "[regionaldirector]= '" & Me.txtGlobalRegDir & "'") >= 1 Then
MsgBox "Duplicate", vbExclamation, "Error"
Cancel = True
Exit Sub
End If

thanks for sticking w/me this long Alexandre!

 

[arage]

ok, i spoke too soon! this thread lives on

 

[Alexandre]

Arage, the more I look to your Dcount/Dlookup creature (never seen something like that before) the less I think it will ever work.
Imagine that there is no record corresponding to your Dlookup (= one of the cases when the key entered by the user is not duplicate but new), what value will hold the first parameter of your DCount?

Someting like Null I guess.
Certainly not Me.[Event_form_Event].

So how will Dcount work with such parameter?
(By the way it must be Me![Event_form_Event] and not Me.[Event_form_Event])


If you persist

why not trying to combine both conditions in the Dcount criteria? (also, i am wondering: is really Event_form_Event the name of a field in your query? I doubt):

If Dcount(Me![Event_form_Event], "defaultquery", _
"[WhatEverIsTheFieldForEventInYourQuery] = " & Me![Event_form_Event] _
& " AND [regionaldirector]= '" & txtGlobalRegDir & "'") = 1 then ...

You might still have to check the quotes syntax around Me![Event_Form_Event], it might be like for txtGlobalRegDir

And if you definetly choose this method, then Before Update IS the right event to place it:

If ... then
Msgbox ...
DoCmd.CancelEvent
End If

 

[Keith P]

Using the Form_Error sub try

If dataErr = 3022 then
msgbox "Please enter another value"
Response = 0
me![myControl].setfocus
end if

 

[Alexandre]

Plainly agree with you, Keith, but the dawn guy is Dlookup-addicted

 

[arage]

Keith,
your code worked except it only worked for
particular records, specifically the reg Dir's (1100, 1200, etc)themselves and not on the representatives under them (1101, 1201 etc.)

sigh this is really too much

 

[Alexandre]

Arage,

The error code 3022 will only trap duplicated primary keys errors. So are your representatives reference numbers primary keys of a a "representative" table?

If so, I guess from "representatives under them" that they appear in a subform on the form where directors are? Then the error trapping routine must be put in the On_Error event of the subform, this time.

 

[Alexandre]

By the way, I m curious: Keith s code is strictly equivalent thanConst DuplicateKey = 3022

Dim strMsg As String

If dataErr = DuplicateKey Then
Response = acDataErrContinue
strMsg = "Duplicate, try again. "
MsgBox strMsg
End If

you had used previously but said it would not give the expected result. So what happened meanwhile?

 

[arage]

Aleandre,
yeah, the ref # is a concatenation of a sales rep code & event #.

the forms are filtered by directors, & then should accept incremental event #'s ie...

(repCode)-(event#)=(ref#)
is equivalent to...
1100 (repcode) - 1(event #) = 1100-1 (ref#)
but 1101-1 isn't acceptable since 1100-1
already exists.

Keith's code wasn't allowing me to do
something like 1200-1 if there was a 1100-1,
but 1200 and 1100 are 2 different regions ie
regional directors so it should have worked.

 

[arage]

yes oh yes friends, did you think i wud let
this thread die?

but of course not!

and yet, i bring good news, currently i've
managed to get my Dcount to properly return
values based on the function arguments it
(Dcount) takes.

it's a long story & i realize now that part
of the propblem was i was not explaining
part of my situation properly (i guess) &
that is why the code suggestions made to me
were not working.

as soon as i verify my code & start receiving
the error messages my form ought to be
giving i aim to return to this thread w/a
more detailed explanation.

after the explanation, at which time my form
shud be working as i see fit, i will in
essence slay this topic/thread that has
plagued my days and nights before running
screaming into the hills (much like Homer
Simpson did when he ate chilli peppers
that were strong enough to hurl his mind
into the twilight zone)

 

[arage]

i did it.

Alexandre, wherever you are thank you thank you thank you!

Everybody else, the same goes to you.

my code was wrong, it was a fundamental
error on my part in the way i perceived
the fields coming from my query. I was
saying stuff like....
event_form_event_
when i ought to have been saying stuff
like...
[event form].[event#]
the end resulting code looks like this & it
more or less works like a dream.

If Me!txtGlobalRegDir = Me!regionaldirector Then
If DCount("[event form].[event#]", "defaultquery", "[event form].[event#]=" & Me.Event_form_Event_ & "and [allreps].[regionaldirector]= '" & Me.txtGlobalRegDir & "'") = 1 Then
MsgBox "Your regiona has already assigned this event number. Please assign a new event number.", vbExclamation, "Error"
Cancel = True
Exit Sub
End If
End If