Ok...so, after some more continuing work on my db, I have debugged the VBA code and...well, I am at my wits end why I am receiving "Data type mismatch" errors. So, here is my first attempt at doing a class module. I am aware that I could use a query to intercede for me, but I want to make this available available fo use throughout my forms, and to have the ability to look up the record through the form, as well as numerous other funtions.
Option Compare Database
Private mrst As ADODB.Recordset
Private mconn As ADODB.Connection
Private mAppID As Long
Private mAppLName As String
Private mAppFName As String
Private mCriteriaType As String
Private mThirdParty As String
Private mActionDate As Date
Private mNotes As String
Private mempID As String
Private mempName As String
Public Sub GetRecord()
'Declare and instantiate one connection object
'and recordset object
Set mconn = New ADODB.Connection
Set mrst = New ADODB.Recordset
'Point the connection object
'to the connection associated with the CurrentProject object
Set mconn = CurrentProject.Connection
'Utilze the connection just opened as the connection for
'the recordset
mrst.ActiveConnection = mconn
'Get record information info from the database
mrst.Open "SELECT tblMain.AppID, " _
& "tblMain.AppLName, tblMain.AppFName, tblMain.criteria, " _
& "tblMain.thirdparty, tblMain.ActionDate, tblMain.Notes, " _
& "tblMain.empID, tblMain.empName " _
& "WHERE tblMain.AppID = '"
'Call the routine Scatter
Call Scatter
'Close the Connection and the recordset
mrst.Close
mconn.Close
'Close the connection and recordset from memory
Set mconn = Nothing
Set mrst = Nothing
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical
Resume Next
End Sub
Public Property Get AppID() As Long
On Error Resume Next
AppID = mAppID
End Property
Public Property Let AppID(ByVal lngAppID As Long)
On Error Resume Next
mAppID = intAppID
End Property
Public Property Get AppLName() As String
On Error Resume Next
AppLName = mAppLName
End Property
Public Property Let AppLName(ByVal strAppLName As String)
On Error Resume Next
mAppLName = strAppLName
End Property
Public Property Get AppFName() As String
On Error Resume Next
AppFName = mAppFName
End Property
Public Property Let AppFName(ByVal strAppFName As String)
On Error Resume Next
mAppFName = strAppFName
End Property
Public Property Get Criteria() As String
On Error Resume Next
HoldType = mCriteria
End Property
Public Property Let Criteria(ByVal strHoldType As String)
On Error Resume Next
mHoldType = strCriteriaType
End Property
Public Property Get ThirdParty() As String
On Error Resume Next
NameCaseWrk = mThird Party
End Property
Public Property Let NameThirdParty(ByVal strThirdParty As String)
On Error Resume Next
mNameCaseWrk = strNameThirdParty
End Property
Public Property Get ActionDate() As Date
On Error Resume Next
ActionDate = mActionDate
End Property
Public Property Let ActionDate(ByVal varActionDate As Date)
On Error Resume Next
mActionDate = varActionDate
End Property
Public Property Get Notes() As String
On Error Resume Next
Notes = mNotes
End Property
Public Property Let Notes(ByVal strNotes As String)
On Error Resume Next
mNotes = strNotes
End Property
Public Property Get empID() As String
On Error Resume Next
empID = mempID
End Property
Public Property Let empID(ByVal strempID As String)
On Error Resume Next
mempID = strempID
End Property
Public Property Get empName() As String
On Error Resume Next
empName = mempName
End Property
Public Property Let empName(ByVal strempName As String)
On Error Resume Next
mempName = atrempName
End Property
Public Sub Scatter()
'Take the field Values for the recordset and fill class variables
With mrst
mAppID = !AppID
mAppLName = !AppLName
mAppFName = !AppFName
mCriteria = !Criteria
mThirdParty = !ThirdParty
mActionDate = !ActionDate
mNotes = !Notes
mempID = !empID
mempName = !empName
End With
End Sub
Public Sub Gather()
'Take the class variables and fill the record Set
With mrst
!AppID.Value = mAppID
!AppLName.Value = mAppLName
!AppFName.Value = mAppFName
!Criteria.Value = mCriteria
!NameThirdParty = mThirdParty
!ActionDate = mActionDate
!Notes = mNotes
!empID = mempID
!empName = mempName
End With
End Sub
Public Sub Update(ByVal lngAppID As Long)
On Error GoTo Err_Handler
'Declare and instantiate one connection object
'to connection associated with the CurrentProject object
Set mconn = New ADODB.Connection
Set mrst = New ADODB.Recordset
'Point the connection object
'to the connection associated with the current project
Set mconn = CurrentProject.Connection
'Utilze the connection just opened as the connection for
'the recordset
mrst.ActiveConnection = mconn
'Set lock type and cursor to make
'the recordset updateable
mrst.Locktype = adLockOptimistic
mrst.cursortype = adOpenDynamic
'Get the Application record being updated
mrst.Open "SELECT * FROM tblMain WHERE tblMain.AppID = '" & strAppID & "'"
'Call the routine to fill recordset
Call Gather
mrst.Update
'close the connection and the recordset
mrst.Close
mconn.Close
'Clear the connection and the recordset
Set mconn = Nothing
Set mrst = Nothing
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical
End Sub
I assumed at first that it had something to do with the data type (hence the data type mismatch error). Any suggestions?
Thanks!
Option Compare Database
Private mrst As ADODB.Recordset
Private mconn As ADODB.Connection
Private mAppID As Long
Private mAppLName As String
Private mAppFName As String
Private mCriteriaType As String
Private mThirdParty As String
Private mActionDate As Date
Private mNotes As String
Private mempID As String
Private mempName As String
Public Sub GetRecord()
'Declare and instantiate one connection object
'and recordset object
Set mconn = New ADODB.Connection
Set mrst = New ADODB.Recordset
'Point the connection object
'to the connection associated with the CurrentProject object
Set mconn = CurrentProject.Connection
'Utilze the connection just opened as the connection for
'the recordset
mrst.ActiveConnection = mconn
'Get record information info from the database
mrst.Open "SELECT tblMain.AppID, " _
& "tblMain.AppLName, tblMain.AppFName, tblMain.criteria, " _
& "tblMain.thirdparty, tblMain.ActionDate, tblMain.Notes, " _
& "tblMain.empID, tblMain.empName " _
& "WHERE tblMain.AppID = '"
'Call the routine Scatter
Call Scatter
'Close the Connection and the recordset
mrst.Close
mconn.Close
'Close the connection and recordset from memory
Set mconn = Nothing
Set mrst = Nothing
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical
Resume Next
End Sub
Public Property Get AppID() As Long
On Error Resume Next
AppID = mAppID
End Property
Public Property Let AppID(ByVal lngAppID As Long)
On Error Resume Next
mAppID = intAppID
End Property
Public Property Get AppLName() As String
On Error Resume Next
AppLName = mAppLName
End Property
Public Property Let AppLName(ByVal strAppLName As String)
On Error Resume Next
mAppLName = strAppLName
End Property
Public Property Get AppFName() As String
On Error Resume Next
AppFName = mAppFName
End Property
Public Property Let AppFName(ByVal strAppFName As String)
On Error Resume Next
mAppFName = strAppFName
End Property
Public Property Get Criteria() As String
On Error Resume Next
HoldType = mCriteria
End Property
Public Property Let Criteria(ByVal strHoldType As String)
On Error Resume Next
mHoldType = strCriteriaType
End Property
Public Property Get ThirdParty() As String
On Error Resume Next
NameCaseWrk = mThird Party
End Property
Public Property Let NameThirdParty(ByVal strThirdParty As String)
On Error Resume Next
mNameCaseWrk = strNameThirdParty
End Property
Public Property Get ActionDate() As Date
On Error Resume Next
ActionDate = mActionDate
End Property
Public Property Let ActionDate(ByVal varActionDate As Date)
On Error Resume Next
mActionDate = varActionDate
End Property
Public Property Get Notes() As String
On Error Resume Next
Notes = mNotes
End Property
Public Property Let Notes(ByVal strNotes As String)
On Error Resume Next
mNotes = strNotes
End Property
Public Property Get empID() As String
On Error Resume Next
empID = mempID
End Property
Public Property Let empID(ByVal strempID As String)
On Error Resume Next
mempID = strempID
End Property
Public Property Get empName() As String
On Error Resume Next
empName = mempName
End Property
Public Property Let empName(ByVal strempName As String)
On Error Resume Next
mempName = atrempName
End Property
Public Sub Scatter()
'Take the field Values for the recordset and fill class variables
With mrst
mAppID = !AppID
mAppLName = !AppLName
mAppFName = !AppFName
mCriteria = !Criteria
mThirdParty = !ThirdParty
mActionDate = !ActionDate
mNotes = !Notes
mempID = !empID
mempName = !empName
End With
End Sub
Public Sub Gather()
'Take the class variables and fill the record Set
With mrst
!AppID.Value = mAppID
!AppLName.Value = mAppLName
!AppFName.Value = mAppFName
!Criteria.Value = mCriteria
!NameThirdParty = mThirdParty
!ActionDate = mActionDate
!Notes = mNotes
!empID = mempID
!empName = mempName
End With
End Sub
Public Sub Update(ByVal lngAppID As Long)
On Error GoTo Err_Handler
'Declare and instantiate one connection object
'to connection associated with the CurrentProject object
Set mconn = New ADODB.Connection
Set mrst = New ADODB.Recordset
'Point the connection object
'to the connection associated with the current project
Set mconn = CurrentProject.Connection
'Utilze the connection just opened as the connection for
'the recordset
mrst.ActiveConnection = mconn
'Set lock type and cursor to make
'the recordset updateable
mrst.Locktype = adLockOptimistic
mrst.cursortype = adOpenDynamic
'Get the Application record being updated
mrst.Open "SELECT * FROM tblMain WHERE tblMain.AppID = '" & strAppID & "'"
'Call the routine to fill recordset
Call Gather
mrst.Update
'close the connection and the recordset
mrst.Close
mconn.Close
'Clear the connection and the recordset
Set mconn = Nothing
Set mrst = Nothing
Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical
End Sub
I assumed at first that it had something to do with the data type (hence the data type mismatch error). Any suggestions?
Thanks!