Trouble extracting field from table based on 2 criteria

[elsandalos]

Hi all,
Been pulling my hair out about this for a while, so i hope someone's feeling kind:

I'm using the BHRefPrefix to build part of a reference number. The Principal and Site exist on a separate form, and i need to use both as criteria in order for the correct BHRefPrefix to be returned.

I sort of get it working using two joins, but it doesn't let me add any new records. I've tried straight queries, queries with DLookup expressions, and i just can't get what i need.

Pete

 

[Mile-O]

=[Forms]![FormName]![ControlName]

As the criteria in the query.

 

[elsandalos]

=[Forms]![FormName]![ControlName]

As the criteria in the query.

ok .. but what about the joins in the query? I need two joins really (Principal and Site from one table to another), and i can't set any primary keys on it because none of the columns contain unique values.

Pete

 

[Mile-O]

Can you describe what tables you have and what fields are joined/related between the tables?