dlookup

[Rockape]

Hi all,

I need some starting help with some code.

I have a field name called "reference" and these references are stored in the table called tbl_personnel.

I have a form called "frm_input" and a box named "ref". It is through this box that i enter the data for "reference" in the tbl_personnel.

I need code that will prevent me from continuing entering more data if it finds that a number being entered already exists in the database (as part of some other record).

I have looked at dlookup, but i have never used it as part of an If command.

Grateful for some help.


cheers to all

 

[Scooterbug]

I would use a dcount in lieu of a dlookup. Reason being is this can tell you if more than one of the reference exsists.

dim iCount as interger
 
icount = dcount("*","TableName","[Ref]=" & me.TextBox)
 
if icount<> 0 then
     msgbox icount & " record(s) already exsist for this person."
end if

 

[boblarson]

Also, Dcount can handle nulls where DLookup can't and you would need to encapsulate the DLookup in the NZ function where you don't need to do that with DCount.

 

[Rockape]

Thanks Scooterbug and Bob,

Tried your code or something similar. Perhaps my mistake in not saying that the number is a long text value with some letters and numbers.

But will try again, Will get back to you.

Thanks once again

 

[boblarson]

Then just change

dcount("*","TableName","[Ref]=" & me.TextBox)

to

dcount("*","TableName","[Ref]=" & Chr(34) & me.TextBox & Chr(34))

 

[Rockape]

Thanks bob,

It works, out of curiosity, what does the Chr(34) do?


on another note...I've put this code on the beforeupdate. How can i stop it from continuing? Yes I'm advised but it doesn't stop me from bypassing and continue with the error!

Thanks yet again

 

[boblarson]

It works, out of curiosity, what does the Chr(34) do?

Chr(34) = "

so it surrounds text with quotes

on another note...I've put this code on the beforeupdate. How can i stop it from continuing? Yes I'm advised but it doesn't stop me from bypassing and continue with the error!

What is the full code you are using in the BeforeUpdate event? With that we can modify it so it will work.

 

[Rockape]

hi,

There is no code before that. What I've done is included the code you gave on the beforeupdate on that box.

My intention was that if that unique text was entered previously with another record, the message box would appear preventing further entry for that record.

my reasoning if I go further is this:

The table has a a field called status. this field is populated with a combo selecting from two options eg active or inactive.

therefore going back to my original request, if the reference number already exists and the status field is "active" the record cannot be saved. If the reference number exists and the status is inactive the the reference number which already exists would be allowed.

the idea being that i would be able to keep a history of inactive records.

Hope this makes sense.

 

[Rockape]

hi again,

I was perhaps being economical with my explanantion previously but i thought i could work with what you gave me. I'm out of my depth on this one, so i would be grateful for your patience.

Thanks again for the trouble

 

[boblarson]

So, essentially it would be something like this in the FORM's BEFORE UPDATE event:

dim iCount as interger
 
icount = dcount("*","TableName","[Ref]=" & Chr(34) & me.TextBox & Chr(34) & " AND [Status]='Active'")
 
if icount<> 0 then
     msgbox icount & " record(s) already exsist for this person."
     Cancel = True
     Me.Undo
end if

 

[Scooterbug]

therefore going back to my original request, if the reference number already exists and the status field is "active" the record cannot be saved. If the reference number exists and the status is inactive the the reference number which already exists would be allowed.

the idea being that i would be able to keep a history of inactive records.

Hope this makes sense.

How are you determining what the status is? I would assume that you have a Yes/No field in the table to denote active or not.

If that is the case, you can have multiple criteria in the dcount.

icount = dcount("*","TableName","[Ref]=" & chr(34) & me.TextBox &  _
chr(34) & " AND " & chr(34) & "[Status]=" & chr(34) & "Active" & chr(34)

Note, that if you are using a yes/no field, you can use [status]=0 for No and [status]=-1 for yes.


As for where to put the code, I would put it in the Before Update event of the form. This way, it will check before the record is actually written to the table.

 

[Rockape]

Dear Bob and Scooterbug,

Many thanks, I will be trying out this code this morning.

As regards status, the choice is either Active or Inactive. and yes I agree the code should be placed in the Beforeupdate.

One question: (sorry if seem repetitive, Why do you use Chr(34) and not just the " ?

Many thanks and once again my apreciation.

 

[laspettwi]

Thanks so much 4 sharing with us,bros

 

[boblarson]

Why do you use Chr(34) and not just the " ?

Because it isn't just putting a " in there. If you use Chr(34) then it does and it is fine, but if you use the " you need 3 of them """ which gets a little confusing at times to people, so I've found using Chr(34) is way easier to understand for most than remembering when to put """ in place.

 

[Scooterbug]

What I like to do is to build a quick little function to handle the chr(34).

Function QuoteWrap(sString as String) as String
 
QuoteWrap = chr(34) & sString & chr(34)
 
End Function

this way, when I need to make sure that quotes are included like in your situation, I can just do this:

icount = dcount("*","TableName","[Ref]=" & QuoteWrap(me.TextBox) &  _
" AND " & QuoteWrap("[Status]=") & QuoteWrap("Active")