Link to home
Start Free TrialLog in
Avatar of CaptainGiblets
CaptainGibletsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access forms, list / text box control.

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
Avatar of rockiroads
rockiroads
Flag of United States of America image

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

eg

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?

Avatar of CaptainGiblets

ASKER

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.
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
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.
Any one have any ideas? Need to do this ASAP
ASKER CERTIFIED SOLUTION
Avatar of Computer101
Computer101
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial