Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    7

    Angry Unanswered: Insert Records with List box

    I am trying to configure an input screen that will allow the user to put records into a system. The system must keep a record for each test that is performed even for the same person. I am using a listbox for the test names. I am setting up a loop that will allow for this insert to occur for each item selected in the listbox box. However I am running into a situation where I have used a variable for the test name, but the sql statement is looking for a control with the name that the variable contains. Any suggestions?? Code below:


    Dim frm As Form
    Dim ctl As Control
    Dim varItm As Variant
    Dim TName As String
    Dim IntI As Integer

    Set frm = Forms!frmDrugTesting
    Set ctl = frm!lstTestName

    For Each varItm In ctl.ItemsSelected
    For IntI = 0 To ctl.ColumnCount - 1
    TName = CStr(ctl.Column(1))
    DoCmd.RunSQL ("Insert Into tblTestResult (ClientKey,
    Vendor, CollectionDate, TestResult, TestName, RefRange,
    Method) VALUES (cmbClientName, cmbVender,
    txtCollectionDate, cmbTestResults, " & TName & ",
    cmbRange, cmbMethod)")
    Next IntI
    Next varItm

  2. #2
    Join Date
    Jul 2002
    Posts
    7
    Just Got It Thx all

  3. #3
    Join Date
    Jul 2002
    Posts
    7
    In case anyone was working on something similar ~ Here is the code I used to configure this.


    Dim frm As Form
    Dim ctl As Control
    Dim varItm As Variant
    Dim TName As String
    Dim IntI As Integer

    Set frm = Forms!frmDrugTesting
    Set ctl = frm!lstTestName

    For IntI = 0 To ctl.ListCount - 1
    TName = ctl.ItemData(IntI)
    If ctl.Selected(IntI) Then

    'Add Record to New Client Table
    DoCmd.RunSQL ("Insert Into tblTestResult (ClientKey,
    Vendor, CollectionDate, TestResult, TestName, RefRange, Method)
    VALUES (cmbClientName, cmbVender, txtCollectionDate,
    cmbTestResults, """ & TName & """, cmbRange, cmbMethod)")
    End If
    Next IntI


    Thx again

Posting Permissions

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