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

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,
Who is Participating?
jefftwilleyConnect With a Mentor Commented:
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.
LenaWoodConnect With a Mentor Commented:
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.
cazinkAuthor Commented:
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.
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.

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,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.