A
Anita
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
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