Results 1 to 3 of 3
  1. #1
    eliasgirl01 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011

    Recordset Help; Not sure how to pass a string to a table field

    A little info about my database: I would like a user to fill out a form and enter it as a record and the records must be completely searchable. One of my fields (TsuApps) on the form is a multi-value field. I like how multiple selections are entered into the field as a string with entries separated by commas (yes, I know this is bad database design, but for my purposes it'll be fine), but you can't search it (multi-values fields aren't subject to WHERE or HAVING clauses).

    My approach: I have adapted a bit of code, where a button is clicked and it reads in the items selected, adds a comma between each entry, and is stored in a string. (or at least I think that's what I'm doing) I want the string to be passed into a table field (TsunamiApps) of the same record so it can be searchable. But whenever I do that, it says the string length is 0. What am I doing wrong?

    Also, if anyone has suggestions about my approach or perhaps a different approach to take, I'm all ears. But go easy, I'm really new to access and I have no idea how to normalize tables so this seemed the easiest route.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Have you debugged? Refer to link at bottom of my post for a tutorial on debugging techniques.

    The loop code is not working. This code was originally designed for a multi-select listbox. Not sure how to adapt to multi-select combobox.

    Your Entry form has an error on ID textbox. Says field does not exist. Fix the ControlSource.

    This procedure can be accomplished without the recordset coding. The TsuApps field is part of the form's RecordSource. Can simply populate the field with the string value (once you figure out how to populate the string variable) but the control would have to be unbound which means you loose the multi-select feature.

    Might have to be a multi-select listbox for any of this to work.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cheesenbranston is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Edinburgh, Scotland
    Hi there,

    Since the control is bound to the table, there is no need for the code you are using. As soon as the record is changed, the changes are saved to the table, just like the other fields on the form.

    The code is only required if you want to perform some other operation with the values in the combo. In this case.....

    You should make the following change in Private Sub AppendTableButton_Click()

    For Each varItem In Me!TsuApps.Value
    strCriteria = strCriteria & ",'" & varItem & "'"
    Next varItem
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If

    I haven't worked with a multi-select combo, but if you inspect the "itemsselected" property of the control you will find it is 0, so nothing gets done in the loop and strcriteria is always ""

    You need to use the "value" property of the combo in the loop.

    Also, as June7 pointed out you need to change the ControlSource property of ID to TestID.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass Variable to Table Field with VBA
    By dgj32784 in forum Programming
    Replies: 3
    Last Post: 03-28-2011, 09:36 PM
  2. Using a string to DIM a Recordset
    By ColPat in forum Programming
    Replies: 10
    Last Post: 09-25-2010, 03:53 PM
  3. String to Copy a Field of Data to Another Table
    By aquarius in forum Programming
    Replies: 1
    Last Post: 09-17-2010, 09:02 AM
  4. Replies: 1
    Last Post: 11-13-2009, 03:03 AM
  5. Replies: 1
    Last Post: 07-31-2009, 03:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums