Default Values in subforms

[Natalie]

I just realized that we were working with a main form that has a subform,
which has a default value; however, when a new record is created in the main
form the subform displays the default value yet is does not create a record
in the subform table.

My subform has a default Inspection Schedule(date) and when you create a new
record on the mainform I also want the subform table to update with that
default date. We have been missing inspections for all the new entries
because we did not realize that they were not updating on the subform table.

Thank you for your help.

 

[Jeff Boyce]

Natalie

A default value is the value that will be entered once a record is created.
No record, no default value.

Are you saying that the ONLY value entered in the subform is the date? If
so, perhaps there's another way of getting done what needs done.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Natalie]

Here is my problem: I have a main form with 1035 records; and a subform table
that only has 733records. I want to create a query that combines the two
tables without loosing the 302 records. I have tried and I cannot think of
another way to do it.

Here are some of the solutions I have thought of:
One option is to have the subform to create a new record automatically
whenever the mainform gets a new record (basically create a space holder).

My second choice is to add the subform table to my main form query, but my
mainform/subform relationship is a one to many relationship and if I change
the date from one record it will also update the second record if it has the
same user ID. Here is my setup:

Main Form:
[User ID]-primary key
[address]

Subfrom
[Inspection ID]-primary key
[User ID]- linked to main form


Or maybe my third option is to figure out how to create a query that will
query for all the User ID's from the main table even if there is no User ID
match from the inspection schedule.

 

[Tom Wickerath]

Hi Natalie,

Your third option is the best one to go with:

Or maybe my third option is to figure out how to create a query that will
query for all the User ID's from the main table even if there is no User ID
match from the inspection schedule.

In query design view, double-click on the join line between the two tables.
You should see a Join Properties dialog box displayed. Click on a button that
reads Join Type. Select either the 2nd or 3rd choices, whichever is
appropriate for your case. You have just created what is known as an outer
join. The default join, which shows results only for matching records in both
tables, is known as an Inner Join.

After clicking on OK twice to dismiss this dialog, you can see the effect by
clicking on View | SQL View, while still in the query design view. If you
look at the SQL (Structured Query Language) statement, you should see "Outer
Join" (it might be a left or right outer join) where there was once the words
"Inner Join" (corresponds to the first join type).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Here is my problem: I have a main form with 1035 records; and a subform table
that only has 733records. I want to create a query that combines the two
tables without loosing the 302 records. I have tried and I cannot think of
another way to do it.

Here are some of the solutions I have thought of:
One option is to have the subform to create a new record automatically
whenever the mainform gets a new record (basically create a space holder).

My second choice is to add the subform table to my main form query, but my
mainform/subform relationship is a one to many relationship and if I change
the date from one record it will also update the second record if it has the
same user ID. Here is my setup:

Main Form:
[User ID]-primary key
[address]

Subfrom
[Inspection ID]-primary key
[User ID]- linked to main form


Or maybe my third option is to figure out how to create a query that will
query for all the User ID's from the main table even if there is no User ID
match from the inspection schedule.