Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011

    Unanswered: Creating multiple records from a List Box

    Howdy all! I am rather new to Access and am looking for some help for a database I'm building for work.

    I have a form that imports data from an excel sheet to a temporary table in my database. From that table I load the first column "Loan number" into a list box on a second form.

    Now, on that second form, I want the user to be able to select all or some of the entries in the listbox and then fill out a few text boxes (In this case there are four "date received","date loaded", "time received" and "time loaded") and I want to create a button that will then create entries into a loan table that will save each of those loan numbers as a seperate record and will put the values from the text boxes in each record created.

    I know almost nothing about coding or macros so I'm not even really sure where to start as far as doing this. Can any of you help?

  2. #2
    Join Date
    Oct 2009
    if one is designing databases; one needs a textbook - easily found at Amazon or any large book store.

    am not sure your question is of code/macro - it would seem that all will work with just a normal form sourced on a table; possibly with a subform/table.
    www CahabaData com

  3. #3
    Join Date
    May 2011
    I have several textbooks here at my disposal. They don't go into great detail on a lot of the more advanced features of Access however which is why I've come to these forums.

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 15
    First you need to set the MultiSelect property of the ListBox to Simple (1) or Extended (2) (see Access help for the differences between both values), then you can cycle through the ItemsSelected property of the ListBox to retrieve the selected values. From Access help:
        Dim ctl as Control
        Dim varItem as Variant
        Set ctl = Me.List1
        For Each varItm In ctl.ItemsSelected
             Debug.Print ctl.ItemData(varItm)
         Next varItm
    If you need to retrieve the values from more than one column you can loop through the Column property:
        For Each varItm In ctl.ItemsSelected
            For I = 0 To ctl.ColumnCount - 1
                Debug.Print ctl.Column(I, varItm)
            Next intI
        Next varItm
    For saving various values of controls to a table, you can build a SQL statement and have it executed:
    Private Sub Cmd_CreateRecords_Click()
        Dim varItem As Variant
        Dim strSQL As String
        For Each varItem In Me.List1.ItemsSelected
            ' Values from List1 and Text2 are of type Text while value from Text1 is numeric.
            strSQL = "INSERT INTO Table_1 ( Field1, Field2 Field3 ) " & _
                     "VALUES ( '" & Me.List1.ItemData(varItem) & "', " & Me.Text1.Value & ", '" & Me.Text2.Value & "' );"
            CurrentDb.Execute strSQL, dbFailOnError
        Next varItem
    End Sub
    Have a nice day!

Tags for this Thread

Posting Permissions

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