Blank Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables in database. One table holds individual demographic data
and a 5 digit record # field. The record # field is unique in the demo table.
Second table contains record # and other data. Record # in second table may
have duplicates matching unique record # in demo table. I'm trying to create
a query and resulting form I can use to enter a new record which would
contain demo data for first table and other data required for second table.
When I create query with two tables joined I get blank query with no data
entry option. Same happens when I create form from query.

How do I fix query using both tables so I can add a new record with
information required for both tables?

Any help will be greatly appreciated.
 
The normal method is to use the Form / Subform combination where the Main
Form is bound to the tblDemo (Parent" Table) and the Subform is bound to the
other Table ("Child" Table).

Check Access Help topic "About Subforms" ...

To be more efficient, you should set the record # as PK Field and a 1-to-M
relationship betwwen the 2 Tables.
 
Don't use a query for this. Use a form/subform instead.

First thing is to make sure that the record # field in the demographic data
table is the primary key. If you can't make it the PK, there's either
duplicate data or a null in the field. Fix it.

Next go to Tools, Relationships on the menu. Create a relationship between
the two tables on the record # fields and enable referential integrity. If it
won't let you that means there's an orphan record in the second table with a
record # not in the demographic data table. Fix the problem then try to
enable referential integrity again.

Next create a form based on the demographic data table. After making sure
that it works right, go back to design view and put a subform on it based on
the second table. Since you have set up the relationship between the two
tables, Access should properly link the form and subform. Now when you add a
new record in the subform, Access will automatically populate the foreign key
field with the primary key in the demographic data table.

Now when you create a query for a report based on these two tables, it might
seem that there are some missing records. This will happen if you have a
record in the demographic data table but no matching data in the second
table. Your query has an inner join and you'll be needing a Left join to see
all the records.
 
Back
Top