696796
"join key of issue not in recordset"
Suggests you have a data problem.
Let step back a bit.
The process I follow when designing a database is to...
a) Ask what reports / information do I need
b) Identify how and what data I am going to collect to produce the information. This part I work out on paper.
c) Design my tables to collect the data and normalize them.
d) Enter test data and using queries, make sure I can retrieve the information. This step looks at providing the objectives in (a) using the design in (c).
Then I delete my test data, and establish my relationships. I suspect others may establish their relationships in (c) which is fine too.
Then I build my forms.
Have you established your relationship using the Relationship tool provided by Access? From the database window, from the menu, select "Tools" -> "Relationships". Paul Litwin's article explains this.
or
...Moving on
With relational databases, the most common relationship is a One-to-Many (1:M). I like to refer to this as parent and child, although you may see master and detail and variations thereof used. The "gotcha" is that you need a parent before you can have a child. You need the record on the "one" side to exist with it's primary key. The primary key is now used on the "many" side as the foreign key. If you think about it, it is pretty hard to now what foreign key to use if you have not established the primary key.
In the design suggestion I provided, I used a 1:M between (one side) tblIssue and (many side) tblIssueOccurance because you indicated you wanted to see the number of times an issue arises.
Filling in the pieces, I have to ask if you created an "Issue" in tblIssue?
To check this, use the Query Builder to...
1) Look at data in tblIssue
2) Look at data in tblIssue + tblIssueOccurance
Since tblIssueOccurance is linked to four or five tables, before you can create a single record in this table, you need to create records in...
tblOwner
tblPart
tblSupplier
(if you use a table for priorities, tblPriority)
and of course
tblIssue
Create records for Owner, Part and Supplier (and priority) should be fairly straight forward). Creating an Issue record before the occurence may be a little more complex.
...Moving on
Preferences. There are some wizards I like to use in Access, for example, the Forms wizard. And there are some wizards I do not use, for example, the autoforms. I can understand their convenience factor, but I then have to go back and tweak the design.
(I am assuming you have established your relationships)
Here is something to try.
Use the "Create form by using wizard". Select tblIssueOccurance. Use "Tabular" or "Datasheet" template. I am going to refer to this table as sbfrmOccurence.
Now create another form using the wizard. Select your tblIssue. Use "Columnar" or "Justified" template. At the last page / window of the wizard, select "modify the form's design". Move the form which is open in design mode to the side until you can see database windo which lists all your forms (tables, etc). Click and drag the sbfrmOccurence to the bottom of the current form. This is a form + subform design. After creating your issue record in the main form, because you have established your relationships, records created in the subform will be linked to the main record via the foreign key.
This is just an example for demonstration purposes, and may not be applicable to your needs. You probably want to create a main form based on tblIssueOccurance and use combo boxes to link to your related tables.
Richard
