Results 1 to 8 of 8
  1. #1
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28

    Display table data depending on selected data

    Basically what I want to do is have a combo box in which users can select a project from a list. (This comes from the "Projects" table)


    After the project is chosen, I would like to display some other information about that project that is from other columns in the table.

    This seems like something that should be pretty simple to do, I just have no idea how to go about it.

    If I need to give any more information, I'd be happy to give it to you.

    Thanks!

  2. #2
    offie is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    176
    I think what you want is something similar to what I have done, I had:
    Text boxes to enter criteria into a search query
    Checkboxes to select fields from the search query and build a new results query in VBA
    End Product: The results query would have only the specified fields but also only the records that were specified in the search query from the texboxes in the same form

    So what you would have to do is make a normal seach form and on the same form add checkboxes, then on the on-click of a command button enter the VBA

    This is the code I used, you would obvioulsy change it a bunch
    Code:
    Private Sub ProjectSearchCommand_Click()
     
    DoCmd.OpenQuery "ProjectSearchQuery"
    DoCmd.Close acQuery, "ProjectSearchQuery"
    DoCmd.Close acQuery, "ProjectSearchResults"
     Dim strSQL As String
        strSQL = "SELECT "
        
    '   Check ProjectCode box
        If Check1 = -1 Then
            strSQL = strSQL & "[ProjectCode],"
        End If
        
    '   Check Trade box
        If Check2 = -1 Then
            strSQL = strSQL & "[Trades],"
        End If
        
    '   Check Title box
        If Check5 = -1 Then
            strSQL = strSQL & "[Title],"
        End If
        
    '   Check Status box
        If Check9 = -1 Then
            strSQL = strSQL & "[StatusCode],"
        End If
        
    '   Check ClientAgency box
        If Check6 = -1 Then
            strSQL = strSQL & "[ClientAgencyCode],"
        End If
        
    '   Check FundingAgency box
        If Check7 = -1 Then
            strSQL = strSQL & "[FundingAgencyCode],"
        End If
        
    '   Check AcceptDate box
        If Check13 = -1 Then
            strSQL = strSQL & "[AcceptDate],"
        End If
        
    '   Check BidDate box
        If Check14 = -1 Then
            strSQL = strSQL & "[ProjectBidDate],"
        End If
        
    '   Check CompDate box
        If Check15 = -1 Then
            strSQL = strSQL & "[ProjectCnstCompleteDate],"
        End If
        
    '   Check EICName box
        If Check10 = -1 Then
            strSQL = strSQL & "[EICFullName],"
        End If
        
    '   Check Remark box
        If Check12 = -1 Then
            strSQL = strSQL & "[Remark],"
        End If
        
    '   Check TeamLeaderNamebox
        If Check11 = -1 Then
            strSQL = strSQL & "[TLFullName],"
        End If
    '   Check ChangeOrders box
        If Check3 = -1 Then
            strSQL = strSQL & "[ChangeOrders],"
        End If
        
    '   Check Contractors box
        If Check8 = -1 Then
            strSQL = strSQL & "[Contractors],"
        End If
        
    '   Check BonusWithTrades box
        If Check16 = -1 Then
            strSQL = strSQL & "[BonusAmountAllTrades],"
        End If
        
    '   Check Field Orders box
        If Check4 = -1 Then
            strSQL = strSQL & "[FieldOrders],"
        End If
        
    '   Check TotalContractAmount box
        If CheckTOtalCA = -1 Then
            strSQL = strSQL & "[SumOfContractAmount],"
        End If
        
    '   Check ContractAmountByTrade box
        If CheckCAByTrade = -1 Then
            strSQL = strSQL & "[ContracAmountByTrade],"
        End If
            
    '   Check to make sure that they have made at least one selection
        If Right(strSQL, 1) <> "," Then
            MsgBox "You have not selected any fields to show.", vbOKOnly, "Please select one."
            Exit Sub
    '   If they have, drop the final comma and add From clause
        Else
            strSQL = Left(strSQL, Len(strSQL) - 1) & " FROM [ProjectSearchQuery];"
        End If
        
    '   Do view code before applying, uncomment line below
    ''  MsgBox strSQL
        
    '   Assign SQL code to Query
        CurrentDb.QueryDefs("ProjectSearchResults").SQL = strSQL
        
    '   Open query to view results
        DoCmd.OpenQuery "ProjectSearchResults", acViewNormal, acEdit
    End Sub
    Edit: I think I just made it way too complicate do you want to have the same fields show up every time?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Parameterized query is one way to filter records. Review:

    http://datapigtechnologies.com/flash...tomfilter.html

    http://datapigtechnologies.com/flash...earchform.html

    http://datapigtechnologies.com/flash...mtoreport.html

    Other methods would not use parameterized query but instead involve more code to apply filter to form or move to a record on form or as in offie's example, modify the query definition of a saved query object.

    I don't prefer parameterized query objects. I usually use WHERE CONDITION argument of DoCmd.OpenReport - in VBA code something like:
    DoCmd.OpenReport "report name", , , "[fieldname]=" & Me.textboxname
    Use delimiters in the criteria if text or date:
    "[fieldname]='" & Me.textboxname "'"
    "[fieldname]=#" & Me.textboxname "#"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28
    If I were to use your preferred option, how would I do that in a form, rather than a report?

  5. #5
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28
    I should probably have specified that this is in a form.

  6. #6
    swavemeisterg is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Posts
    28
    I honestly am just really intimidated by this method. I really don't know how to work with the SQL stuff. Essentially I want a combo box on a form that has the serial numbers for products. Depending on which is chosen, I want some text fields to populate with information from the product table corresponding to the chosen SN

  7. #7
    offie is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    176
    The middle link from June7, to make a search form, do exactly that but with a combobox. The only this that is different is that the prompted name is Combo1 instead of Text1. You don't have to work with SQL, access does that for one in these simple cases.

  8. #8
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Why not simply let Access do all the work? You may have done some of this already.

    • Create a Form using your Table/Query as the Record Source.
    • Go to the Field List and drag the Fields you want retrieved onto the Form
    • Place a Combobox on the Form.

    When the Combobox Wizard comes up:

    • Select "Find a record on my form based on the value I've selected..."
    • Click on Next
    • Select the appropriate identifying field (your Project field) to appear in the Combobox
    • Click on Next
    • Size the Combobox column
    • Click on Next
    • Name the Combobox
    • Click on Finish

    Now the user can use the dropdown arrow of the Combobox to retrieve the data or simply start typing the identifying data in and the Combobox will start to autocomplete the entry. When the correct entry is found hit <Enter> and the appropriate data should be retrieved.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 7
    Last Post: 10-15-2012, 11:05 PM
  2. Replies: 11
    Last Post: 07-24-2012, 07:50 PM
  3. Display previous quarter data when quarter is selected
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 07-03-2012, 03:12 PM
  4. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  5. Replies: 1
    Last Post: 05-25-2010, 02:58 PM

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