Index related question

[arage]

Index related question
I’d like to code something in the before update event of my form that will replace the default error message that comes up when a duplicate index is created. What system value can I check that I can use to override the default message, with one of my own? Please advise.

 

[Talismanic]

You could use a DCount to check for the value in the index which would then allow you to control the event without Access getting involved.

I am not sure what you are doing but here is a quick example of what I am talking about.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim Answer As Variant

If DCount("[jobname]", "JobNumbers", "[JobNumber]= '" _
& Me!JobNumber & "'") = 1 Then

strMsg = "Please try a different number"
strTitle = "Duplicate Number"
intStyle = vbOKCancel + vbInformation
Answer = MsgBox(strMsg, intStyle, strTitle)

If Answer = vbCancel Then Exit Sub

Cancel = True

End If

In my example the Job Numbers are not truely numeric. They are alphanumeric so if you are using real numbers you will have to alter this code slightly.

[This message has been edited by Talismanic (edited 03-09-2001).]

 

[arage]

hey thanks for the tip, i'll try it out.

but could you explain the purpose of the
last 2 statements in your code as it applies to your example? i don't follow those last 2 lines exactly.

i mean these lines...

If Answer = vbCancel Then Exit Sub

Cancel = True

and btw, aren't you short by one end if?

 

[Keith P]

An alternative approach is to use error trapping, e.g. on error goto errLabel

errLabel:
if error = whateverItIsForDuplicateIndex then
display message as described by Talismanic.
This method means the code is only being performed when there is an error not every entry.

 

[arage]

Right Keith,
your idea is more along the lines of
what i was thinking initially, but i was
unaware there is an On Error event, or
something like it, but according to you
there is & that's where i'll implement
Talismanic's idea.

 

[arage]

I’m currently just trying to get my DCOUNT procedure to work in a basic sense based on Talismanic’s idea but it’s not working. My code looks like this:

'err msg if ref # exists

If DCount("[event_form_event_]", "defaultquery", "[event_form_event_]=" & Me.Event_form_Event_) = 1 Then
MsgBox "Duplicate event, try again.", vbExclamation, "Error"
Cancel = True
Exit Sub
End If

The above code occurs currently, in the before update event of my form, but I may move it to an on error event, if I get it to work in a general sense. But as it stands, the code isn’t working properly. Please advise if anything wrong stands out above.

 

[arage]

actually i modified my last post with this code b/c it will help me out more. I'd appreciate comments on below:

If DCount("[event_form_event_]", "defaultquery", "[regionaldirector]=" & Me.regionaldirector _
And "[event_form_event_]=" & Me.Event_form_Event_) = 1 Then
MsgBox "Duplicate event, try again.", vbExclamation, "Error"
Cancel = True
Exit Sub
End If

 

[llkhoutx]

ON event BeforeUpdate, do a DLookup on the index to see if there's a duplicate, then cancel or not.

 

[Talismanic]

arage, did you get this figured out?

 

[arage]

Not yet Talismanic,

see, your suggestion was to Dcount and if i get a 1 then provide err msg. Thing is i have several regions (therefore several regional directors) ea. region has starts from its own event # zero & goes up, HOWEVER ALL EVENT #'S are stored in a single defaultquery (as shown in my code) therefore i need to provide an err msg if the Dcount=1 and the regional director match.

this is my current problem right now...

 

[Alexandre]

Arage,

Acces detects duplicate keys very well, why not let it do its job and just trap the error to replace the default message, as Keith suggested?
If I remember well the error code for duplicate key is 3022. There are examples in Access help on how to trap an error using error code (on error event), and probably even a specific example on duplicate keys.

 

[arage]

ok Alexandre, I'm trying it out your way right now but can't find the error code for duplicate keys (to verify the 3022 you mentioned previously)

 

[arage]

ok you're right, my bad it IS 3022

 

[arage]

currently i put the following at the end of my before update event but, it's not overriding the default messages with my own yet:

On Error GoTo errLabel

errLabel:
If Err = 3022 Then
MsgBox "Duplicate", vbExclamation, "Error"
Cancel = True
Exit Sub
End If

 

[Alexandre]

Arage,

Under A2K,I am sure about Err = 3022 this is an example of routine I use. I prefer to force (Me.refresh) the validation of the primary key immediatly after the user entered it because I find that access does it too late (only after udate of the whole record) that is why it is in an afterupdate event:


Private Sub Referencia_Contrato_AfterUpdate()
Const conCléDupliquée = 3022
Dim RefContr As String
Dim Choix As Integer
Dim RstChange As Recordset

