Query/Subform Help

[Guest]

Good day,

I have a table with the field OrderID that is a primary key linked to other
tables with one too many relationships.

I generate a query based on the information inputed in a main form with
subforms and it does not display the
information unless I go through each subfrom and press enter which makes a
record. Basically I donot want to have the user to do this all the time if
they feel like skippping a subform. Any Idea on how to take care of this so
my query works regardless. I have set all me defaults to Zero on the subforms
hoping this would do the trick however it doesnt.

Hope this makes sense, any help would be appreciated.


For Example: Try This to see what I mean.

Create a Table Called MAIN
Fields/Structure are:
MainID - AutoNumber (Set as Primary Key)
Date - Date/Time
Sales Name - Text

Create another Table Called PressID
Fields /Structure are:

PressID - Number
Description - Text
TotalCost - Currency

Create a relationship between the two MainID to PressID one to many checking
off all three boxes.

Then create a form with the maintable information and the Press table
information as the subform through the wizard.
Only fill in the MainTable information in the form. You will see the PressID
return 1 which is correct (Same as main table)

Generate a query based on this infomation and you will see that nothing
shows up unless you press enter in the PressSubform creating a record. or put
in information.

 

[Marshall Barton]

I have a table with the field OrderID that is a primary key linked to other
tables with one too many relationships.

I generate a query based on the information inputed in a main form with
subforms and it does not display the
information unless I go through each subfrom and press enter which makes a
record. Basically I donot want to have the user to do this all the time if
they feel like skippping a subform. Any Idea on how to take care of this so
my query works regardless. I have set all me defaults to Zero on the subforms
hoping this would do the trick however it doesnt.

Hope this makes sense, any help would be appreciated.


For Example: Try This to see what I mean.

Create a Table Called MAIN
Fields/Structure are:
MainID - AutoNumber (Set as Primary Key)
Date - Date/Time
Sales Name - Text

Create another Table Called PressID
Fields /Structure are:

PressID - Number
Description - Text
TotalCost - Currency

Create a relationship between the two MainID to PressID one to many checking
off all three boxes.

Then create a form with the maintable information and the Press table
information as the subform through the wizard.
Only fill in the MainTable information in the form. You will see the PressID
return 1 which is correct (Same as main table)

Generate a query based on this infomation and you will see that nothing
shows up unless you press enter in the PressSubform creating a record. or put
in information.

Your query need to use an outer join instead of an inner
join.

 

[Guest]

Hi Marshall.

Can you walk me through this based on this example. I still cannot get it to
work

One too many relationship from MainID to PressID

Much Appreciated.

 

[Marshall Barton]

You were complaining that the query you are using to verify
if a record existed didn't return anything unless you hit
the enter key. The reason for that is because a subform
record has not been created, but your query only returns
records that exist in both tables.

In the query's design window, click on the line connecting
the MainID and PressID fields. Then right click on the line
and select the Join Properties item in the popup menu. You
should see that the selected option is to show only records
where the join fields are equal (this is an Inner Join),
Select the option for show all records from table Main and
on;y those records from table Press where the join fields
are equal (an outer join).

Note that the scenario you are testing with this query is
behaving as expected. A subform record will not be created
until you enter something in the press form AND do something
(move to another record, close the form, etc) to cause the
new data to be saved to the table. If you run the query
before the record is saved, it will not show up in the
query.

 

[Guest]

Marshall.

Thank you very much. I was changing the join in the relationship between the
two tables instead of in the query. Much appreciated.

Cheers.
K

You were complaining that the query you are using to verify
if a record existed didn't return anything unless you hit
the enter key. The reason for that is because a subform
record has not been created, but your query only returns
records that exist in both tables.

In the query's design window, click on the line connecting
the MainID and PressID fields. Then right click on the line
and select the Join Properties item in the popup menu. You
should see that the selected option is to show only records
where the join fields are equal (this is an Inner Join),
Select the option for show all records from table Main and
on;y those records from table Press where the join fields
are equal (an outer join).

Note that the scenario you are testing with this query is
behaving as expected. A subform record will not be created
until you enter something in the press form AND do something
(move to another record, close the form, etc) to cause the
new data to be saved to the table. If you run the query
before the record is saved, it will not show up in the
query.
--
Marsh
MVP [MS Access]

Can you walk me through this based on this example. I still cannot get it to
work


One too many relationship from MainID to PressID