Lookup Problems

[newtech2]

Background:

Main Contact Table

[Contact ID]
[Contact First Name]
[Contact Last Name]
[Discipline ID] is a look up to the Discipline Table


Discipline Table
[Discipline ID]
[Discipline Name]
[Disc Deactive] yes/no


qryDiscipline: is a query with all fields from Discipline table

Main Contact Query is a query with all fields from Main Contact table


Main Contact Form
Based on Main Contact query, with combo box Discipline ID based on qryDiscipline showing Discipline Name only

The list of Discipline names on the form is getting quite large, so when I edit the row source of Discipline ID to criteria “no” in [Disc Deactive], the drop down is what I want, but records that had the “yes” disciplines no longer show. Aauuug!

I am hoping this fix will also fix a problem I am having with wanting the Discipline name in reports when using the [Discipline ID] field in reports. Not sure if a lookup is the best thing to use if you are planning on using the field for other things later. But, what are the other options?

 

[CosmoKramer]

I was confused at first, because I don't usually deal with double negatives (If it's not deactivated, it's active right?

If Disc_Deactive is truly a Yes/No type field, then try setting the criteria as

[Disc Deactive] = 0

which should return all records not deactivated....

 

[LittleSmudge]

So what are you asking NewTech ?

In Reports and the like you can have a control to show the Discipline Name - you have two basic options.

1) Base the Report on a SQL string that combines the two tables like:-

Report.RecordSource = "SELECT Contact.*, DisciplineName FROM Contact INNER JOIN Discipline ON Contact.DisciplineId = Discipline.DisciplineId WHERE DiscDeActive = False"

Then the text box control can bind to the DisciplineName field directly.


2) Base the report on the Contact table directly.
Place a textbox control called txtDiscipline on the form bound to the DisciplineId field and set .Visible = No
Then in a textbox control that is to display the name put :-
DLookUp("DisciplineName","Discipline","DisciplineId = & DisciplineRef&quot


( If you insist on using spaces in field names then you'll have to go back through the above and put them in. AND you have to put the [ ] brackets in as well AND you won't be able to use Value Tips in code debugging - but it's up to you. )


'ope-that-'elps.

G LS

 

[newtech2]

Thanks so much for responding.

Regarding double negative and spaces in field names: it has been a learning experience.