query w/ sql View and access table

[TMGreen]

I have a sql view joined (join option 2) to access table. Key is
project/subproject (found on both tables). Not all view entries have
matching notes record. How can Users can edit where there is a note, but if
there is not a note, it won't allow them to add. Previous to using sql view
it worked.

How do I set it up so that if there is not a matching NOTE, the user could
enter the new note and Access will create the appropriate record (with
project/subproject key?) - do I have to do it in form? (this user is not a
form lover!)

Relationships are setup in sql to handle the view table. I setup a
relationship in access between view and table but it didn't seem to help.

 

[Allen Browne]

You have a Project table, and a SubProject table with a one-to-many relation
between them. The SubProject table has a Notes field, and you want to design
an interface to users can edit or add new notes.

If that's the idea, the simplest solution would be to create a main form
bound to the Project table, with a subform bound to the SubProject table.
You probably want to show the subform in Form View, so there's lots of space
to add notes. The subform has its own navigation fields, so you can add
multiple notes in the subform for the project shown in the main form.

That's generally much easier than trying to do it all in the one query.

 

[TMGreen]

Actually it's a one to one relationship - one note per Project/subproject
combination. The user wants to do this in a query rather than a form -
however I can use a datasheet type form. I tried the form, but when
attempting to ADD a new record to this joined table I got same issue. The
before update on the notes field didn't cover it. I need a before insert on
that joined table only? Can you use the before insert event when inserting
to the joined table instead of main table? how?

 

[Allen Browne]

Using a multi-table query to attempt to add new records that can be added to
either table is an exercise in frustration.

Use the form and subform instead.