Help debugging code
I have been working on and off with Access and VBA for about three years. I like tryi g to develop programs to use at work. I have recentlyh purchased the Access VBA for beginners and learned quite a bit. However, I still have much to learn. Anyway to get to the point,
I have a search form to serarch for employees that allows me to select the employee and have them show up in another form where I can edit the data. My problem is getting that info into the form. I am getting an error that says:
"-2147217904 no value given for one or more parameters"
The code I am using is below. I have tried several things, but can't seem to get anywhere. I thought it was in the SQL, but the code to have all employees appear in the frmEmployees form works great. However, when I want to be selective, using the info from the frmEmployeeSearch, it does not work. Help.
Option Compare Database
Option Explicit
Dim blnAddMode As Boolean
Dim blnAllRecords As Boolean
Dim rsEmployees As ADODB.Recordset
Dim objEmployees As clsEmployees
Const EMPLOYEES_FORM = "frmEmployees"
Dim intCurrEmployeeRecord As Integer
Private Sub Form_Load()
On Error GoTo HandleError
Set objEmployees = New clsEmployees
Set rsEmployees = New ADODB.Recordset
'not in add mode
blnAddMode = False
'make sure unclosed is enabled by default so only unclosed records load first
togShowUnclosed.Value = True
togShowAll.Value = False
chkUnlisted.Value = False
chkERT.Value = False
txtEmpID.Locked = True
txtShift.Locked = True
txtRecordId.Visible = False
'lock project id field so no edits allowed to it (primary key assigned by database)
txtRecordId.Locked = True
'load the records in the recordset and display the first one on the form
Call LoadRecords
Exit Sub
HandleError:
GeneralErrorHandler Err.Number, Err.Description, EMPLOYEES_FORM, _
"Form_Load"
Exit Sub
End Sub
Sub LoadRecords()
On Error GoTo HandleError
intCurrEmployeeRecord = 0
If Not IsMissing(lngEmployeeLookupId) And lngEmployeeLookupId > 0 Then
'populate the main recordset
Set rsEmployees = objEmployees.RetrieveEmployeesFromSearch
Else
blnAddMode = False
'populate the main recordset
Set rsEmployees = objEmployees.RetrieveEmployees(blnAllRecords)
End If
'if the recordset is empty
If rsEmployees.BOF And rsEmployees.EOF Then
Exit Sub
Else
'populate the shift and Classification combo boxes
Call PopulateComboBoxes
'populate the object with values in the recordset
objEmployees.PopulatePropertiesFromRecordset rsEmployees
Call MoveToFirstRecord(intCurrEmployeeRecord, rsEmployees, objEmployees, blnAddMode)
'populate the controls on the form with the current record
Call PopulateEmployeesControls
End If
Exit Sub
HandleError:
GeneralErrorHandler Err.Number, Err.Description, EMPLOYEES_FORM, "LoadRecords"
Exit Sub
End Sub
Function RetrieveEmployees(blnAllRecords As Boolean) As ADODB.Recordset
On Error GoTo HandleError
Dim strSQLStatement As String
Dim rsEmpl As New ADODB.Recordset
'build SQL statement to retrieve data
strSQLStatement = BuildSQLSelectEmployees(blnAllRecords)
'generate the recordset
Set rsEmpl = ProcessRecordset(strSQLStatement)
'return the populated recordset
Set RetrieveEmployees = rsEmpl
Exit Function
HandleError:
GeneralErrorHandler Err.Number, Err.Description, CLS_EMPLOYEES, "RetrieveEmployees"
Exit Function
End Function
Function RetrieveEmployeesFromSearch() As ADODB.Recordset
On Error GoTo HandleError
Dim strSQLStatement As String
Dim rsEmpl As New ADODB.Recordset
'build SQL statement to retrieve data
strSQLStatement = BuildSQLSelectEmployeesFromSearch
'generate the recordset
Set rsEmpl = ProcessRecordset(strSQLStatement)
'return the populated recordset
Set RetrieveEmployeesFromSearch = rsEmpl
Exit Function
HandleError:
GeneralErrorHandler Err.Number, Err.Description, CLS_EMPLOYEES, "RetrieveEmployees"
Exit Function
End Function
Function BuildSQLSelectEmployees(blnAllRecords As Boolean) As String
On Error GoTo HandleError
'generate SQL command to retrieve employees records
Dim strSQLRetrieve As String
'if option to display all records is selected in toggle button
If blnAllRecords Then
strSQLRetrieve = "SELECT * FROM tblEmployees " & _
"ORDER BY txtLName, txtFName, txtMInitial"
Else
'look up particular Employees record
strSQLRetrieve = "SELECT * FROM tblEmployees " & _
"WHERE blnActive = true " & _
" ORDER BY txtLName, txtFName, txtMInitial"
End If
BuildSQLSelectEmployees = strSQLRetrieve
Exit Function
HandleError:
GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC, _
"BuildSQLSelectEmployees"
Exit Function
End Function
Function BuildSQLSelectEmployeesFromSearch()
On Error GoTo HandleError
'generate SQL command to retrieve employees records
Dim strSQLRetrieve As String
strSQLRetrieve = "SELECT tblEmployees.intRecordID, tblEmployees.lngEmpID, tblEmployees.txtEmpName, " & _
"tblEmployees.lngSSN, tblEmployees.txtFacility, tblEmployees.txtShift, tblEmployees.txtLName, " & _
"tblEmployees.txtFName, tblEmployees.txtMInitial, tblEmployees.txtScheduleName, tblEmployees.txtAKA, " & _
"tblEmployees.txtUSERNAME, tblEmployees.txtRDO, tblEmployees.txtClassification, tblEmployees.txtHomePhone, " & _
"tblEmployees.txtCellPhone, tblEmployees.blnUnlisted, tblEmployees.txtTrainingType, tblEmployees.txtEMAIL, " & _
"tblEmployees.blnChemAgtExempt, tblEmployees.blnSCBAQual, tblEmployees.blnERT, tblEmployees.txtOtherExempt, " & _
"tblEmployees.lngSeniorityHours, tblEmployees.lngNESScore, tblEmployees.lngSeniorityTieBreaker, tblEmployees.blnActive " & _
"FROM tblEmployees " & _
"WHERE tblEmployees.lngEmpId = lngemployeelookupid " & _
"ORDER BY txtLName, txtFName, txtMInitial"
BuildSQLSelectEmployeesFromSearch = strSQLRetrieve
Exit Function
HandleError:
GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC, _
"BuildSQLSelectEmployeesFromSearch"
Exit Function
End Function
Function ProcessRecordset(strSQLStatement As String) As ADODB.Recordset
On Error GoTo HandleError
'open the connection to the database
Call OpenDbConnection
'create a new instance of a recordset
Dim rsEmpl As New ADODB.Recordset
'set various properties of the recordset
With rsEmpl
'specify a cursortype and a lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'populate the recordset based on SQL statement
.Open strSQLStatement, cnConn
'disconnect the recordset
.ActiveConnection = Nothing
'sort the recordset
End With
'close the connection to the database
Call CloseDbConnection
'return the recordset
Set ProcessRecordset = rsEmpl
Exit Function
HandleError:
GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC, _
"ProcessRecordset"
Exit Function
End Function
Sub OpenDbConnection()
On Error GoTo HandleError
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\testDbEmployeeInfo.mdb;"
'create a new connection instance and open it using the connection string
Set cnConn = New ADODB.Connection
cnConn.Open strConnection
Exit Sub
HandleError:
GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC, _
"OpenDbConnection"
Exit Sub
End Sub
|