Dlookup

[TedMartin]

I have what should be a simple problem but cannot get a simply DoLookUp to work. I have spent the last hour running through your searches but jsy can't fix it.

I have a table of Unique Part Nos and just want to do a simple user message to prevent any duplicates being entered. I know the table won't accept any duplicates but when my button closes the form, it doesn't tell you that the record has not been written. Interestingly, you do get a warning when using the X close rather than a simple form close command button.

Dim strSearch as String
varX as Variant

strSearch = Part_Number.Text

varX = DLookup("Part_Number", "Components", "' " & strSearch & "'")
MsgBox varKey

If Not IsNull(varX) Then

If MsgBox("This Part Number already exists." & vbNewLine & vbNewLine & _
"Do you wish to continue to create a new component entry?", vbYesNoCancel) = vbYes Then
Me.Undo
Part_Number.SetFocus
Else
Cancel = True
Me.Undo
End If

End If

Thanks you wonderful people

 

[H Pepper]

Maybe it is not your DLookup that doesn't work. Could be the Not Is Null ....

If Not IsNull(varX) Then

Try

If Not Isnull(varX) or varX Not ""

Just a thought .......

 

[Mile-O]

Dim strSearch as String
varX as Variant

Dim varX As Variant

However, why a Variant? I don't know what type of field Part Number is but I'm guessing it is Text therefore you would be better to dimension this variable as a String.

strSearch = Part_Number.Text

Drop the .Text part - you need to set the focus into the control before you can make use of this property.

varX = DLookup("Part_Number", "Components", "' " & strSearch & "'")

Enclose Part_Number in square brackets and remove that unnnecessary space in the criteria section. The criteria needs a field name too. I would also add aNz() function since you have no guarantee the DLookup() will be successful.

varX = Nz(DLookup("[Part_Number]", "Components", "[Part_Number] = """  & strSearch & """"))

MsgBox varKey

Where is varKey dimensioned?

If Not IsNull(varX) Then

    "Do you wish to continue to create a new component entry?", vbYesNoCancel) = vbYes Then

Your question is a Yes or No question. vbYesNo will suffice as the Cancel is redundant.

 

[TedMartin]

Thank you - the problem also seemed to be with the nested If statement.

Any thoughts on why the X close gives a duplicate warning and why a simple cmd close button does not?

Ted

 

[Mile-O]

What's the warning?

 

[TedMartin]

The Warnng

Its a standard Access warning say that it cannot write the record as it would create a duplicate record.

It would be nice to get this warning from my cmd button that is set to close the form.