Showing a group of records

[Kevin Field]

Can someone suggest a way to do this?

I have a form which has a combo box in the form header.

Bascially i want to be able to select a record from the combo box (in this case a book ref no) and when selected it will show all client who are in that particul;ar book.

The clients come from a seperate table and are linked to the bookref no mentioned above.

I have been trying various ways but all that happens is it homes in on the first record in the long list which has that ref no.

What i want to happen is that it singles out all of the records with that ref and shows only them.

Any ideas?

 

[vbaInet]

What you can do is:

1. Create a query that will be the form's Record Source and include Book Ref as one of the fields
2. In the criteria of Book Ref refer to the ID part of the combo box, which is usually column 0. If it is the first column then you can write the criteria like this:

[Forms]![ComboBoxName]

However, this wouldn't show all records when nothing is selected in the combo box, so you can change that to:

LIKE IIF(IsNull([Forms]![ComboBoxName])), "*", [Forms]![ComboBoxName])

I'm not a fan of the LIKE operator so the other way is to actually build your sql string and append the WHERE clause to the SELECT part of the query, then set the record source of the form to that newly built query and requery the form. Here's how:

http://baldyweb.com/BuildSQL.htm