Problem dealing with unbound fields

[mscertified]

I have a form that is bound to a table. On the same form are unbound fields
in a related table (many:1 relationship).
I tried using bound fields but no arrangement allows me to add new records
to the related table.
The problem I have is that most of my processing is kicked-off by the BEFORE
UPDATE event, but if the user changes only one of the unbound fields, it does
not fire.
Where would I put the code to detect changes to the unbound fields so I can
update the related table? Or is there any better way to do this?

Thanks.

 

[Maurice]

I'd say in that case you have to do some checking if you required fields
(bound fields) have info on them or not. If not you could fire the before
update event from the on exit event of the unbound control. As I said you do
have to do the checking first because you have to decide wheter the code
get's fired or not.

hth

 

[Jeanette Cunningham]

no arrangement allows me to add new records
to the related table.

If you can fix it to use bound forms, everything will be much easier.
Base the parent form on a query from the table on the one side of the
relationship.
Base the subform on a query from the table on the many side of the
relationship.

If it won't work, to use unbound controls, any processing that would have
occurred on the form's before update event can be moved to the click event
of the close button. You won't be able to use the form's dirty property for
the unbound controls. The unbound controls won't have an old value to use to
know if the control's value has been edited.

It is so much easier to use bound forms. I suggest you try really hard to
get it working with bound forms.

Jeanette Cunningham

 

[mscertified]

Thanks for the response
You have it backward; the main form is based on the many side of the
relationship and the subform would be the one side. I could find no way to
make that work. If you have any idea let me know. I already spent 2 fruitless
days trying to get that to work. I'd love to use bound forms but the
form/subform facility seems to be designed for 1:many and not many:1
relationships. I also HAVE to be able to update the related table or add a
record if a related record does not exist.
The main table is Issues, an Issue may have zero or one client. A client may
have many Issues.

 

[mscertified]

Thanks for the response.
The close button idea wont work because I also have to trigger the
processing when moving from one record to another or when creating a new
record.
Essentially I need a 'before update' event but for unbound controls.

 

[Jeanette Cunningham]

backward ?? <g>
If you want a form for a one to many relationship to work you must base the
parent form on a query from the one side table and the subform on a query
from the many side table. This is exactly one of the great features of
access, that it makes it a breeze to build forms for one to many
relationships. You must put the many side table details in the subform and
the one side details in the main form if you want it to work.

However, I suspect that you may really have a many to many relationship.
If depends on how your data is set up. See if this model fits your data (you
are the one who is familiar with the data) - this is a suggestion.

If the same issue can apply to many different clients and the same client
can have many different issues, this is many to many.
If this is the case you need 3 tables.
tblIssues
tblClients
tblClientIssues

tblIssues is one to many to tblClientIssues
tblClients is one to many to tblIssues

here is a description to set up a form for many to many relationships
Use a main form (single view) for one of the Many side tables. Within the
main form, use a continuous subform bound to the junction table with the
Link Master set to the main form's PK field. This subform has at least one
visible control that is a combo box bound to the second table's PK field.
This form's Current event and the combo's AfterUpdate event sets an
invisible text box on the main form to the value of the combo box.

Along side the continuous subform is another subform bound to the other many
side table with its Link Master property set to the invisible text box.

With this arrangement, you can navigate or search for the main form record.
The continuous subform displays the items assigned to the main form record.
Clicking on a record in the continuous subform automatically displays the
related
record in the other subform.


Jeanette Cunningham