Pat said:
I have a field in the main DB that is the record ID (autonumber) which is
not
displayed on the parent form. But because I have a table relationship
setup
with the child table I need to access this field from child form when
inserting records into the child DB. It works if the record is existing
and
being edited, but on a insert into the main table it will not be visable
to
the child.
I am opening the parent form in Add Mode with the following syntax:
DoCmd.OpenForm "frm_ModifyFirearms", acNormal, , , acFormAdd, acDialog
The first problem is your use of the term sub form here. In MS access, the
term sub form is a very specific term that denotes the placing of a sub-form
contorl on an existing form. We generally use this type of setup to edit and
model relational data, and the reason why we do this is because MS access
will automatically add the foreign key value for you.
If you actually launch the form separately as you are doing, then access
will not set the foreign key for you (eg: that field used to relate back to
the main table). If you use a true sub-form, ms-access WILL set this for
you.
The field in the main db is firearm_id. The field in the child DB is
weaponid.
ok...
The subform does have the fileds linked on the parent form.
Not 100% clear here. however, as noted by your other post, you do not have
to place those controls on the form to actually referenced and use the
values. Hence, you do not have to place the controls on the form to
reference the underneath record set data in this case.
Also it's not clear as to why you're opening the above forming dialog mode,
and you might want to expand on why this is so.
however, to answer the question I would recommend the following idea:
use the 2nd forms beofre insert event, and go:
weaponID = forms!NameOfMainForm!firearem_id
So, you really only need one line of code in the 2nd form.
I should also note that the reason why we're using the before insert the
event is this event only fires when the user actually starts to type data
into the form, and since the record will not be created unless the user
starts typing. This means if the user decides to close the form you will not
be littered with tons of blank records as simply closing that 2nd form
should NOT add a reocrd, and should the user decide to simply close that 2nd
form, no reocrd will be added.
The other thing I should mention here is that we should as you correctly
pointed out that we should force a disk write of the first form before we
launch into the second form:
eg: go:
if me.Dirty = true then
me.Dirty = false
end if
DoCmd.OpenForm "frm_ModifyFirearms", acNormal, , , acFormAdd
As mentioned, you really don't want to open the 2nd form as a dialog form un
less there is some special compelling reason to do so. However while you
don't want to open the 2nd form in dialog mode, you should most certainly
open the form as model (you will find this model setting in the second forms
other tab of the Forms property sheet).