Database Lock when performing a dlookup

[Data69]

I am trying to perform a DLookup to see if a record exists before I append a table. Any help to why my code is returning the error is greatly appreciated.

Error
The database has been placed in a state by user 'admin' in which it prevents it from being opened or locked.



Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

With rst
.Open "ServiceCenterProblem", CurConn, , , adCmdTableDirect
If IsNull(DLookup("[number]", "ServiceCenterProblem", "[number] = '" + Me!problem_number + "'&quot) Then
MsgBox "Service Number Already Exists, Please enter a new Problem Ticket.", 48
Me!problem_number.SetFocus
.Close

 

[chrissie1]

first thing

what are you using ado or doa

mdb or adp

because the code you are using is trying to use a bit of both but nothing really good Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[ssecca]

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

Try this??

Good Luck
ssecca

 

[Data69]

I am using ADO. I am using the same code with in the same database and it works just fine. The only difference is the dlookup is adding a new record if the value IsNull. I want to add this code to another command button to look for the record before executing a query look up and adding a record.

 

[vbajock]

What line are you getting the error on.

Set CurDB = CurrentDb - not needed in ADO, delete it
adCmdTableDirect - change to adCmdTable use adCmdTableDirect only if you are using an index
I don't see enough code to see why you are appending a null
I assume you have checked the startup properties to make sure you are opening the database in shared mode.

Perhaps not all your code is here, but why are you closing the recordset if the dlookup fails?:

With rst

.Open "ServiceCenterProblem", CurConn, , , adCmdTableDirect

If IsNull(DLookup("[number]", "ServiceCenterProblem", "[number] = '" + Me!problem_number + "'&quot) Then
MsgBox "Service Number Already Exists, Please enter a new Problem Ticket.", 48
Me!problem_number.SetFocus

---> .Close

 

[Data69]

I am not sure if my approach is correct. I am opening a form that has an unbound text box. I want to enter a Problem_number. Once the number is entered I want to select a command button that will: look at my internal database to see if the record exists. if the record exists I want to return an error that the record exists. If the record is not internal I want to open a query that will go out to an external database and pull customer information. I figured the Dlookup would query my internal database for existing records. Maybe I am approaching this wrong.

 

[vbajock]

The text box the user enters data into has an event called "After Update". This event is fired when the user hits the enter key or moves off the field with the mouse after he has entered new data.

In the After Update event, put the code:


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

MsgBox "Service Number Already Exists, Please enter a new Problem Ticket.", 48

Me!problem_number.SetFocus

else

'Number is not null so do something else

end if