tblB (buyers)
buyerID, First, Last
b01, Bob, Smith
b02, Susie, Quinn
tblJ (jobs)
jobID, buyerID (buyerID is a lookup field on tblB)
j01, b01 (bobs job 1)
j02, b02 (susies job 1)
j03, b01 (bobs job 2)
tblC (charges)
buyerID, jobID, amount (buyerID is a lookup field on tblB, I want jobID to be a lookup on tblJ where buyer ID matches the input buyer on tblC)
b01, ____ >I want a drop down with J01 & j03, but no j02 since that's not Bob's job
I want user to input a charge against a buyer, and after that buyer is selected from drop down, I want that charge assigned to one of that buyer's job. I am looking for this to be a defined requirement in the table field lookup row source criteria, but it will not query down the results with a WHERE clause.
trying something like this in tblC jobID field lookup row source:
SELECT tblJ.jobID, tblJ.buyerID FROM tblJ WHERE (((tblJ.jBuyer)=([tblC]![buyerID])));
But not working...
It's like the select statement can't recognize the tblC buyerID that has already been selected by the user?
I know how to fix it in a form, but because of the architecture I'm using I want it defined in the table. Any help please????!!!
Thanks, Marcus