Combo Box Issue

[Greg]

Hi All,

I have the following in the Row Source of my combo box.

SELECT tblStaffCode.* FROM tblStaffCode WHERE (((tblStaffCode.Active)=True));

It works fine and when the checkbox is False the name doesn't appear in the
drop down list which is great, HOWEVER when tblStaffCode.Active is False it
removes the staff code (staff member initials) from the display within the
form all records. The record within the table isn't affected.

Basically I don't want the staffcode (initials) to be removed from existing
records.

Greg.

 

[TC]

Nil comprendo!

You have a staff table where each record has an Active flag. Your combo
box only displays the active records, it does not display the inactive
ones.

That much I understand - but not the rest. What do you mean, "it
removes the staff code (staff member initials) from the display within
the form all records" ?

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Greg]

TC

Sorry, i'm probably not using the right terminology in my explanation.

Your correct on the part you understand so far.

The combo box lists the staff's intitials for example

GBE
DAS
MJW

and so on...

The problem is when staff members Active checkbox is un-checked it won't
display the staff members initials in the combo box field for old records
too. It's no problem for future records as the staff member isn't active
anymore, but old records just have null value on the form, but the table does
actually show the staff members initials.

I hope this is clearer.

Greg

 

[Ron2005]

I am having a problem visualizing your form. It would seem that if the
initials are in the table for the older records, why are they not
showing on your form. Is the combobox actually bound to the field in
the database? The initials should be showing even if they are not in
the dropdown.

 

[RobMastro]

Ron

The problem is occuring on the relationship you have setup between the tables.


For example:

tblPersons (Name, Address, City , State, ZipCode, ColorID, IsActive)
tblColors (ColorID, Color)

The join between tblPersons and tblColors should be ... and I'll do this by
the wording that you see when you create the relationship ..

Include ALL records from tblPersons and only those records from tblColors
where the joined fields are equal.

This is an important to note because a deletion from the tblColors will not
affect the display of the records in tblPersons.

If you set the relationship so that it includes ALL records from tblPersons
and ALL records rom tblColors, if you are missing an entry in the tblColors,
it will affect the display of the records in tblPersons.

HTH

 

[Greg]

Ron,

I agree it should be showing but ?????

Yes the combobox is bound to the field.

When i print/preview a report the initials appear just not in the subform.

Greg.

 

[Greg]

RobMastro,

I thought it may have been relationship related, however i exprimented the
different types of join types to no avail.

When i print/preview a report all is well.

I have a subform "Notes" within a form called "ClaimForm" and it's only the
subform that does not want to display the initials of an inactive staff
member.

It's got me utterly frustrated !

It's my fault for poorly designing in the first place, i initially made the
combobox row source a value list and have now changed to table/query.

Greg.