Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 4th, 2006, 05:51 PM
Registered User
 
Join Date: Aug 2006
Location: , Michigan, .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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








Similar Threads
Thread Thread Starter Forum Replies Last Post
Help Debugging fs22 Javascript 3 May 17th, 2005 10:13 AM
Debugging BSkelding VB.NET 2002/2003 Basics 1 June 16th, 2004 10:32 PM
Changing code while debugging. ltdanp21 VS.NET 2002/2003 5 June 16th, 2004 06:11 PM
Need help debugging.... reg03 Beginning PHP 5 January 27th, 2004 03:51 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.