?
Solved

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

Posted on 2006-06-06
5
Medium Priority
?
355 Views
Last Modified: 2008-03-10
Actually this is a 2 part question:
preface;
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
    .AddNew
    ![model description] = NewData
    .Update
End With
response = acDataErrAdded
rst.Close
Set rst = Nothing
Else
    response = acDataErrContinue
    Me.ModelName_ID.Undo
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,
Charly
0
Comment
Question by:cazink
  • 2
  • 2
5 Comments
 
LVL 10

Assisted Solution

by:LenaWood
LenaWood earned 300 total points
ID: 16844667
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
        .Requery
    End With
    Me.lstAppSite.Requery
   
    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.
Lena
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 1200 total points
ID: 16845090
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.
J
0
 

Author Comment

by:cazink
ID: 16888326
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.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16888587
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.
J
0
 
LVL 10

Expert Comment

by:LenaWood
ID: 16888808
Cazink,

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 well...in 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 reasons...to help me learn more about Access and the most important reason to help people such as yourself.

Good luck in your project,
Lena
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
Suggested Courses

839 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