Need Help DLookUp

[FCCC]

Hi everybody,

I am trying to find a duplicate number on Item_No field but my code always returns NOT NULL even there was no duplicates. Actually I only have 1-30 numbers on my Item_No field. Can someone tell me what is wrong with my code below?

Dim FindItemNo As Variant
NewItemNo = Item_No + 100
FindItemNo = DLookup("[Item_No]", "dbo_PMOption_History", NewItemNo)

If IsNull(FindItemNo) Then
Item_No = NewItemNo
Else
Item_No = NewItemNo + 50
End If

Do I need to Dim NewItemNo? Any ideas will be very much appreciated.

Thanks.

 

[thornmastr]

Try this:

Dim finditem as variant
Dim newitem as long ‘assuming it’s numeric

New_item = item_no + 100 ‘assuming item_no defined already
Finditem = dlookup (“item_no”, “dbo_pmoption_history”, _
“item_no = ” & new_item

if nz(find_item) = 0 then
item_no = new_item
else
item_no = newitem + 50
endif
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[FCCC]

Hi Robert,

Thanks for the idea, however it is doing the reverse of what I had. Even there was a duplicate number, it won't find it. It returns NULL and doesn't add 50 to the next number.

Hope you still have any ideas.

Thanks again.

 

[thornmastr]

Well, obviously I looked at this the wrong way. Let me give you another approach since I have an aversion to using aggregate functions. I’ll give you a very generalized approach and you should be able to see what is going on. If you have other questions, feel free to post back.

Assuming you want to know if some value will give you a duplicate key; which you cannot have, the following function will return true if there is a duplicate value. The input to the function is the key value you want to test. I am assuming the value is numeric, and what you are looking for is a column item called primKey.

Public function fduptest (valueIn as long) as Boolean
Dim rs as recordset
Dim strSQL as string

Strsql = “select * from yourTable where primkey = “ _
& valuein
set rs = currentdb.openrecordset (strSQL, dbopensnapshot)
if rs.recordcount = 0 ‘ no duplicates
fduptest = false ‘means you can use that nbr for key
else
fduptest = true ‘whoops. It found a record
endif

end function

Believe it or not, the IO call is faster than DLOOKUP. Now, putting this into the proper construct.

If fduptest(newitemno + 10) = false then
You can use this value
Else
Whoops. You need another value
Endif

Hope this helps.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[FCCC]

Hi Robert,

Thank you very much for another idea. I will give it a try and I will let you know.

 

[FCCC]

Hi Robert,

Tried the last code that you gave me but I get error in using function. I never tried using Function before, can you please tell me where I should put the code? My code right now is in AfterUpdate.

Thanks again and I really appreciate your help.

 

[FCCC]

Hi Robert,

I was able to get to work the first code you gave. The reason why it always returned NULL was the field of the variant that you are looking for should be enclosed with parenthesis "[]"...

Dim FindItem As Variant
Dim New_Item As Long

New_Item = Item_No + 100
FindItem = DLookup("Item_No", "dbo_PMOption_History", "[Item_No]=" & New_Item)

If Nz(FindItem) = 0 Then
Item_No = New_Item
Else
Item_No = New_Item + 50
End If

That was all I changed and it works. I knew your code was right except for the naming convention on the DLookup statement.

Thanks again for the help.