Setting a foreign key automatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an AddComments form that I open from another reviewFeedback form. The
AddComments form is bound to a table of the same name; the table is linked to
the Feedback table via the idFeedback field. I would like to open the
AddComments form and programmatically set the idFeedback control on the
AddComments form to whatever value is showing in the reviewFeedback,
idFeedback control. I tried this code but it doesn't work.

Two questions: (1) Why doesn't it work? (2) How can I do what I am trying to
do?

Private Sub Form_Open(Cancel As Integer)
Me!idFeedbackNumber = Forms!reviewFeedback.idFeedbackNumber
End Sub



Expand AllCollapse All
 
The open event in ms-access is too soon to try and modify values.

The open event also has a "cancel" option, and thus you as a general rule
use the forms "open" event to check, or test for conditions that would want
you to NOT allow the form to open (missing values, wrong values, record in
use by another user, etc. etc. etc.).

However, you can not modify values in the forms on-open event (too soon..and
the event can be canceled).

So, code that sets up field values, sets up defaults, sets up any thing with
values etc, that code belongs in the forms on-load event.

So, you code you have should be ok if you move it to the forms on-load
event.

However, a much better event would be the forms on-insert event. That way,
if the user adds more then one record, then your FK value gets set. The way
you have your code now is that it runs once when the form loads...and you
might not even be moving, or on the correct record. So, you really only want
to set the FK when you INSERT the record. So, the best event here to use is
the on-insert event.

So,

on-open - we use this event to "test" conditions that would STOP or
prevent the form from loading.

on-load - we use this event to setup code values, default text box
values, and even your code could go here

on-insert - this code only runs when the user actually types some
characters and actually ADDS a new record. If the user does not modify any
data, and closes the form..then you don't get a nasty blank record added (if
your code runs in the on-load..then you already dirtied the record...and
then if the user closes...you added a record). So, on-insert only runs when
in fact the record is dirtied..and the user modifies some data on the form.
So, this is your best event to use here. You don't run the code unless it
needs to, you can allow the user to add more then one record when the form
is loaded (and your FK set code will run each time). And, if the user
decides to close the form and do nothing, you have not yet modified record
and thus closing the form means no blank record accident added.
 
Back
Top