On Error GoTo ErrorTrap

Me.Refresh
Exit Sub

ErrorTrap:
If Err = conCléDupliquée Then
RefContr = Me![Referencia_Contrato].Value
Choix = MsgBox("Verifique os seus dados: esta referência " _
& "de contrato jà existe." & Chr(13) & Chr(10) _
& "Quer conferir o Contrato: " & RefContr & " ?", vbYesNo _
+ vbDefaultButton2 + vbExclamation + vbMsgBoxSetForeground)

If Choix = 7 Then
SendKeys "{Esc}{Esc}", True
Me![CategFinanc].SetFocus
Else:
SendKeys "{Esc}{Esc}", True
If RefContr <> "" Then
Set RstChange = Me.RecordsetClone
RstChange.FindFirst ("[Referencia_Contrato] = '" & RefContr & "'")
Me.Bookmark = RstChange.Bookmark
Else:
Me![CategFinanc].SetFocus
End If
End If

End If
End Sub

It allows the user to go and see the existing record if he wants.

 

[Alexandre]

Arrage,

before update is NOT ok for the purpose: the value is not commited yet so how access could generate an error? Either use the On-Error event of the form (where acees goes and cheks automatically each time an error occurs) or follow my previous example by forcing refreshment afterupdate of the PrimKey and trap immediatly in the same event.

I enforce refreshment to avoid a situation where the user fills up the ten fields of the record and only at the end access tell him his PrimKey existed yet!

 

[arage]

check it out Alexandre:

i took your latter suggestion & put this code which i found in acc97 in the form error event:

Private Sub Form_Error(dataErr As Integer, Response As Integer)

Const DuplicateKey = 3022

Dim strMsg As String

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

End Sub

The only problem now is that after my message
i see a "you cant save this record at this time" message.

There is no discernable error # w/this message as only yes/no buttons are given.

i was hoping the code above would show a message & return control to the form.

 

[Alexandre]

Can t see any mistake in your code, Arage.
In fact, I couldn t eve reproduce your problem with a brand new table and form, thus confirming my suspicion: it must be provoqued by something else you made to your form.
Any .refresh or .requery for example would provoque an error since the duplicate PrimKey is not removed from the control yet.

That is why i use this duplicate key trapping in combination with (see my example above):

Sendkeys "{Esc}{Esc}" 'simulates manual suppression of the currently edited record

If you want to supress only the currently entered value :
SendKeys"{Esc}"

other wise, include this else statement in your on error code, to always get explicit description of the errors:

Private Sub Form_Error(dataErr As Integer, Response As Integer)

Const DuplicateKey = 3022

Dim strMsg As String

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

Else:
Msg = "Error # " & Str(Err.Number) & " generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.Helpfile, Err.HelpContext

End If

End Sub

When you handle various error trapping routines, you may prefer to use a Select Case than If

 

[arage]

Thanks for your great help Alexandre,
I don't want to give detailed help file
to my user & will look into the sendkey esc
commands as soon as i get my quick and dirty
solution working the way i want.

currently my code looks like this but i get
run time error 5 (which i haven't found in
help files yet):

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

the above is in the before update event
still, but hear me out.

the first argument of the Dcount will lookup
the form's event# in the query.

The 2nd argument is the domain being searched, again a query.

The 3rd argument is criteria where the
domain's field must equal a txt control
on the form.

this way i satisfy my conditions about
event#'s being in sync under a particular
region (ie a regional director)

for example this is acceptable:
1400-1
1404-2
1406-3

but this is not acceptable:
1400-1
1401-1
1403-2
1404-4
1406-4

b/c event #'s are duplicate for the region
(from 1400 to 1499)

 

[Alexandre]

I see...
Persistence in difficulty may be a quality sometimes. I am a bit lie you.

Your Dlookup is not ok. the 3rd parameter should be a criteria to test on, not a value (think about it as the right part of a WHERE statement in SQL). So you may ask for the value in one field (1st parameter) on a given table (2nd parameter) based on testing value in another field (3rd parameter).

In your case somthing like:
DLookup("Event_form_Event_", "defaultquery", "Event_form_Event = " & Me.[Event_form_Event_])?

But I must say you 're loosing me a bit with what you are trying to do. I might be wrong but I suspect you have a design problem: your underline table should have a two-fields primary key made of the fields event and regions.

Then... error trap! I insist because it is much more reliable, rapid (hardcoded) and performs only when necessary (when your function will run each time a value is entered...)