ListBox Problem

[DS]

I have a Listbox that I need to fill based on two tables.
Table1
PaymentID Number
PaymentName Text
PaymentType Number

Table2
PrivlegeID Number
Cash Yes/No
CreditCard Yes/No
HouseAccount Yes/No

I need to populate the listbox with Table1 and I can only populate the
listbox with PaymentNames that are checked Yes in Table2.

The PaymentName field in Table1 are of the same ames in Table2.
Cash CreditCard HouseAccount

I'm not quit sure how to filter Table1 based on Table2

Any help appreciated.

Thanks
DS

 

[Guest]

If you can join the two tables on some field, create a query filtered by the
values in the yes/no fields and use that query as the row source of your list
box.

There is a fundamental flaw in your table design. You have 3 fields where
you should have only 1. The 3 yes/no fields should be 1 numeric Long
Interger fields. It could be called PayMethod. You should create another
table (tblPayMethods) that has 3 fields.
tblPayMethods
[PayMethod] - Autonumber - PK
[MethodDescr] - Text - Description (HouseHold, Cash, etc.)
[Active] - Yes/No (Yes = Okay to use this method)

Then your PaymentType field in Table1 should be an FK to tblPayMethods.
Here are the advantages:

If you add new payment types, you don't have to redesign your table or
modify your code. You only add a record to tblPayMethods and it works.
If you no longer accept a type of payment, you change the [Active] field
from True to False for that payment type and it will no longer be accepted.
You do, of course need to code for that. The usual method would be to use a
combo with a row source filtered for only Active payment types.
The coding to check one field instead of 3 (more later, maybe) is much
simpler.