recordset open not working - run-time error

[Guest]

Despite listing all parameters required to open a recordset, I keep getting a
run-time error. Can anyone tell me what I'm doing wrong?

Dim conCompany As Connection
Dim rstCompany As Recordset
Dim intCompanyID As Integer
Dim intEntityID As Integer
Dim strEntityName As String
Dim strSQL As String
Stop
strEntityName = Me.Entity
intEntityID = Me.Entity_ID
intCompanyID = Me.[Company ID]

Set conCompany = CurrentProject.Connection
Set rstCompany = New Recordset

' Create new record in table Company
With rstCompany
.Source = "Select * from Company"
.ActiveConnection = conCompany
.LockType = adLockOptimistic
.Open
End With

If intCompanyID = 0 Then 'If Entity Company ID_
' is null

'and if this isn't a person or production
If Me.[Person ID] = 0 Then
If Me.[Production ID] = 0 Then
rstCompany.AddNew ' create new company_
'record
rstCompany![Company] = strEntityName
rstCompany.Update
intCompanyID = rstCompany![Company ID].Value 'Put company_
' Id of record into variable
Me.[Company ID].Value = intCompanyID
rstCompany.Close
End If
End If

'If Entity already has a company, open existing record
Else
rstCompany.Close
Set conCompany = CurrentProject.Connection
Set rstCompany = New Recordset
strSQL = "Select * from Company WHERE " & _
" [Company Id] = intCompanyID "

With rstCompany
.Source = strSQL
.ActiveConnection = conCompany
.LockType = adLockOptimistic
.Open ' *** Run-time error
happens here ***
![Company] = strEntityName
.Update
End With
End If

Set rstCompany = Nothing
Set conCompany = Nothing

End Sub

 

[RoyVidar]

Anita wrote in message

Despite listing all parameters required to open a recordset, I keep
getting a run-time error. Can anyone tell me what I'm doing wrong?

Dim conCompany As Connection
Dim rstCompany As Recordset
Dim intCompanyID As Integer
Dim intEntityID As Integer
Dim strEntityName As String
Dim strSQL As String
Stop
strEntityName = Me.Entity
intEntityID = Me.Entity_ID
intCompanyID = Me.[Company ID]

Set conCompany = CurrentProject.Connection
Set rstCompany = New Recordset

' Create new record in table Company
With rstCompany
.Source = "Select * from Company"
.ActiveConnection = conCompany
.LockType = adLockOptimistic
.Open
End With

If intCompanyID = 0 Then 'If Entity Company ID_
' is null

'and if this isn't a person or production
If Me.[Person ID] = 0 Then
If Me.[Production ID] = 0 Then
rstCompany.AddNew ' create new company_
'record
rstCompany![Company] = strEntityName
rstCompany.Update
intCompanyID = rstCompany![Company ID].Value 'Put
company_ ' Id of record into variable
Me.[Company ID].Value = intCompanyID
rstCompany.Close
End If
End If

'If Entity already has a company, open existing record
Else
rstCompany.Close
Set conCompany = CurrentProject.Connection
Set rstCompany = New Recordset
strSQL = "Select * from Company WHERE " & _
" [Company Id] = intCompanyID "

With rstCompany
.Source = strSQL
.ActiveConnection = conCompany
.LockType = adLockOptimistic
.Open ' ***
Run-time error happens here ***
![Company] = strEntityName
.Update
End With
End If

Set rstCompany = Nothing
Set conCompany = Nothing

End Sub

What error message are you getting?

One guess - try replacing the last select with

strSQL = "Select * from Company WHERE " & _
" [Company Id] = " & intCompanyID

i e - putting the value of the value into the string, not the variable
name.


I would probably replace the last section with

strSql = "update company set company.company = '" & _
replace(strEntityName, "'", "''") & "'"
conCompany.Execute strSql,,adcmdtext+adexecutenorecords

in stead of opening a recordset.

Also, I like disambiguating my declarations, so in my code, you'd found

Dim conCompany As ADODB.Connection
Dim rstCompany As ADODB.Recordset

Set rstCompany = New ADODB.Recordset

where both you and Access will know that you mean ADO, and not DAO,
should both references be selected.

 

[Guest]

Thanks a million - problem solved

Anita wrote in message

Despite listing all parameters required to open a recordset, I keep
getting a run-time error. Can anyone tell me what I'm doing wrong?

Dim conCompany As Connection
Dim rstCompany As Recordset
Dim intCompanyID As Integer
Dim intEntityID As Integer
Dim strEntityName As String
Dim strSQL As String
Stop
strEntityName = Me.Entity
intEntityID = Me.Entity_ID
intCompanyID = Me.[Company ID]

Set conCompany = CurrentProject.Connection
Set rstCompany = New Recordset

' Create new record in table Company
With rstCompany
.Source = "Select * from Company"
.ActiveConnection = conCompany
.LockType = adLockOptimistic
.Open
End With

If intCompanyID = 0 Then 'If Entity Company ID_
' is null

'and if this isn't a person or production
If Me.[Person ID] = 0 Then
If Me.[Production ID] = 0 Then
rstCompany.AddNew ' create new company_
'record
rstCompany![Company] = strEntityName
rstCompany.Update
intCompanyID = rstCompany![Company ID].Value 'Put
company_ ' Id of record into variable
Me.[Company ID].Value = intCompanyID
rstCompany.Close
End If
End If

'If Entity already has a company, open existing record
Else
rstCompany.Close
Set conCompany = CurrentProject.Connection
Set rstCompany = New Recordset
strSQL = "Select * from Company WHERE " & _
" [Company Id] = intCompanyID "

With rstCompany
.Source = strSQL
.ActiveConnection = conCompany
.LockType = adLockOptimistic
.Open ' ***
Run-time error happens here ***
![Company] = strEntityName
.Update
End With
End If

Set rstCompany = Nothing
Set conCompany = Nothing

End Sub

What error message are you getting?

One guess - try replacing the last select with

strSQL = "Select * from Company WHERE " & _
" [Company Id] = " & intCompanyID

i e - putting the value of the value into the string, not the variable
name.


I would probably replace the last section with

strSql = "update company set company.company = '" & _
replace(strEntityName, "'", "''") & "'"
conCompany.Execute strSql,,adcmdtext+adexecutenorecords

in stead of opening a recordset.

Also, I like disambiguating my declarations, so in my code, you'd found

Dim conCompany As ADODB.Connection
Dim rstCompany As ADODB.Recordset

Set rstCompany = New ADODB.Recordset

where both you and Access will know that you mean ADO, and not DAO,
should both references be selected.