Record in One Table Triggered by Field Selection in Another

[sdlewis]

I have a database that contains information about various incidents. In the generic Incident form, there are check boxes regarding what type of incident each record was. If the type of incident causes a removal, there are a great deal more fields that need to be filled in that are on a seperate Removal form.
The problem is that when I go to update on the form that includes only removals, all records show up. I know it is not good practice, but right now I have one table with several dozen fields. Several of those fields could be in a seperate table just for removal information, which I plan on doing. Right now I have two primary keys, Date and Serial Number.
** How do I make it so that when someone goes to enter/update the Removal form, only records for which the removal box was checked on the Incident form show up?

 

[RickSpr]

I assume your Incident form shows only one record at a time. I'm a little confused because you said "How do I make it so that...only records for which the removal box was checked on the Incident form show up?" Maybe you're using a "continuous form"? If so, the form may show several records, but only the one the cursor is in is actually active.

The OpenForm method has a "wherecondition" parameter for this purpose. The "wherecondition" is just a SQL WHERE clause without the WHERE keyword.

To use the parameter, you need to have a field (or combination of fields) that uniquely identify the record you want to show up in the Removal form. Usually, a form's record source includes the primary key, so I'll assume the Incident form has the key field IncidentID in its record source.

In your OpenForm method call, simply enter this into the "wherecondition" parameter:
"IncidentID = " & Me.[IncidentID]
This temporarily changes the Removal form's record source by including this condition in its WHERE clause. With this restriction, only the one record will appear in the Removal form.

If you're using the OpenForm action in a macro, rather than using VBA code to open the form, the syntax is slightly different. Code this for the "Where Condition" parameter:
"IncidentID = " & Forms!Incident![IncidentID] Rick Sprague

 

[sdlewis]

I'm not quite sure what you mean. I'll try to define the problem better. The first table is 'tblJT9Main'. It has 'Date' and 'ESN' as primary keys. It also has a field 'Removal' that is a yes/no. The second table is 'EPCData'. It has the same primary keys and also has the 'Removal' field. I want only the entried from the 'tblJT9Main' table where the 'Removal' field was checked to show up in the 'EPCData' table.
In addition, the form to populate the 'EPCData' table has some fields from 'tblJT9Main', and several other fields from 'EPCData'. I set the control source for the whole form to 'EPCData', then build the control source for the 'tblJT9Main' fields to: =[tblJT9Main]![fieldname]. When I go to Form View, those fields show #Name? in the box.
I hope this helps clear up the problem. Thanks for bearing with me.
Seth Lewis

 

[RickSpr]

Take the second problem first. In the control source expressions, is the [tblJT9Main] supposed to refer to the table of that name, or did you also name your first form tblJT9Main and that's what you're trying to refer to? If you mean it to refer to the table...well, you can't. Sorry, only forms can be used with this syntax. If you mean it to refer to the first form, and the form is, in fact, named tblJT9Main, then you just need to change it to "Forms![tblJT9Main]![fieldname]". (Note that the first form must be open or you'll get an error.

Now back to your original problem, getting the flagged records on the first form to show up on the second. If I understand you correctly, what you want is to have the second form pull data from the JT9Main table, let the user review and perhaps modify it, and then write it to EPCData table. Is that correct? Is the user deciding whether to add each record to EPCData, or just adding information to the record? Or have I got this all totally wrong?

I feel I should warn you that, if your table design isn't good, and this application expands with many functions and lots of updating going on, it might collapse under its own weight. With poor table design, you'll find yourself writing code to do things Access could have done for you, with proper table design. I can't stess enough how important good table design is. Putting it off as inconvenient is unwise. If you don't believe me now, you will eventually.
Rick Sprague

 

[UncleJack]

Rick has put it about as eloquently as I've ever seen. You need to read about "Normalization" before designing your tables, and I stress the plural. It would be extremely rare for any table to have several dozen fields in it. Think in terms of compartmentalization for easy of design, maintenance, and, above all, flexibility.

It sounds to me like you're working with maintenance related events on P&W JT9 jet engines. I would first create an Engine Identification table that stores make, model, S/N, date acquired, etc. Then I would create an Incident table that references the engine ID from the first table. Lastly I would create a Removal table that lists all items related to the removal of the engine from the airframe which is what I presume you mean by Removal. You can make forms based on these tables that display only the relevant information.

Separation of the data in your one large table into smaller, more logically managed chunks is the basis for an easy to build, understand, and use application.

Uncle jack