Record Locks when running a DLookUp Macro??

[OrionElectrotech]

I have a data entry form where users add new Candidates and their information. The candidate name (ca_name) is the primary key in the underlying table (CANDIDATES), with no duplicates allowed, but duplications will not be checked for until they save the form. However I need it to check if it is a duplicate immediately.

I have tried the following macro, which runs On Update -

DLookUp("[ca_name]","[CANDIDATES]","[CA_NAME] = Form.[CA_NAME] ") Is Not Null
And cancels the event.

However this appears to be creating lots of record locks, we're guessing when 2 or more users are adding candidates at the same time, and the DLookup is trying to run simultaneously?

Does anyone have any suggestions of how to stop this happening?

Thanks

Caroline

 

[SoCalAccessPro]

It's not Dlookup that's doing it.

Your record is "Dirty", and Access locks several different ways. The default of which is page level locking, IIRC (All Records, it may be called somethibg else).

Look into record level locking.





Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[OrionElectrotech]

Thanks for your suggestion.

My database defualts to record level locking at the moment, but I still believe the problem is when people are adding new records at the same time - in the data entry form, the new record is not acknowledged in the underlying table until you come out of that record.

Can I get the new record to save any quicker using a macro or something?

Caroline

 

[TheAceMan1]

How are ya OrionElectrotech . . .

The [purple]syntax[/purple] in the criteria statement of your DLookUp [purple]is not proper![/purple] Try this:

[blue]"[CA_NAME] = '" & Me![CA_NAME] & "'"[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[OrionElectrotech]

Hi Aceman

Have I copied your syntax correctly?

DLookUp("[ca_name]","[CANDIDATES]","[CA_NAME] = '" & [Me]![CA_NAME] & "'") Is Not Null

It keeps putting square brackets around [Me], and doesn't then recognise it.

Yours (in need of training!),

Caroline

 

[JBinQLD]

You've corrected the syntax fine now, dont worry about the [].

I'm more worried about just putting "is not null" at the end. You have used an IF somewhere haven't you?

Basically, rather than a macro, just put in some VBA (Event procedure) thus:

if nz(DLookUp("[ca_name]","[CANDIDATES]","[CA_NAME] = '" & [Me]![CA_NAME] & "'"),"")="" then
  'no records matched this record so procede
else
  'a mtch was found - cancel
end if

Note Nz function on the dlookup - if the lookup returns a null this coverts it to "".

Make sense?

 

[PHV]

Use an event procedure (VBA code) instead of a macro ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OrionElectrotech]

This brings up a run time error 2465 -
Orion Database can't find the field '|' referred to in your expression.

Caroline

 

[PHV]

Which line of code is highlighted when in debug mode at the time the error raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

Which line is highlighted? You have put "if nz(DLookUp("[ca_name]","[CANDIDATES]","[CA_NAME] = '" & [Me]![CA_NAME] & "'"),"")="" then" all on one line haven't you? the line break is from this site, it's a one line statement

Also, chuck in :

Msgbox(me![ca-name])

at the top of the code to ensure it is getting your control value correctly.

 

[OrionElectrotech]

The whole line is highlighted. Yes, I put it all onto one line.

Caroline

 

[PHV]

Could you, please, post the whole VBA code of your ecent procedure (from Private Sub to End Sub) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OrionElectrotech]

Private Sub ca_Name_BeforeUpdate(Cancel As Integer)

MsgBox (Me![ca_name])
If Nz(DLookup("[ca_name]", "[CANDIDATES]", "[CA_NAME] = '" & [me]![ca_name] & "'"), "") = "" Then

'no records matched this record so proceed

Else

'a match was found - please enter a number after the candidate name to proceed

End If
End Sub

 

[PHV]

I'd try this:

Private Sub ca_Name_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookUp("ca_name", "CANDIDATES", "ca_name='" & Me.Controls("ca_Name") & "'")) Then
  MsgBox Me![ca_name] & " already in the CANDIDATES table"
  Cancel = True
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

Try this in the AFTER Update event:

If Nz(DLookup("[ca_name]", "[CANDIDATES]", "[CA_NAME] = '" & [me]![ca_name] & "'"), "") = "" Then

  'no records matched this record so proceed
  
Else

  'a match was found - please enter a number after the candidate name to proceed
me!ca_Name=""

End If

If you want, you could write a litte function to automatically add a number to end of name, for now i'm just setting it to blank

 

[OrionElectrotech]

Well, JBinQLD's suggestion - that gets the same error message as before.
PHV's suggestion - nothing happens, no error message on the code, but no message saying it's a duplicate name either.

Caroline

 

[JBinQLD]

In my previous comment i suggested adding the mgbox and i see you did. Did the msgbox pop up when you updated? If so, what did it say?

 

[PHV]

So, try this:

Private Sub ca_Name_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookUp("ca_name", "CANDIDATES", "ca_name='" & Me.Controls("ca_Name") & "'")) Then
  MsgBox Me![ca_name] & " already in the CANDIDATES table"
  Cancel = True
Else
  MsgBox Me![ca_name] & " is not yet in the CANDIDATES table"
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OrionElectrotech]

In answer to JBinQLD's question, it just shows the candidate name you are trying to enter, with an ok button below.

PHV - works a treat! Was that just with the addition of the Else argument?

Thanks to everyone for your help, as ever,

Caroline

 

[OrionElectrotech]

Just a thought - will this solve our problem of record locks??!! Or is that down to 2 users trying to add a record at the same time?
I got so engrossed in getting the code right that I forgot the main reason for my initial question!

Caroline