Display record and subrecords if exists, after primary key entry

[drazeni]

I have a set of tables with a one to many relationship:

Header table (one relationship)
Detail table (many relationship)

Header table has a primary key called 'Reg_no' and detail table is joined on the same field (one to many). I have an input form with a subform, referencing the two tables with data entry set to yes. Simple enough.

My dilemna is as follows:

Upon the user entering a reg_no, if the master record (header table) matching the entered reg_no exists all the other details must be displayed in the main and subform for further master/sub record entry/edit.

How do I make the form call up the master and it's subrecords for editing if the reg_no exists? (I would prefer not to use DLookup, as it may be required to modify the master record detail).

I assume that the reg_no field will be a combo box? but if there is no existing record, a new one must be created.

If anyone has a good solution to this, or you require more info from me, please post.

Much appreciated.
Mario

 

[mac318]

In the After_Update event of your text\combo box, set the subform's Recordsource to a select statement with the text\combo box in the where clause:

SubFormName.RecordSource = "Select * from Tablename where KeyFieldName = " & ComboBoxName & ";"

SubFormName.Requery

Each time the value in changed in the combo box, the subform will reflect the change.

To change the values in the master, I suggest that you use the ComboBox Wizard to lookup a record based upon your primary key.

mac