trouble presenting nested subform/linked table data

[yoshe]

Hello, I have a main form for physician practices that contains a subform of individual physicians in the practice. This subform contains, among other items, tax id numbers. It seems the tax numbers can change so I need to add effective dates and allow multiple tax numbers per physician. I'm planning to set up another table containing physician id, tax number and effective date, which i plan to link to the main physician table on the physician record id. I'm having trouble with presentation. Currently the physicians are displayed in continuous form view, which everyone likes. Is there any way to get the tax number field to behave like a drop down box, showing all the tax ids for that physician in the linked table? And somehow be able to add records to it? Or can anyone suggest an alternative way to approach this. Thanks very much for any help.

 

[TheAceMan1]

How are ya yoshe . . .

[blue]Is there any way to get the tax number field to behave like a drop down box, showing all the tax ids for that physician in the linked table?[/blue]

Yes there's a way! However [blue]the combobox can't reside in the detail area for a continuous form[/blue].

What you do is setup the combobox in the forms header or footer and update the [blue]Tax Number[/blue] field via the combo. This is the only way I see to maintain your desired view.

To explain why the combobox can't be in the detail section of a continuous form:

For a selection to be displayed it has to exist in the combo's [blue]row source[/blue]. Also the [blue]row source[/blue] is whats available to all records (it doesn't work on an individual record basis ... [blue] this is key![/blue]). So when you make a selection, [purple]any prior selections that don't exist in the new row source will go blank![/purple]

I hope you can see this.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]