Wizard for Easily Creating Forms and Subforms

[Guest]

I have created a project database for which there is a record for each
project. In general, the project has 2 kinds of fields:

- Freeform
- Categorical - Based on values (foreign keys) in related tables

Is there an easy way (wizard) to create a display and edit form which shows
the freeform fields in a grid at the top of the form, and shows each of the
categorical fields as drop downs at the bottom? As I click each record in the
upper grid, the drop-downs will display the associated categorical field, and
possibly allow me to change it as well?

Conversely, I'd like to create another "filter" form which has the
categorical drop list fields at the top, the
freeform field grid at the bottom, and have it work so that the grid will
only display the fields selected in the "filters"?

Thanks in advance

 

[John Nurick]

Hi Tommy,

Have you tried a form in continuous view with a footer (and/or header).
Display one kind of field in the detail section, and the other in the
footer (or header).

 

[Guest]

Hi John,

I have indeed tried this, but I am having trouble "synchronizing" parent and
child fields on the form. Although I'm not an Access expert, I know this can
be done without resorting to brain surgery. BTW, not to waste your (or anyone
else's), if there is some documentation or FAQ that you're aware of for doing
this, please send.

Thanks

 

[John Nurick]

If you do it the way I suggested there are no "parent-child" issues:
it's one form, bound to one query (or table). All you need to do is
ensure that the query is returning all the fields you need, and then
place them on the detail section or the header (or footer) as required.

You'll need to provide rowsources for the comboboxes you use to display
the "categorical" fields, but these are normally simple queries on the
related tables, again with no "parent-child" issues.

If this doesn't cover your situation, please explain you data stucture
carefully. I've begun to wonder whether you used "field" in your
original posting in places where you meant "record".

 

[Guest]

Sorry for the confusion, let me see if I can clarify.

The master "Project" table contains rows with the following columns:

-Name (text)
-Description (text)
-StartDate (date)
-EndDate (date)
-TypeID (one of the values in the "Type" table)
-DeptID (one of the values in the "Department" table)
-etc.

In addition there are 2 other tables "Type" with the following columns:

-TypeID (number)
-etc.
-etc.

And "Department" with the following columns:

-DeptID (number)
-etc.
-etc.

Also 1 to many relationships have been specified between the Project table,
and the Type and Department tables.

I'd like to create a form which displays the Project table in a grid format
at the top, and the Type and Department table value as drop downs at the
bottom. As you click on the different rows of the Project grid, the value in
the Type and Department values change accordingly. Also, if desired, the
Type and Department values can be changed for a particular Project record by
somehow using the drop downs.

Thanks

 

[John Nurick]

I believe that what you describe is what I am suggesting. However, I'm
not sure what you mean by

the Type and Department table value as drop downs

.. Do you just want to have comboboxes that display the TypeID and DeptID
values from the Project table, or do you also want to display other
fields from the Type and Department tables?

Either way, no subform is needed. As I've said before, create a form
bound to the Project table, in Continuous view. In its detail section,
put controls bound to whatever fields you want to display from the
Project table. Arrange and format the controls to give you the grid
effect you want.

Then use the View menu to reveal the form's header and footer. If you
like, put titles and labels in the header area. In the footer, place a
combobox bound to Project.TypeID. Name it cboTypeID. Make its RowSource
a query based on the Type table that returns whatever fields you want to
display, sorted in the order you want. It might be something like this:

SELECT TypeID, TypeName FROM Type ORDER BY TypeName;

Then set the Columns and ColumnWidths and other properties of the
combobox so it displays the data neatly. If you want to hide the TypeID
and just display the name, set the width of the first column to 0.

Repeat the process using a second combobox (cboDepartmentID) bound to
the Department table.

If you want to display more fields from Department and Type than will
fit into the single line allowed by the combobox, one way is put
textboxes in the form footer with expressions like this as their
ControlSources:
=DLookUp("FieldName","Type","TypeID=" & [Forms]![XXX]![cboTypeID])
where XXX is the name of the form.