Forms with prefilled values from another table

[Gigi0614]

HELP!!

Access is doing so much for me, I don't know where to look to see what I'm actually doing!!

I'm using the Forms wizard

I've got a form called surgeryProcedure
It displays Surgeries & all the Procedures associated with it. There is a one to many relationship with the surgery and surgeryProcedure table.

I also have a Procedure table. This is a stand alone table that just list all the procedures & their costs.

In my form, I'd like to update the surgeryProcedure table, but I'd like for the user to be able to have a drop-down menu of all the Procedures (that comes from the Procedures table.

My problem is, I'm using the forms wizard, and I'm not sure how to do this... How can you have a form with drop down menus from one table actually updating values in a different table?

I'd like to update the surgeryProcedure table, but have a drop down menu of choices from the Procedure table just to make sure no bogus data gets enterend!!

Is this a combo box??

 

[fneily]

To make the following work, break any relationships to the surgeryProcedure table in the Relationship Window.

You could just open your surgeryProcedure table in design view, click on the Data Type cell next to the Procedure fieldname. Click on the dropdown arrow and select Lookup Wizard. Verify that the first option is selected. Click Next. Under View, make sure Tables is selected. Select your Procedures table from the list. Click Next. From the Available Fields list select the Procedure field. Click the >. Click Next. Click Next. Click Finish. Say Yes to save the table. If you go to Datasheet view and click in the Procedure field of the surgeryProcedure table, you'll get a dropdown arrow. Click on it and you'll see the procedures from the other table. Recreate your form. The dropdown will continue through to the form.

 

[Gigi0614]

Gotcha!! I guess you have to setup those lookups on the table before you create your form.

Now...1 more question... Why did I have to break the relationships to make it work?

THX!

 

[fneily]

There's other ways to do what you want. That's just one solution which is easy to explain. As an example, you can also setup an Unbound Combo box on your form and then when a selection has been made, use VBA code to populate the Procedure box.
In Access 2002, if you kept the relationship in the Relationship window, the Lookup Wizard will give a message that says "You can't change the data type or field size...." I forget if you get this message in Access 2000.