Record look up

[Data69]

I am a beginner and have problem with a record look up. I have created a form with an unbound text box. I want to enter a problem number in the box. I am trying to create three outcomes. 1. I receive a message if the box is empty. 2. if I put in a problem number I want to query a table to see if it exists, If it exists I get a message. 3. if it doesn't exist I want to open another form. My problem is that I can not get the Dlookup to run. What is wrong witht he code. I have included all the code.

Private Sub number_Enter()
If IsNull(Me!number) Then
MsgBox "ServiceCenter Problem Ticket is a Required Field.", 48
Me!number.SetFocus


Else
If IsNull(DLookup("[number]", "ServiceCenterProblem", "[number] = '" + Me!number + "'&quot) Then
MsgBox "Problem Ticket already exists, Please enter another Ticket.", 48
Me!number.SetFocus
End If

End If
End Sub

 

[ajdesalvo]

From Access Help:
Number Property
Returns or sets a numeric value specifying an error. Number is the Err object's default property.

You should rename the number field, something like ServiceNum.

Also, is number a Integer or a String? If it is an Integer then you don't need the single quotes;

If IsNull(DLookup("[number]", "ServiceCenterProblem", "[number] = '" + Me!number + "'&quot) Then

Should be:

If IsNull(DLookup("[number]", "ServiceCenterProblem", "[number] = " + Me!number + " &quot) Then

Good luck!
Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[Data69]

Number is a text String because it is the following Format "P0000000". I have tried entering in a number I know exists and it does not return the error. Should I use the seek method here? I have been struggling witht his and am at a loss here. Do I need to open the table in order to query it?

 

[ajdesalvo]

Yikes, I missed your problem the first time!

If IsNull(DLookup("[number]", "ServiceCenterProblem", "[number] = '" + Me!number + "'&quot) Then
MsgBox "Problem Ticket already exists, Please enter another Ticket.", 48

Should be:

If Not IsNull(DLookup("[number]", "ServiceCenterProblem", "[number] = '" + Me!number + "'&quot) Then
MsgBox "Problem Ticket already exists, Please enter another Ticket.", 48


Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[Data69]

that was it. thanks Now I want to open a nother Form if it the number doesn't exist. Do I run a DoCmd?

 

[ajdesalvo]

Yes, DoCmd.OpenForm "YourFormName" will do it! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"