narrow selections in row source

Hello all! I'm a bit of a newbie and I'm tearing my hair out trying to figure out how to narrow down the choices on a form's drop down list. Basically, I have a database (yes, it's a class assignment) that keeps track of cds and dvds among a group of friends that borrow the media among themselves. I have a table for the cds, one for dvds, one for the members, one for cd transactions, and one for dvd transactions. In the forms, I have one for each of the tables, and in the cd and dvd transaction forms there is a selection for borrower. I'm trying to make it so the cd owner cannot be selected as a borrower. I'm thinking I can do this with "row source" in properties (which currently reads "SELECT [Member].[ID], Member.[LastName], Member.[FirstName] FROM Member;"). Is there a way I can alter that to not include the media's owner, or am I barking up the completely wrong tree? Any help would be greatly appreciated!

Is there some reason you have chosen to treat CD's and DVD's separately? I would think they could all be in one table and a field in that table could tell you if it is a CD or a DVD.

Possibly dependent (cascading) comboboxes are relevant. Review http://www.datapigtechnologies.com/f...combobox2.html

Include the OwnerID field from CD_DVD table as a column in the CD_DVD combobox RowSource. Reference that column as filter criteria in the Member combobox. Column index begins with 0. If the OwnerID is in column 2, the index is 1

SELECT ID, Lastname & ", " & FirstName AS MembName FROM Member WHERE ID <> [CD combobox].[Column](1);

I put them into separate tables because the fields they contain are different.

How different?

If you want to provide db for analysis, follow instructions at bottom of my post.

The DVD table has the movie rating and the CD table has the recording artist. They aren't completely different, but I wouldn't want to try to combine them and have a bunch of blank cells within one table.

Just for the record: There is *no* disk penalty for blank fields. Access only allocates disk space when you store something in the field.

So CD table is for music and DVD table is for movies? I think Music and Movies are better table names. Why not rating in Music?

I'm with RG, I can (and often do) tolerate a few empty cells for the sake of convenience. It is a balancing act between normalization and ease of data entry/output.

I would combine the CD and DVD tables and then combine the two Transactions tables, which are identical.

You might also explore the Microsoft Lending Library database template. http://office.microsoft.com/en-us/te...010206883.aspx

Back to your original question.

Properties of DVD (or CD) combobox:
RowSource: SELECT DVD.ID, DVD.Title, DVD.Owner FROM DVD;
ColumnCount: 3
ColumnWidths: 0";2";0"

Properties of Borrower combobox:
RowSource: will have to be set with VBA code because the owner ID in DVD combobox is in column 3 - SQL cannot reference column index
ColumnCount: 2
ColumnWidths: 0";2"

Code in Borrower combobox GotFocus event.
Private Sub Borrower_GotFocus()
Me.Borrower.RowSource = "SELECT Member.ID, [LastName] & ', ' & [FirstName] AS MembName FROM Member WHERE ID<>" & Me.DVD.Column(2) & ";"
Me.Borrower.Requery
End Sub

I was afraid it was going to be a solution requiring VBA! I ended up having to change the layout a bit, my instructor is a sticker for not having empty cells, so I will have to keep that as is, but I'm moving it towards just using the IDs to identify everything in the forms and using names just for the reports. In a real world setting that would be horrible, but it seems like an easier way to deal with the values when it is just a number! I tried adapting the code you listed to no avail, I keep getting a syntax error for a missing operator when I try to update the borrower within the form. If you can help no more I just want to say thank you for at least pointing me in the right direction...which is more than I can say for my instructor! I attached where I'm at now, thanks again!

Could use the Artist field in the movie records for the director or the studio, then there are no empty cells.

You have not configured the comboboxes on Transaction forms as suggested.

I never build lookups in table. http://access.mvps.org/access/lookupfields.htm

I think I'm building the lookups in the form though, not the table. And I'm not seeing where I can change columncount or columnwidth, is that the configuration changes I need to make to the comboboxes?

As noted in the post, those are combobox properties. Located on the Format tab of Properties sheet. RowSource is on Data tab.

Ahh, Ok, I got the DVD row source corrected, but I'm still not seeing where to change columncount or columnwidths. So I take it the VBA code is supposed to be calling on the second row of the DVD combobox for the limitation?

Ahhhh, got it now!! Thank you so much! I was trying to call on column "2" in the code, instead of column "1". I missed that part of the adjustment. Thanks again!