Results 1 to 2 of 2
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Displaying multiple values from Recordset in a Text Box control..?

    I'm currently using the below code to take a variable from a Combo Box and use it to pull back a related value.
    The value is then stored in a Text Box on that same form.
    The issue I'm having is that there are some cases in which more than 1 record would be returned from the SQL String.
    In this instance, how would I go about displaying all of these records on the form?

    Any help is appreciated. Thanks!

    ========================================
    Private Sub Test()


    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim sSQL As String
    Dim ServerParam As String
    Set db = CurrentDb
    Dim rs As Recordset


    'On Error Resume Next


    ServerParam = [Forms]![frmRef_CADV_OP_Level_Mapping]![cboCARoles].Column(0)




    sSQL = " SELECT tblRef_CADV_Op_Levels.CSOL_NAME " & _
    " FROM tblRef_CADV_Op_Levels " & _
    " INNER JOIN (tblRef_CADV_Roles INNER JOIN tblRef_CADV_OP_Level_Role_Xref " & _
    " ON tblRef_CADV_Roles.CR_ID = tblRef_CADV_OP_Level_Role_Xref.CARLX_CR_ID) " & _
    " ON tblRef_CADV_Op_Levels.CSOL_ID = tblRef_CADV_OP_Level_Role_Xref.CARLX_CSOL_ID" & _
    " WHERE [tblRef_CADV_Roles].[CR_NAME]='" & ServerParam & "';"



    Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)


    With rs
    strOpLevel = !CSOL_NAME
    End With

    [Forms]![frmRef_CADV_OP_Level_Mapping]![txtCAOPLevels] = strOpLevel

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's a LOT of code for what you're describing. You cannot return multiple records to a textbox. The closest you can come is to concatenate values, perhaps separating them with commas. However, not only may a listbox be more appropriate, but your whole approach may be kind of wonky. Usually multiple records associate with a single value are represented by a subform in either datasheet or continuous format.

    I'll wager you don't have Option Explicit turned on (or at least have it in your module) because you haven't declared strOpLevel (or it isn't obvious that you did). Lastly, please use code tags for anything more than a few lines - see # on thread menu bar.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Load values from a text box into a recordset
    By CanuckBuck in forum Programming
    Replies: 15
    Last Post: 01-19-2016, 08:56 AM
  2. Replies: 2
    Last Post: 08-02-2013, 01:38 AM
  3. Replies: 1
    Last Post: 01-06-2013, 01:47 PM
  4. Displaying Multiple Values, Storing One.
    By greatfallz in forum Forms
    Replies: 10
    Last Post: 08-02-2011, 01:18 AM
  5. Displaying multiple text values in crosstab query
    By MFlood7356 in forum Queries
    Replies: 6
    Last Post: 06-28-2011, 11:30 AM

Tags for this Thread

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