Accessing data from a subform in the parent form's recordset

[Pat]

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 field in the main db is firearm_id. The field in the child DB is
weaponid. The subform does have the fileds linked on the parent form.

I hope I made some since of this question. I am new to Access and VBA.

Thanks in advance....

 

[Jeanette Cunningham]

Pat,
with a form subform setup, put the record ID on the parent form in a textbox
control.
Also put the recordID which is the foreign key on the child subform on the
subform in a textbox control.
The link master fields will be firearm_id and the link child fields will be
weaponid.
That is assuming that there is a one to many relationship between firearms
table and the weapons table and the tables are joined on that field.
This means in the relationship window, that you click on the firearm_id and
drag and drop it on the weaponid in the weapons table.
Setting up the link master fields and link child fields lets access manage
all the work to save the main form record before you add new child records.

Jeanette Cunningham

 

[Pat]

The only thing you mentioned that I have not done is have the fields on the
form. I was trying to keep that hidden from the user. And yes there is a
one to many relationship on the tables. Do the fields have to be on the
form?

It seems that the record is not being commited to the table before the
subform attempts to update the child DB. When I use the debugger the
recordset me.parent.recordset.fields("firearm_id").value is not set.

 

[Pat]

I got it woking with the following code on the main form.

Private Sub Tabs_Change()
' the tab index is 0 based
If (Me.Tabs.value = 2) Then
' commit the record...
If (Me.Dirty) Then Me.Dirty = False
End If
End Sub

THe subform is on tab #3. THis commits the record before the subform fires
the form activate event.

 

[Albert D. Kallal]

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).

 

[Jeanette Cunningham]

Pat,
pleased to hear you got it working. I always put the text box for the ID on
each form and make it hidden - set its visible property to no (property
dialog | format tab). I read years ago a post from Allen Browne explaining
that if you don't put the ID on the form, you can get access crashing on odd
occasions. I haven't heard about this recently, but I still do it.

Jeanette Cunningham

 

[Albert D. Kallal]

The only thing you mentioned that I have not done is have the fields on
the
form. I was trying to keep that hidden from the user. And yes there is a
one to many relationship on the tables. Do the fields have to be on the
form?

See my other post, it is not necessary to have the fields on the form at
all...

The fields to have to be included in the form's underlying record set, but
the actual text box control for those fields to not have to be placed on the
form to use them.

Keep in mind that some users build and use a query as the forms record
source. once again the rule is the same, and while you don't have to place
the fields on the actual form, that query will in fact have to include those
fields you plan to use and reference in your code

 

[Pat]

Thanks for all the replies. To be a little more specific here. The form
frmFirearms will open the form frm_ModifyFirearms when the user clocks on the
command button to add a new record with the following code.

DoCmd.OpenForm "frm_ModifyFirearms", acNormal, , , acFormAdd, acDialog

The form frm_ModifyFirearms has a tab conrtol with a page that has the form
frmImage on the page. The user will enter the fields such as make, model,
etc. Then click on the tab to attach image(s). The form frm_ModifyFirearms
data source is the table dbtFirearms. The form frmImage data source is the
table dbtImages. There is a relationship between the tables
dbtFirearms.firearm_id and dbtImages.weaponid as a one to many. The problem
I was having before I added the ditry=false is the value
Me.Parent.Recordset.Fields("firearm_ID").value was null. When I used the
debugger to look at the value of Me.Parent.Recordset.recordcount it was 0 and
BOF & EOF were true. I am usinf the following code in the form frmImage.

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate

intWeaponID = Me.Parent.Recordset.Fields("firearm_ID").value
If (intWeaponID = 0) Then
intWeaponID = Me.Parent.Recordset.Fields("firearm_ID").value
End If
Me!WeaponID = intWeaponID
Me.ImageFrame.Picture = "" ' Default to nothing

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox me.name & " - Form_Activate" & vbCrLf & Err.description
Resume Exit_Form_Activate

End Sub


I am looking for the best way to allow the user to add a record to the main
table dbtFirearms and before closing the form enter images into the images
table dbtImages. Any help is very much appreciated.

 

[Albert D. Kallal]

DoCmd.OpenForm "frm_ModifyFirearms", acNormal, , , acFormAdd, acDialog

