Using combo box twice in same form

[WrHn85]

First time didn't take, I'll try again here...

My Contractor form captures information about the consultants doing projects for us. We track the company they work for (cboVendorID) but they can also bill through their own prof. corporation (cboPayableID), so we track both in tblContractor.

I was using 2 tables (tblVendor & tblPayable) but keeping them in sync was goofy, so now I want to use tblVendor as row source for each, but bind each cbo to the appropriate field in the Contractor table. I can set the row source fine but for some reason can't get my head around how to set the join/relationship.

Can you set more than one join between 2 tables like that? The problem is, if I need a query to show VendorID/VendorName and also PayableID/PayableName it doesn't. Would I have to make a little query for PayableID/PayableName and then use it in the main ContractorDetails query? brainfreeze

thanks for any light you can shed on this!

 

[Oldsoftboss]

In the relationship window set up the relationships with both cbo fields. When designing a query and both tables are in the query window, you will see both relationship lines. Just highlight and delete the relationship you DONT want in the query. This does not effect the main database relationships.

When setting up the relationships initially, click on the join type and select the option that say "show all records from... your main table.... and only those from the related table.

Dave

 

[WrHn85]

Thanks for your reply, Dave. I have a few situations like that in the db so I'll make them all outer joins.

The other thing about these queries is that most of them so far have large numbers of fields for comprehensive reports or for merge letters. So I've got some big honking queries in there. (new db is about 35 tbl, none over 1000 records, RI in place)

Someone's post a while back said having a bunch of tiny queries was better than a couple of huge ones - is that always the case? I would have thought it would use more effort for Access to run all the queries separately inside the main query.

Thanks for the help - I'm learning tons from this forum!!