transfer items slected on a list box to form with a subdatasheet

Posted on 2006-06-06
Last Modified: 2008-03-10
Actually this is a 2 part question:
I am very comfortable using code to manipulate forms and reports. However the code I am using is not advanced enough to allow me to manipulate files or records which do not appear on the screen in a form. The type of code I speak of is for example, the code I acquired which adds the record to my table when a limit to list property is used. An example is below:
Private Sub ModelName_ID_NotInList(NewData As String, response As Integer)
Dim rst As Recordset
Dim Msg As String
Dim UserResponse As Integer
NewData = UCase(Mid(NewData, 1, 1)) & Mid(NewData, 2)
Msg = NewData & " is not on the list." & vbCr & "Would you like to add it?"
UserResponse = MsgBox(Msg, vbYesNo + vbQuestion, "Please Verify")
If UserResponse = vbYes Then
Set rst = CurrentDb.OpenRecordset("tblModel")
With rst
    ![model description] = NewData
End With
response = acDataErrAdded
Set rst = Nothing
    response = acDataErrContinue
End If
End Sub
Even though I understand what is going on, I have not had a need, until now, to undertake experimenting in this direction. Now the first question.
1) Conceptually, I would like to know a general series of steps I should take to manipulate data as in the above fashion. Should I always dim the current db then make a recordset etc. The preferred method of good code writing is what I am seeking. What I am afraid of is breaking the data I have now.
second question
2) I have a form with a subform that has a datasheet view. I am doing estimate takeoff. So on the subform datasheet a new record is added to each line as the user picks from a combo box on each line. Since the items selected are usually the same or similar, I would like to have a pop up list box of the items a user can pick from (multiple selection property on) and when the list box is closed the items which were selected are written to the subform.
This may be alot to ask, but I am looking for direction more than just a solution.
many thanks in advance,
Question by:cazink
    LVL 10

    Assisted Solution

    I did something very similar in a database - I didn't add my records to a subform, but in anycase you will need to add them to a table so they can be shown in the subform.  Here is the code I used...of course you will have to adapt it to your use:

    Private Sub cmdAddAllSiteItems_Click()

        Dim i As Integer
        With Me.lstNonAppSite
            For i = .ListCount - 1 To 0 Step -1
                .Selected(i) = False
                Call AddSite(.ItemData(i))
            Next i
        End With
        Me.txtAgenApp = -1
        Me.txtSiteApp = -1
        Me.txtSiteID = -1

    End Sub

    Public Sub AddSite(SiteNum)

            Dim strInsertInto As String
            Dim strSelect As String
            strInsertInto = "INSERT INTO tblSiteApplicability ( SiteRecID, FunctionQuestionRecID) "
            strSelect = "SELECT " & SiteNum & ", " & Me.FunctionQuestionRecID
            CurrentDb.Execute strInsertInto & strSelect

    End Sub

    Hope this gets you pointed in the right direction.
    LVL 34

    Accepted Solution

    Hi cazink,
    You open the door for lots of input here when asking about best practices. The truth is, the simplest solution is almost always the best. If you're good at SQL, then that's probably what you should use. Recordsets are my preferred method, simply because of their transactional nature, meaning, I can commit my changes or back them out if things don't go so well. Lots of people like to use queries because they are easy to use and pretty versatile. Best practices depends on what youre trying to save. Money vs time, quality vs quantity. reuse over disposable code. Ideally, we'd never write a function more than once. We'd simply feed our core code variables all day and it did the trick. That's not the real world though. I know I didn't truely answer your question, but no one can but you. Access and VBA are very powerful and useful tools, within limits. Writing code that's easy to read, easy to use, and that includes how you manage data, is only really important to you...and your boss. Just my $.02.

    Author Comment

    I split the points between the two of you. However, I was a little disappointed in that the both of you only addressed one answer each. The first answer seemed to take a minute to answer with little attempt to address the question and then added some copied code. I think the intent was to receive the points. However, the second person really gave it a shot at giving a little direction and therefore received the majority of the points.
    But thanks to you both.
    LVL 34

    Expert Comment

    The points in and of themselves aren't that important to me. Trying to help you find the answers you are looking for is. I am going to attempt to try to answer once again specifically what you addressed on both points...and I hope that if you have questions or would like more explanation, that you please ask.

    In the case where your user is entering data through a list box and it's found that the item is not on the list. that generally has two optcomes. If the underlying source for the list is a complex query, that recordset can't be edited. In that case, the code above is creting a recordset that CAN be edited and adding the entry. If the dropdown source was simply a table, then the item can be inserted using a SQL insert statement.

    Part two
    When you create your subform, you can design a comboBox with the source as whatever you want it to be. You can do this using the ComboBox wizard. Tell it the recordsource, tell it that you want to lookup the values in a table or query. go to the next screen. Select the source and click next. The next screen will prompt you to select the values that you want to appear when you select the dropdown in the combo box. the next screen allows you to size the columns you've just chosen. The next screen allows you to choose the column where your value will come from that will eventually populate your datasheet form. On the next screen...tell it where you want to store the value. In your case, that's the name of the field that you're trying to populate using this method. Select finish, and you're done. When you view this form now in datasheet view, you have a dropdown in that field where you can select a value from and it's stored in the underlying table in the field you specified.
     Hope this helps.
    LVL 10

    Expert Comment


    I am sorry I didn't address both of your questions...I answer questions as I can from work.  I was going by your statement that you wanted direction, not a solution therefore I gave you code that I had used and worked for me.

    As for Jeff only answering 1 question as his defense, why answer part of the question that I already addressed.  I am sorry that you were disappointed in your response from us.  As it is with Jeff, I don't care about the points...I answer questions for two help me learn more about Access and the most important reason to help people such as yourself.

    Good luck in your project,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now