Serch Form

[amfroehlich]

I want to make a form where a user will enter both of my primary keys and then hit a search button which will pull up all the information that pertaines to those keys from two different tables, so that it could be edited. Does anyone have an example of this or could give me a description on how to make this form.

Thanks,

Aaron
ray:

 

[SydneyGeek]

Hi Aaron, try this.
Assuming you want to edit the 2 tables independently, you'll need one main search form and two subforms, one for each table
First, build the subforms. For each subform, use a query that uses all fields, or the source table, as the record source. Select the table/query, create an Autoform, and go to Design view. In the Properties, change the Default view to Datasheet. Save each form as subMyTableName [change MyTableName to suit].
Now the main form. Create a new form, with no record source (it needs to be an unbound form for this to work). Now, using the Toolbox with wizards ON, drag a ComboBox onto the form. The Wizard launches. Get it to pick the Primary Key and a descriptive field from the first table, and hide the Primary Key. This makes it easier for the users but if you need to see the Primary Key, make it the only field in the combo. Keep going through the wizard, give the combo a caption, and then go to that combo in Design view. Right-click, select the Properties, and select Other. Change the name to something useful (cmbMyTableName, using the correct table name).
Repeat for the second combo.
Put it together. Save the main form. Call it frmSearch. Still in Design view, Restore the size so you can see the database window. For each subform, do this:
Find the subform icon and drag it onto the main form. When it's there. you may or may not get a dialog asking you to link parent and child fields. If you don't, right-click the subform and go to its Properties. You should see an option to set Parent and Child fields. Click the Builder button inside the Parent or Child fields rows. You'll have a dialog. for Parent, select the combo that corresponds to the subform. For the Child field, select the Primary Key of the table.
Repeat for the second subform.
Save the form, and test it out.


Denis

 

[amfroehlich]

This is working good so far but it won't let me set parent or child feilds becuase it says "Can't build a link between unbound forms." What am I doing wrong?

 

[SydneyGeek]

OK, sometimes Access spits the dummy with unbound forms. Another option is to have the subforms based on queries, and filter those queries on the Combos. This post shows how to build a cascading series of combos. Look at the setup for the last combo, going to the form. It should get your system running.

Denis