Form Design Questions

[baseballrock17]

A 2-part question that i am certain has been asked hundreds of times but I cannot find a direct answer:

I have created a DB with several tables. PKs, and FKs are set up in the Relationships view. For example I have a tblClient (one) with a FK to tblAttorney (many).

1) As I'm adding a client to the DB, I'd like the form to present me with a drop down list of the lawyers and their firms that are already in the DB. Once selected, it should provide the AttorneyID in the table.



2) Unless the attorney isn't in the DB yet, what is the best way to direct the user to a form or subform where they can enter the Attorney information in THEN select the attorney for the client.

A point in the right direction would be most helpful... I'll take a tutorial or a link to another post. Whatever works. Like i said i know this question must get asked all the time. I just cant find the answer.

Thanks in advance.

[RuralGuy]

The ComboBox wizard can assist with that control and the NotInList event is just what you need to pop up another form to add another Attorney.

[baseballrock17]

Good stuff, RG. Thanks so much for the helpful response!

[RuralGuy]

You're welcome. Post back here if you are still having a problem with this issue.

[baseballrock17]

Post back here if you are still having a problem with this issue.

You asked for it,now!

I am using an event procedure to open the "other" form. Another 2-part question:
1)How can I make the form open in New (blank) record state?

2)Once I'm done providing the info to the "popup" form, is there a way to post this record back to the original form?

[RuralGuy]

You should be using the NotInList event of the ComboBox to open the other form. Are you? I'll answer your 2 questions in a minute.

[baseballrock17]

I put the procedure in the wrong event. Now it works as expected.

DoCmd.OpenForm "frmAttorney", acNormal, , , acFormAdd, acDialog opens the form in new Record view. Once added and saved, question 2 works correctly.

[RuralGuy]

Bingo!! It sounds like you have it sorted now.