Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    18

    Unanswered: Using Access 2007 and 2 Listboxes With Information Being Imported In

    Good Afternoon,

    I am very new to VBA and I am working on a Tracking System that will allow me to add information received from our clients and post that information into an Access Database named TrackingSystem3. The problem that I am having is on my forms. I have 2 Listboxes name ErrorCodeDescription and ErrorCodesandCorrections where i wrote 2 seperate query statements to pull that information in and allow the user to select more than one opition. I have set the Multi Select tab under others to Extended so that If i wanted to pick more than one opition it will allow me to do that. Also the name of the fields in my Tracking Database are as dollows:ID, Assigned To, Error Code Description, Error Codes and Correction, Open Date, Agreements and Type of Communication.

    I wrote the following code thinking this would help me but its still not working. Here is the following code. I have a few command statements on my form but the one in particular that is suppose to help me is AddRecord. When I click AddRecord and its suppose to add the new record whatever i selected is suppose to populate the Table and its not working

    Is there anyone out there that is willing to help me because at this point i dont know what else to do. Thanking You In Advance.......

    Private Sub AddRecord_Click()
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim varItem As Variant
    Dim ErrorCodeDescription As ListBox
    Dim ErrorCodesandCorrections As ListBox

    On Error GoTo ErrorHandler

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("TrackingSystem3", dbOpenDynaset, dbAppendOnly)

    'make sure a selection has been made
    If Me.ErrorCodeDescription.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Error Code"
    Exit Sub
    End If

    'make sure a selection has been made
    If Me.ErrorCodesandCorrections.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Error Code and Corrections"
    Exit Sub
    End If

    'add selected value(s) to table
    Set ctl = Me.ErrorCodeDescription
    For Each varItem In ctl.ItemsSelected
    rs.NewAdd
    rs!ErrorCodeDescription = ctl.ItemData(varItem)
    rs!ErrorCodesandCorrections = ctl.ItemData(varItem)
    rs.Update
    Next varItem

    SQL = "INSERT INTO tblTrackingSystem3 (ErrorCodeDescription, ErrorCodesandCorrections) " & _
    "VALUES (ErrorCodeDescription.Column(0),ErrorCodesandCorre ctions.Column(0));"
    Exit Sub

    db.Execute SQL, dbFailOnError

    ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

    ErrorHandler:
    Select Case Err
    Case Else
    MsgBox Err.Description
    DoCmd.Hourglass False
    Resume ExitHandler
    End Select

    End Sub
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by Reabernathy View Post
    SQL = "INSERT INTO tblTrackingSystem3 (ErrorCodeDescription, ErrorCodesandCorrections) " & _
    "VALUES (ErrorCodeDescription.Column(0),ErrorCodesandCorre ctions.Column(0));"
    Exit Sub

    db.Execute SQL, dbFailOnError
    I can tell you two issues in this part.

    1) You have the Exit Sub before the db.Execute so the sql statement will never be ran

    2) The ErrorCodeDescription.Column(0) statement is inside the quotes so it's going to be treated as a string rather than the value from the list.
    Last edited by sps; 08-02-12 at 11:01.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •