linked table/subform with no records

[markjkubicki]

I have 2 tables related (many to one), as well as a form with subform; the later table being the data source for the sfrm.



the problem I seem to be having is:
if no entries exist in the related field of both tables (- there would never be an entry in one without a relationship to the same entry in the other), then when the form loads in cannot link the sfm (null error)

my thought is that before the form loads, I need to check if there are any entries in the field, and if not -break the link between the tables, and when an entry is made, re-establish it

does this make any sense, or am I crazy thinking?

as always, with many thanks in advance..
Mark

[June7]

Do you have the forms based on tables or queries? If queries, do not include both tables.

Do you have relationship built? What type?

Is this a relationship like with an Order and OrderDetails? Order is the parent and OrderDetails the child. Must be able to have entries in parent table without entries in child table. Entries should not be possible in child table without parent entry first. Subform should be the child table.

[markjkubicki]

I correct myself... there is no subform, the data comes directly from the form's record source (so this is quite different... and I apologize for the confusion; never-the-less I'm stuck)

the form is based on tables

the tables are related in the datasource for the form ("include only where joined fields from both tables are equal" - however it is effectively a one to many condition...)


there will only be entries in both tables that have an exact match
there could be an instance where the "many" table has records, but no entries in the related field, so that the "one" table has no records; consequently, there is no link between the 2 tables (null), and the form won't load properly

ex:
the 2 tables are tblDetails and tblContactInfo
the matching field is "strManufacturer"

any record on the form would have (or not) an entry in txtManufacturer (herein lies the problem... it is possible that no record has an entry in that field)
as soon as one is entered (into tblDetails), a corresponding record in tblContactInfo is created (at this point the record in tblContactInfo has no other details; telephone, email,.... you would think that this would tie into a master database, but the specifics varies project by project, so the details needs to be manually entered for each manufacturer for that project. if the manufacturer reoccurs in the tblDetails, the contact info will be the same. editing it at any occurance will edit it at all, hence the separate table (tblContactInfo.) inversely, when any manufacturer is completely removed from the project, the correlated record in tblContactInfo is deleted... (all thru VBA...)

[June7]

If the form is based on 'tables' (plural) then it is really based on a query. Can't use one form to enter data into multiple tables.

Not understanding data structure. tblDetails is details for what - contact? Or are you selecting a contact from a combobox to save in txtManufacturer field of tblDetails record?

If desired contact not found, use NotInList event to open contacts form and enter new record which will then be available for selection in the combobox. Review: http://www.blueclaw-db.com/access_no...ed_example.htm

Why would you want to remove contact record? Couldn't they be a contact for more than one project?