Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    results from list box multi-selection to field? (2002)

    I am working on a book database that has a table [Books] with a field [language]. Some books are written in multiple languages and the book database will be used by people who speak different languages. A multi-selection list seems to be a good way to deal with the problem. I created a sub form called [sfrmLanguage] that opens when a "button" called language is selected. The sub form contains a box displaying a list [lstLanguage] based on a table called tblLanguage, a control button to close the form without changes and a control button that should write a string to the language field of the current record that is a concatination of the abbreviations for the languages selected separated by /. tblLanguage contains the fields > index field, abr, english, french and german fields. note the english, french, and german fields list the names of each of the languages in the table (~20) in that language.

    The following code for the control button correctly (except for a leading /) creates the concatinated string based on the selections from the list:

    Dim lst As Access.ListBox 'lstLanguage is location of data
    Dim varItm As Variant 'counter - number of list items selected
    Dim strLan As String 'concatinated 3 letter appreviation for languages in column 1

    Set lst = Me![lstLanguage]
    For Each varItm In lst.ItemsSelected
    strLan = strLan & "/" & lst.Column(1, varItm)
    Next varItm

    As a beginning Access programmer, I am unable to figure out how to write the results of the concatinated string to the current field "language" in the table Books. I have not been able to find any thing at works in the several Access books I have or in information I find online - many things I try result in an error message "object required".

    What is the correct way to get my concatinated string into a field in the current record?

    Thanks for any help!!!!!!!

    Bob Holmstr

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: results from list box multi-selection to field? (2002)

    You could do that with some effort, but it would bite you because multiselect lists will allow you to select multiple items in a record, but they won't display those items when you come back to that record. Furthermore, multiple facts in a single field is poor design and will also trip you up somewhere along the line. The correct relational way to design this is to have a separate table that records a single record for each language you select for each book rather than having language in the books table. The Book_Language table would contain the unique identifier for the the book record and a language indicator for each language that applies. Instead of using a listbox on a subform, use a continuous subform to display all the records relating to the current book in the parent form. If that seems unclear, post back with questions.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: results from list box multi-selection to field? (2002)

    Charlotte,

    Thanks for your reply. I am a rank amateur at Access but it seems to me that there are places where a multi-select list box can be used to advantage even though it seems to be little used and may be considered poor practice. I did not have much difficulty writing the code to collect the information I need to write to a field in the current record (that part works as determined by watching the lower VBA window) - I am just having difficulty "writing" the information to the appropriate field in the current record of the Books table.

    I agree that I could use a continuous subform to display the list of selected languages and I have used that technique elsewhere in the project but I chose not to for several reasons in this case. 1. A continuous subform takes more space that that required for a simple text box holding 2 or 3 - 3 letter abbreviations. Entering the data using several openings of a combo box takes far longer than placing several clicks in a multi-selection list box. 3. There are already more than 5000 entries in the database where the language information is displayed as lang1/lang2/lang3 in a single text box. The "customers" are quite pleased with that method - they just want to speed up the data entry process by selecting the languages from a list and to improve accuracy by having a consistent set of appreviations.

    I also want to use the same technique (multi-select list box) in another place in the project - keywords for the documents. The requirements are that the keywords are selectable from a reccommended list and are displayable (selection and display) in three languages (English, French, and German). I can easily display a 3 column multi-select list and using a modified version of the code above, create 3 strings that contain the selected keyword lists in english, french and german. If each string is written to the appropriate field in the database record than is is displayed on a tabbed subform at the bottom of the page labled English, French, German. Searches for records using wildcard substrings works very well. This seems to be a very simple way to create what at first glance appears to be a complicated requirement.

    Yes, I understand that putting several "facts" in the same box may not be correct database practice but it seems to work very well in this case.

    So, is it possible to tell me how to write a string to a field in the current record?

    Thanks

    Bob

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: results from list box multi-selection to field? (2002)

    You will run into difficulty with your scheme when the client wants to analyze the data for all books where the language is English or German but not French, or something similar, because you can't easily stratify the data that way. That, however, is your problem to deal with if you choose to put the values into a single field. Keep in mind, though, that you can *display* multiple values in a single control on a form without actually storing the data that way.

    As for writing the value to a field, the easiest way is to include the field on the form (it doesn't have to be visible) and simply set its value to the string I understand you to have created. If you need more detailed instructions, post back.
    Charlotte

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: results from list box multi-selection to field? (2002)

    Ok, I figured out how to do it on my own.

    The following code looks at a multi-select list box, combines the language appreviations in column 1 of the list box with a "/" in between, strips the leading "/", and writes the results to a field called language in a table called books.

    Private Sub Update_Language_Click()
    On Error GoTo Err_Update_Language_Click

    Dim dbs As DAO.Database
    Dim rstBooks As DAO.Recordset
    Dim lst As Access.ListBox 'lstLanguage is location of data
    Dim varItm As Variant 'counter
    Dim strLan As String 'concatinated 3 letter appreviation for languages in column 1

    Set dbs = CurrentDb
    Set rstBooks = dbs.OpenRecordset("Books", dbOpenDynaset)
    Set lst = Me![lstLanguage]
    For Each varItm In lst.ItemsSelected
    strLan = strLan & "/" & lst.Column(1, varItm)
    Next varItm
    strLan = Right(strLan, Len(strLan) - 1) 'strips leading /
    rstBooks.Edit
    rstBooks!Language = strLan
    rstBooks.Update

    DoCmd.Close acForm, "sfrmLanguage"

    Exit_Update_Language_Click:
    Exit Sub

    Err_Update_Language_Click:

    MsgBox Err.Description
    Resume Exit_Update_Language_Click

    End Sub

    Bob

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: results from list box multi-selection to field? (2002)

    I think your terminology needs to be cleaned up to get the best response. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> You are using the term "subform", which has a very specific meaning in Access, when what you are describing is a popup form. You're liable to get confused and confusing questions and answers if you use non-standard names for what you're doing. Since it's quite possible to show and hide a true subform, I hadn't quite twigged to the fact that you were actually using something else and that your selections weren't being made in the form itself. Anyhow, I'm glad you figured out something that works for you. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Charlotte

  7. #7
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: results from list box multi-selection to field? (2002)

    Charlotte,

    I spoke too soon - the "solution" that I posted only works for the first record in the database. If I change the language for a different record in the database, the language field for the first record is changed not the language field in the current record.

    You are correct in concluding that what I was really talking about is a "pop-up" form. I tried your suggestion of putting the language field on the pop-up form and ran into several problems:

    1. The field list is not available when I am in design view for the pop-up form.
    2. If I create a text box and put the field name as the control source - the only thing the box displays is ?Name.
    3. If I put the field on the main form it displays correctly but then if I cut and paste it to the pop-up form I get the same ?Name.
    Note: The field is called "Language" and it is in a Table called "Books"

    Extra information about the database that may help explain why I am having difficulty:
    1. The database is split into front end and back end (It seemed a good idea to this beginner after reading Helen Feddema's book, but I am beginning to question the wisdom of the decision). The files are BiblioMundi.mlb and BiblioMundi_be.mdb.
    2. When I run some sample code from "Beginning Access 2002 VBA" to determine how many records are in the table I get an answer of 1 even though there are more than 5000 records in the database. The code example is listed below:
    --------------
    Public Sub OpeningARecordset()

    Dim db As Database
    Dim rec As Recordset
    Dim intRecords As Integer

    Set db = CurrentDb()
    Set rec = db.OpenRecordset("Books")

    intRecords = rec.RecordCount
    MsgBox "There are " & intRecords & " records in the books table"
    rec.Close

    End Sub
    -----------------------

    Any ideas about how to resolve my problem? Does the fact that the database is split into front end / back end change the way code needs to be written for the front end?

    Bob Holmstr

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: results from list box multi-selection to field? (2002)

    <hr>1. The field list is not available when I am in design view for the pop-up form.<hr>
    I don't understand what you mean. Which field list? I'm getting awfully confused by what you're trying to accomplish here. I would suggest you pare down the database and post a zipped mdb with the tables and forms involved in this question. If you're using a continuous form with an unbound combobox on it, that could be part of the problem, but since you appear to be very new to Access, we may wind up miscommunicating because terms that one of us uses may mean something different to the other person. It would simplify things a lot if you posted a sample.

    Split databases are *always* the way to go. They avoid a whole bunch of problems like the fact that in any version of Access later than 97, you *must* have an exlusive lock on the database in order to make design changes. The data file normally bloats much less than the front end, so splitting the files also reduces the potential for hitting the upper limit on file size in the back end.

    You code doesn't work as you expected because when you open an recordset, you're at the beginning and you didn't specify the kind of recordset you're opening, so you got a forward only snapshot. A recordcount of zero means there are none. A recordcount of 1 means there are records. You have to open a dbDynaset or a dbSnapshot and then do a MoveLast in order to get the actual count. It has absolutely nothing to do with a split database.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: results from list box multi-selection to field? (2002)

    Books aren't always right, but there may be other reasons for its failure. Without being able to see the context, it's impossible for me to guess at the problem. As far as the problem you're having with the popup form, you're going to have to post the code that inserts the selected languages into the record on the main form. Otherwise, no one else can tell which form you're running it from and the possible reasons for the wrong results. If you happen to be opening a recordset from the popup form and making the change that way, then you aren't navigating to the correct record before updating its data.
    Charlotte

  10. #10
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: results from list box multi-selection to field? (2002)

    <P ID="edit" class=small>(Edited by WendellB on 29-Jan-04 21:40. Image cropped to prevent excessive scrolling)</P>Charlotte,

    The field list for the table in the main form was not available i.e. "greyed out" - I fixed the problem by making the record source for the pop-up form the appropriate table and now I can place the field on the pop-up form. I will try to pare down the database and post it later (it is currently 664K frontend / 5.2MB rearend after repair and compaction). In the meantime I have included a screen shot showing the main form with the pop-up form open. Note that even though the main form shows record #3 which is in german, the pop-up form is showing Eng which is the language of record #1. If I select from the multi-select language list - changes are made to record #1 not to the current record. Somehow I need to link the pop-up form to the current record.

    Yes, I will stick with the split database.

    I am puzzled about the code mentioned above not working. It works exactly as suggested in the "ice cream database" from Beginning Access 2002 VBA - i.e. it gives the correct number of records without any of the additional actions you say are necessary. In fact the book states that it should work as long as it is used on tables. The same code does not work in my data base (after changing the table name) - I thought that might be a clue to solving my problem.

    Thanks for your help and patience on this problem.

    Bob Holmstr
    Attached Images Attached Images

  11. #11
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: results from list box multi-selection to field? (2002)

    Charlotte,

    I have already posted the code that I am running and that I am running it from the pop-up form which is obviously wrong since the pop-up form has no idea what the current record is in the main form that I am trying to update and therefore always updates the first record. I have tried to explain that several times but as an Access VBA beginner It is difficult.

    You clearly understand what the problems is - you said "If you happen to be opening a recordset from the popup form and making the change that way, then you aren't navigating to the correct record before updating its data." How to you navigate to the correct record?

    Thanks for your patience.

    Bob Holmstr

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: results from list box multi-selection to field? (2002)

    As to your question, you need to use the current record information from the other form and find the matching record in the recordset before you make the changes. Actually, the simplest way would be to open a recordset that only contained the record you needed. Then there would be no need to "navigate" anything. The current record on the other form has a primary key, I assume, so you would look for that in the recordset opened by the popup form and then only make changes if you find the matching record. If you would like to post a link to the other thread or even just include the post number where you posted your code, I can be more specific about how to do that.

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> You aren't talking about the code you posted in <post#=333954>post 333954</post#> are you? That doesn't have anything to do with a recordset. Are you aksing how to *open* a recordset to write that information?
    Charlotte

  13. #13
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: results from list box multi-selection to field? (2002)

    Charlotte,

    Sorry, I understand that you are looking at lots of messages and I was not specific about where the code I attempted to use from the pop-up form was posted. The code is in the 5th post to this thread (post #334631).

    How do I open a record set that only includes the record I need?

    Bob Holmstr

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: results from list box multi-selection to field? (2002)

    Try something along these lines:

    Dim strSQL As String

    strSQL = "SELECT * FROM Books WHERE BookID = " & Forms!BookForm!BookID

    Set rstBooks = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    I'm assuming here that there is a primary key on Books that uniquely identifies the book and that the value is on the form displaying the book information. It can be in a visible or invisible control on that form, but it will always be the one associated with the current record. I'll warn you that if you're using Title as the primary key, you'll have problems since books by entirely different authors turn up with the same title, especially over time. If you're using an ISBN or something, you'll need to substitute that in the SQL statement and you'll need to correct the name of the form and the control being referenced to return the value. If you use this approach, the recordset should have only a single record in it because you have selected the record for that specific book only.
    Charlotte

  15. #15
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: results from list box multi-selection to field? (2002)

    Charlotte,

    Thank you! Your suggested code worked perfectly. Yes, I had a Primary Key for each book that uniquely itentified the each entry in the books table.

    Thanks again for your patience!

    Bob Holmstr

Posting Permissions

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