Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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




Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:11 AM.


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