VBA Error

[CarlM1975]

Hi I have this code

Function replace()
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Plaistow Primary School")
Do Until Rs.EOF
Rs.Edit
Rs("Job Details") = replace(Rs("Job Details"), Chr(10), "")
Rs.Update
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
End Function

However I get the message cannot open any more databases on line 2.

I really dont understand it, has anyone come across this before.

Thanks

Carl.

 

[DJkarl]

Replace is the name of a VBA function, try changing the name of your function, VBA might be getting confused.

 

[chergh]

In the sub where you call the function from do you already have an object called 'rs'?

 

[NigelShaw]

Hi,

just a thought. ive always found it good practice to declare the database too-

Set db = CurrentDb()

so i would write it like

Function replace()
Dim Rs As DAO.Recordset
Dim Db As DAO.Database
 
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("Plaistow Primary School", dbOpenDynaset)
 
Do Until Rs.EOF
Rs.Edit
Rs("Job Details") = replace(Rs("Job Details"), Chr(10), "")
Rs.Update
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
End Function

dont know if that would help but line 2 is currently the error line and this is where you are i guess, opening the second database.

Regs,

NS

 

[CarlM1975]

Thanks guys problem solved

 

[chergh]

Care to tell us what solved it? Others may have the same problem in the future and it will save them asking if they can just look at this thread and find the answer.

 

[CarlM1975]

Yeah Soz,

Function swapout()
Dim Rs As DAO.Recordset
Dim Db As DAO.Database

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("Plaistow Primary School", dbOpenDynaset)

Do Until Rs.EOF
Rs.Edit
Rs("Job Details") = replace(Rs("Job Details"), Chr(10), "")
Rs.Update
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
End Function



I had called my function replace which is a function itself as soon as I called it something else the code worked fine.

 

[NigelShaw]

Hi,

I see you chose to edit similar to my detail which is a standard.

Remember, whatever you open you should close.

If you open rs recordset and db database, you should close them both and empty the values to regain memory.

Rs.close
Db.close

Set rs = nothing
Set db = nothing

Regs

Nigel