Check if item is already there

S

s4

Hi, I have a DB where you can add a list of items. I'm trying to however make
it so that if you try adding an item more than once it will stop you. I've
done this before where it finds the item and lets you change the quantity. I
can't get this one to work though, can someone please check my code??

It's a bit messy because I've just been seeing if I could change bits etc to
get it working.

FND = DLookup("plu_code", "tbl_supps", "[sup_item] like '*" & Me.Text8 & "*'")
FOUND = DLookup("barcode", "tbl_barcs", "[plu_code] = '" & FND & "'")

Select Case MsgBox("The closest match for supplier code " & Me.Text8 & " is:
" & Chr(13) & FOUND & Chr(13) & "Would you like to use this item?", vbYesNo,
"Found Items")
Case Is = vbYes
If (DLookup("FIELD1", "SD_PLP", "[FIELD1] ='" & FOUND & "'")) <> Null Then
DoCmd.GoToControl "FIELD1"
DoCmd.FindRecord FOUND, acAnywhere, , acSearchAll, , acAll
DoCmd.GoToControl "FIELD7"
MsgBox "FOUND"
Exit Sub
Else
DoCmd.GoToRecord , , acNewRec
Me.Field1 = FOUND
DoCmd.GoToControl "FIELD7"
MsgBox (DLookup("FIELD1", "SD_PLP", "[FIELD1] ='" & FOUND & "'"))
End If

The messagebox always shows as if it is there but never acts so.

Thanks for any help!
 
M

Maurice

Maybe my stupid thought but where is the case else and end select statement?

You do have a Case Is =vbYes but no alternative.

I think that you might have forgotten something here because this piece of
code would not compile correct. It should look someting like

select case....

Case Is vbYes
your code
Case else
alternative
End select

That way you don't need the if statement.
 
S

s4

Yes the code does include the End Select etc and compiles correctly. I just
didn't see the need in including it and so didn't paste it into the question.

I've stripped it down to

if (DLookup("FIELD1", "SD_PLP", "[FIELD1] ='" & FOUND & "'")) = found then
msgbox "Exists"
else
msgbox "Not Found"
end if

(I've declared what FOUND is as well at the top btw)

It always acts one way or the other no matter what the result. I've reversed
it to put <> and 'Not Found' as the first if. It always does one thing no
matter the result. It's worked on other projects fine, but it just won't have
it here. Is there another way around or anything.

Thanks for your help.

Maurice said:
Maybe my stupid thought but where is the case else and end select statement?

You do have a Case Is =vbYes but no alternative.

I think that you might have forgotten something here because this piece of
code would not compile correct. It should look someting like

select case....

Case Is vbYes
your code
Case else
alternative
End select

That way you don't need the if statement.

--
Maurice Ausum


s4 said:
Hi, I have a DB where you can add a list of items. I'm trying to however make
it so that if you try adding an item more than once it will stop you. I've
done this before where it finds the item and lets you change the quantity. I
can't get this one to work though, can someone please check my code??

It's a bit messy because I've just been seeing if I could change bits etc to
get it working.

FND = DLookup("plu_code", "tbl_supps", "[sup_item] like '*" & Me.Text8 & "*'")
FOUND = DLookup("barcode", "tbl_barcs", "[plu_code] = '" & FND & "'")

Select Case MsgBox("The closest match for supplier code " & Me.Text8 & " is:
" & Chr(13) & FOUND & Chr(13) & "Would you like to use this item?", vbYesNo,
"Found Items")
Case Is = vbYes
If (DLookup("FIELD1", "SD_PLP", "[FIELD1] ='" & FOUND & "'")) <> Null Then
DoCmd.GoToControl "FIELD1"
DoCmd.FindRecord FOUND, acAnywhere, , acSearchAll, , acAll
DoCmd.GoToControl "FIELD7"
MsgBox "FOUND"
Exit Sub
Else
DoCmd.GoToRecord , , acNewRec
Me.Field1 = FOUND
DoCmd.GoToControl "FIELD7"
MsgBox (DLookup("FIELD1", "SD_PLP", "[FIELD1] ='" & FOUND & "'"))
End If

The messagebox always shows as if it is there but never acts so.

Thanks for any help!
Click to expand...
Click to expand...
 
Top