[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access forms,  list / text box control.

Posted on 2007-07-27
Medium Priority
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
  • 3
  • 2
LVL 65

Expert Comment

ID: 19581195
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?


Author Comment

ID: 19581216
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

ID: 19581370
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

ID: 19581932
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.

Author Comment

ID: 19583906
Any one have any ideas? Need to do this ASAP

Accepted Solution

Computer101 earned 0 total points
ID: 19984862
PAQed with points refunded (500)

EE Admin

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

834 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