BaldyWeb

Open a second form to the record selected on the first form

Often you might have a form or control that displays summary information, and you want to select one of those items and open a second form that displays the detailed information.  The technique I use most often is the wherecondition argument of DoCmd.OpenForm (or DoCmd.OpenReport).  It is sort of like saying "open this form but only show the record(s) that meets this criteria".  Using this technique, the second form can be based on the table itself or a query that returns all records.  It looks like this for a numeric value:

DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

Where SecondFormName is the name of the form being opened, FieldName is the field in that form's recordsource the restriction is based on, and ControlName is the name of the control on the current form that contains the value to be shown on the second form.  As you will find throughout VBA, text and date values are treated differently.  For text:

DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"  '(note, that's a double quote, single quote and double quote at the end)

For a date value, use # instead of the single quote:

DoCmd.OpenForm "SecondFormName", , , "FieldName = #" & Me.ControlName & "#"

As noted above, this technique can also be used when opening reports.

But I want to have all the records still available in the second form

Home