Access forms,  list / text box control.

Posted on 2007-07-27
Last Modified: 2013-11-28
Hi all, Im wondering if its possible to set this up in an access form, and how i would go about doing it.

What i want to do is search a database using an sql query, and if there is more than 2 results it will put the results in a list box, if there is only 1 set of results it will put the results into seperate txt boxes. The use needs to be able to pick the record in the list box and it will put the selected records into the text box.

Any help appreciated
Question by:CaptainGiblets
    LVL 65

    Expert Comment

    yes, what u can do is this

    first define rowsource of the listbox eg

    select field1, field2, field3, field4 from mytable

    use columnwidths properties to hide/unhide fields

    now create a click event in your listbox which reads the columns and sets the textboxes accordingly


    private sub mylistbox_click()
        me.txtbox1 = me.mylistbox.column(0)
        me.txtbox2 = me.mylistbox.column(1)
        me.txtbox3 = me.mylistbox.column(2)
        me.txtbox4 = me.mylistbox.column(3)
    end sub

    when u run the query, afterwards, simply call the listbox click event to populate textboxes

    How are you proposing to run this sql query?
    You can use this sql query as the rowsource for the listbox
    How does this sql query get created/run?

    LVL 6

    Author Comment

    i get the results doing this.

    Dim sqlrep As String
    sqlrep = "Select * from Table "
    sqlrep = sqlrep & " where table.[Sales Person] = Forms!form!txtsalesrepreport.Value "

    at a click of a button with some one typing a value in a text box.

    I have no idea how to add a click event onto a list box as i dont know much about them.
    LVL 65

    Expert Comment

    ok, how many fields are we talking about here?
    One of the easiest ways for you to do this is

    Say your form is based on table A, so you have all the fields defined that you want.

    Now add a listbox to this form. Ensure you have the toolbar wizard pressed (2nd button)
    when u add a listbox to your form, you will get a dialog with 3 options. If you select the third ("find record based on listbox"), then click next, just follow the wizard.

    Now what you have is each time u select an item from the listbox, it loads the details for you. Ensure you pick primary column first.

    Now the rowsource for your listbox, just tweak it to add in your filter in rowsource
    LVL 6

    Author Comment

    how can i do a search like so.

    ill search an database for say a certain colour (blue)   so if i find more than 1 record of blue it puts all its findings into a list box.  if it only finds 1 blue i can put all its info into different text boxes.

    i just need to know how to do the if statement.
    LVL 6

    Author Comment

    Any one have any ideas? Need to do this ASAP
    LVL 1

    Accepted Solution

    PAQed with points refunded (500)

    EE Admin

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now