listbox based on form recordsource? 1

[thatguy]

Hey all..
I'm trying to create a form which will allow searching on multiple/various fields. I have several unbound controls and a search button which builds a SQL string based on the controls that have data. The form's recordsource prop is then set to the SQL statement. This all works just fine - I can see the correct records in textboxes, BUT I would like to show the resulting records in a listbox and I can't - for the life of me - figure out how to specify the fields in the rowsource property! In the SQL statement, I've specified field names for all columns (SELECT quote.cquoteid AS QuoteID...) and in the listbox, I've tried setting rowsourcetype = FieldList and rowsource to QuoteID... to no avail.

Could someone Please lend a hand? Thank you!

-- michael~

 

[psemianonymous]

The simplest (dirtiest) way is this:

"fieldname1";"fieldname2";"fieldname3";

The important thing is to surround the field names with quotes. Listboxes may be filled with EITHER the results of a query OR a simple comma/semicolon delimited list.

Of course, instead you could just go to the listbox properties and select "Row Source Type" -> Field List, then fill in your row source. It should then return the field list you want. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[thatguy]

Thanks for the reply, but I must be missing something.

I start out with the Form.RecordSource set to "SELECT field1 As quoteid, field2 As customer...", the listbox RowSourceType = "Fields" and the listbox RowSource = "quoteid";"customer";"status"; Still, when I go into Form view, the listbox is empty. I click the search button, which builds a SQL string based on control values, then that Sub ends with: Forms(0).RecordSource = cSQLSt. Yet, the listbox is still empty. What could I be missing?

-- michael~

 

[psemianonymous]

Sorry, I had two answers. #1 was to set the Row Source Type to Value List and type in your answers. I'll go into more detail on #2:

1. Open your form in Design view.
2. Open the form properties box.
3. Go to the Data tab.
4. Copy (using the cut/copy/paste type of copy) the DataSource to the clipboard.
5. Close the properties box.
6. Click on the listbox.
7. Open the properties box for the listbox.
8. Go to the Data tab
9. Set the Row Source Type to "Field List".
10. Go down one line to the Row Source.
11. Paste data from the clipboard.

Save. This should work. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[thatguy]

Ok.. I moved the SQL statement to the listbox instead of the Form's recordsource property. I'm not entirely sure why I moved it there in the first place... But while it was there, the field names (quoteid, status, etc...) came up in the ControlSource drop-down for the listbox, so they were known to the form, which makes me wonder why I couldn't specify those fields in the RowSource of the listbox when they Type was Field List. ??

This is really just a curiosity question now. Thanks for the help.

-- michael~