I had asked why you opening this form in dialog mode? Is there a particular
reason for this? (if there is not, you much better off to open the form as
model, and NOT dialog. If you use dialog, then you can't even use the menu
to go edit->undo, and you see later on, you can't use the calling code to
set the foreign key value.

So, we should resolve the above issue, and change this form to model, and
NOT use dialog. About the only reason why you would use dialog is if you
have calling code that needs to wait until the form is done (but, as
mentioned, you can' even use the ms-access menus with a dialog form, so,
they
really do tie your hands. Also, it is significantly important if you *do* in
fact need a dialog form, as that can significantly change the suggestions
and
approaches I offer to you. So, we should figure out why this form needs to
be dialog (it is not normal in this case for the form to be dialog).

The next thing to deal with is you are opening the frm_ModifyFirearems in
addmode, but then you suggest you jumping into a sub-form (under a tab) on
this form. KEEP IN MIND if you do not edit any field in this newly opened
form, you cannot enter into child forms to add related data. You **must**
dirty the parent record BEFORE you attempt to enter into a child form.

Once again, if
what I just stated is NOT clear, ask for further clarification . The reason
for
this question is if the user is not actually editing the underlying record
for Modifyfirearems, then *ms-access* will NOT have yet set the PK value
for Modifyfirearems, and that further means ms-access will be un-able to
add and set the foreign key value for dbtImages table.

In other words, opening form
ModifyFirearems in add mode will NOT add the record ***unless*** the user
edits some data on frmModifyFirearems BEFORE they attempt to edit data in
dbtImages (subform).

If the above is in fact your case, then you going to have to use code to
***dirty*** the frm_ModifyFirearems record BEFORE you move into to the
sub-form with image table.

And, if the above is the case, the best approach is do dirty the record
*after* the openform command, and that means you can't use acdialog (so, all
these issues are interrelated).

I would suggest the following:

dim strF as string
strF = "frm_ModifyFireArms"
DoCmd.OpenForm strF, acNormal, , , acFormAdd
forms(strF)!weaponid = me!firearm_id

Now, when you go into the tab subform in frm_ModifyFireArms, there will be a
pk id available for the image sub-form.

***NOTE*** as mentioned, if the user needs to, or will be editing values in
frm_ModifyFireArms *before* they go into the sub-form with images , then
you can eliminate the code of:
forms(strF)!weaponid = me!firearm_id,

since editing data *in* the form
will dirty the record, and trigger creation of a PK value for
frm_ModifyFireArms.

So far, we only adding ONE line of code to your original question *if* we do
this right...

Also, the above design assumes you only adding ONE record to
frm_ModifyFireArms (that means you should set his forms allow additions =
no, because the openform command in adFormAdd will override this setting for
ONE record, and the user will have to close the form to add additional
records (and, forcing the close is done by using model forms, NOT USING
acDialog forms).

 

[Pat]

I really appreciate your guidance on this. There is no particular reason I
chose dialog mode other than a misunderstanding of modal. I'll try your
suggestions and reply later.

Again THANKS!!

 

[Pat]

Albert, thanks for all your help. After trying your suggestions I need to
keep the form open as acDialog. My reason is, the form frmFireams is a
continuous view of all firearms in the table. When the user clicks on the
record selector, it will launch the openform of the frm_ModifyFireams form in
acFormEdit mode in the Form_DblClick event. But if the user clicks on the
command button to add a firearm, it will call the same form, in acFormAdd
mode. When a firearm is added to the table, I need to issue a requery
command to update the list on the frmFireams form when the focus if back on
the frmFirearms. I tried to use the OnTimer event, but I found when the
requery is run, it will reposition the user’s selection on the record
selector to the first record. I guess I could save the position and move to
it after the requery.

I had a problem with the Master/Child links in my code. I resolved it and
now it works fine.

Any thoughts?

 

[Albert D. Kallal]

Albert, thanks for all your help. After trying your suggestions I need to
keep the form open as acDialog. My reason is, the form frmFireams is a
continuous view of all firearms in the table. When the user clicks on the
record selector, it will launch the openform of the frm_ModifyFireams form
in
acFormEdit mode in the Form_DblClick event. But if the user clicks on the
command button to add a firearm, it will call the same form, in acFormAdd
mode. When a firearm is added to the table, I need to issue a requery
command to update the list on the frmFireams form when the focus if back
on
the frmFirearms.

Right, just put the requery command in the close of the 2nd form....

I tried to use the OnTimer event, but I found when the
requery is run

That is a VERY wrong event to use in this case. You don't want to base a
requery of a form on some timer event out of the blue. You ONLY want to
requery ONCE when you need to...

I would as mentiond to requery use either:

a) the close event of frm_ModifyFireams
(and, you can test for me.Newrecord = true if you want)

or
b) use the activate event of frmFireams

Either approach (a,or b) is a fine way to requery the form..

 

[Pat]

Thanks!!! I made the change as you suggested ant it wored fine. 1 Last
question. I used the following in the close of form 2.
Forms!frmFirearms.Requery

Is there any way to not hard code the form name?

 

[Pat]

Thanks!!! I did as you suggested and it works fine.

1 Last question. I used "Forms!frmFirearms.Requery" in the close event in
form 2. Is there a way to not hard code the form name? I tried to reference
me.parent, but got an error.

 

[Albert D. Kallal]

Thanks!!! I did as you suggested and it works fine.
Excellent!!!


1 Last question. I used "Forms!frmFirearms.Requery" in the close event in
form 2. Is there a way to not hard code the form name? I tried to
reference
me.parent, but got an error.

Well, I should point out that me.Parent is only active when you're
in a true sub form. Hence, your code MUST be in the sub-form to use
me.Parent and this feature is not available un less the code your executing
is the inside of the real actual sub form.

It's also a great question on your part to ask how to not hard code this
form name. I like questions like this because it shows you thinking about
your software design, and are thinking about more than just making something
work, but about making it more maintainable.

Unfortunately there's no built in system to grab the previous form, however
as a general software design ruled and approach, I simply in most of my
forms
to put in the following code in the forms "code module":

Option Compare Database
Option Explicit

Dim frmPrevious As Form

Now, in the forms on-load you can go:

Set frmPrevious = Screen.ActiveForm

What this means now is for all of my forms when I think or might need a
reference to the previous form that opened, I can now reference that form
anywhere in code

and, then I can do things like:

frmPrevous.Refresh ' force a disk write

frmPrevious.Requery ' requery the form

frmPrevious.LastName = "hello"

By adopting the above programming standard in my code, then virtually any
time in code where I want to reference the previous form I simply use
frmPrevious in place of "me".....

if you don't wanna use the above idea, then you can also pass the name of
the previous form by using the openargs, adn then go:

forms(me.Openargs).Requery

The form would have to been opened via:

docmd.OpenForm "nameOfForm",,,,,,me.name

 

[Pat]

Thanks for all your help. I will make the final change.