why does this code give me fits?

[Frederick Wilson]

Hello anyone and all,

The code below causes and error that says something about the select
statement is in error. HOWEVER, it only happens some of the time. It is
not too consistent.

I thing I have corrected the error by removing the .Source reference
from the table directly to a variable string "select * from rtbleventtype"

My question is why does it error out with the table reference but not
with a query reference? This code is executed for a NotInList event. Is
it possible it fails because the table it references is open elsewhere?

Which leads to this question, should I be basing my forms off of the
tables directly or via a query?

Thank you,
Fred


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

With rs
.ActiveConnection = cn
.Source = "rtbleventtype"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Open
End With
rs.AddNew
rs![Event] = NewData
rs![TngTypeCode] = DMax("[TngTypeCode]", "rtbleventtype") + 1
rs.Update
rs.Close
Set rs = Nothing

Response = acDataErrAdded

 

[Dan Artuso]

Hi,
On what line does it error out? What is the exact error message?

I would suggest using the Open method with the Options argument set to:
adCmdTable

Something like:
rs.Open "rtbleventtype", cn, adOpenStatic, adLockOptimistic, adCmdTable

See if that makes a difference.

 

[Frederick Wilson]

I'll take a look at this, thanks,

Fred

Hi,
On what line does it error out? What is the exact error message?

I would suggest using the Open method with the Options argument set to:
adCmdTable

Something like:
rs.Open "rtbleventtype", cn, adOpenStatic, adLockOptimistic, adCmdTable

See if that makes a difference.

--
HTH
Dan Artuso, Access MVP

Hello anyone and all,

The code below causes and error that says something about the select
statement is in error. HOWEVER, it only happens some of the time. It is
not too consistent.

I thing I have corrected the error by removing the .Source reference
from the table directly to a variable string "select * from rtbleventtype"

My question is why does it error out with the table reference but not
with a query reference? This code is executed for a NotInList event. Is
it possible it fails because the table it references is open elsewhere?

Which leads to this question, should I be basing my forms off of the
tables directly or via a query?

Thank you,
Fred


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

With rs
.ActiveConnection = cn
.Source = "rtbleventtype"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Open
End With
rs.AddNew
rs![Event] = NewData
rs![TngTypeCode] = DMax("[TngTypeCode]", "rtbleventtype") + 1
rs.Update
rs.Close
Set rs = Nothing

Response = acDataErrAdded