SubForm Issue

[Novice1]

I have a form that tracks military decorations of which there are different types.

I have a subform that shows other similar decorations on the same person. Everything works well with one exception. I don't want to show the decoration (record) I'm currently working on in the subform.

The purpose of the subform is to identify potential problems (e.g., awarding the same decoration twice).

Any help would be appreciated.

 

[CJ_London]

For a definitive answer, you need to provide information about your main form and its recordsource SQL, the subform and its recordsource SQL and the subform control properties - in particular the linkchild and linkmaster properties - otherwise we are just guessing at a possible solution.

Assuming your tables and forms are set up correctly a simple solution would be to put a filter in the subform to exclude the medal shown in the main form - depending on how your form is working this would need to be 'triggered' in the mainform current event and perhaps elsewhere.

 

[Privateer]

You need an unmatched query, or in your case, all the medals from the medals table that don't match the ones already in the person's table. If you go to Create in the menu and select Query Wizard, then select the "Find Unmatched ..." and step through it you will get the query for the subform. It will be something like this. SELECT tblMedals.MedalID, tblMedals.Name FROM tblMedals LEFT JOIN tblPerson ON tblMedals.MedalID = tblPerson.MedalID WHERE (tblPerson.MedalID Is Null); Good Luck.

 

[Novice1]

Thanks. As I understand the "Find Unmatched" query, it finds unmatched records from two separate tables. I'm working from one table. I simply want a query that pulls specific records but not the record I'm working on. I'm trying to exclude the current record (CustID). I believe I should be able to write something in the query of the subform. Am I off base?

 

[Novice1]

Thanks for the help. What worked for me was placing the following code in the query. In essence, all records will show in the subform with the exception of the record I'm working on.

<>[Forms]![frmInputScreen]![CustID]

 

[Privateer]

Glad you found a solution, which is what I would have suggested, does not equal the current record. However, I wanted to chime in with another thought. You should be working off of two tables, or three total. The table with the person's information, which has their primary key PersonID, FirstName, LastName, etc. Then another table that has the medals the person has been awarded PersonID, MedalID, AwardedOn. And the third is the list of all medals MedalID, MedalName. If your tables are normalized accordingly, the unmatched query would work.